Transferring specific cells from Access to Excel

[MarrowGenx]

I would like to know how to transfer one particular cell in Access to another specific cell in Excel. I am trying to take dynamic information from Access and placing it into specific cells in Excel. Here is an example:

Search qryExample(query name) for a specific record meeting the criteria: Row1(row name) = "Test". Take the the data in Row2(row name) found to meet this criteria and move it to cell A1 in the excel file name xlExample.xls.

Thanks for any help you can offer!

 

[ETID]

In excel select Data>Get External Data>New Data base Query,... then from the databses tab, choose "Ms Access Database*" (or what ever other db your working with)...


follow the wizard from there...

Once you have returned your data to excel,..right click in the data area and edit the query or choose properties...there you can set options like refresh on open and auto fill formulas etc....

then you can define your query to select the data you need and link to it from other sheets.

 

[MarrowGenx]

I am sorry, but I do not think that I was clear enough on my initial question. What I need is the VBA code that can be used in Microsoft Access to do the following:

Search qryExample(query name) for a specific record meeting the criteria: Row1(row name) = "Test". Take the the data in Row2(row name) found to meet this criteria and move it to cell A1 in the excel file name xlExample.xls.

I would appreicate if the answer used the names I have stated above, so I can know where to change the code for my specific problem.

I appreciate any help that you have to offer. Thank you in advance.

 

[ETID]

Following the steps above,..you can set these query criteria from within Excel Via MsQuery/ODBC..It's an active link.

Then set your cell ref to point to the returned record...or am I still "built too low" for this question?

 

[MarrowGenx]

Actually, I finally figured it out. Here is the type of code I was looking for:

Public Sub Exporter()
Dim i As Integer
Dim dbs As Database
Dim rs As DAO.Recordset

Form_frmMainMenu.CommonDialog1.CancelError = True
Set dbs = CurrentDb

With Form_frmMainMenu.CommonDialog1

.Filter = "excel files (*.xls)|*.xls|"
.FilterIndex = 1
.DialogTitle = "Export Questions to Questionaire"
.ShowOpen

End With

Set xlWorkbook = GetObject(Form_frmMainMenu.CommonDialog1.Filename)
xlWorkbook.Application.Visible = True
xlWorkbook.Application.DisplayAlerts = False

For i = 1 To 6

CycleSelect (i)
Set rs = dbs.OpenRecordset("Select * From qryQuestionaire Where [CycleName] = '" & CurrentCycle & "'", dbOpenDynaset)
If rs.AbsolutePosition <> -1 Then
rs.MoveFirst
Set xlWorksheet = xlWorkbook.Worksheets(CurrentCycle)
xlWorksheet.Select
Set CurrentRange = xlWorksheet.Range(&quot;A8&quot

Do Until rs.EOF = True

CurrentRange.Value = rs![ObjName].Value
Set CurrentRange = CurrentRange.Offset(0, 1)
CurrentRange.Value = rs![ActName].Value
Set CurrentRange = CurrentRange.Offset(0, 1)
CurrentRange.Value = rs![ActDesc].Value
'make check boxes
Set CurrentRange = CurrentRange.Offset(1, -2)
rs.MoveNext

Loop

End If

Next i

End Sub

I appreciate your help on the matter. Thank you.