One option is code behind Excel that pulls data from Access in the form of a recordset object and then writes data from the recordset into cell. Other option is code behind Access that opens and manipulates Excel object in VBA to write data into cells.
Maybe this will help http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm
Example code from my Excel:
Code:
Dim rsData As DAO.Recordset
Dim intNCount As Integer
Dim intRecords As Integer
Dim j As Integer
Set rsData = DAOdb.OpenRecordset("SELECT * FROM GeoDataAll WHERE StateNum='" & gstrStateNum & "' AND ProjectName='" & Replace(strProjectName, "'", "''") & "';", dbOpenSnapshot)
rsData.MoveLast
intRecords = rsData.RecordCount
rsData.MoveFirst
If intRecords = 0 Then
MsgBox "No samples found. Check your entry and try again." & vbCrLf & "If this error repeats, click AboutDATAGRAB to get administrator contact info.", vbApplicationModal, "No Samples"
Call Reset("All")
ElseIf intRecords > 1002 Then
MsgBox "Number of records exceeds 1002. Operation will terminate." & vbCrLf & "Notify administrator. Click AboutDATAGRAB to get administrator contact info.", vbCritical, "ExcessRecords"
Call Reset("All")
Else
With Worksheets("Samples")
.Range("B16").CopyFromRecordset rsData, , 4
.Range("F16:F" & 16 + rsData.RecordCount) = "Yes"
.Range("C1").Value = gstrStateNum
.Range("C3").Value = intRecords
.Visible = True
.Activate
.EnableAutoFilter = True
.Protect Contents:=True, userInterfaceOnly:=True
Worksheets("Start").Visible = False
End With
With Worksheets("Results")
j = 16
Do While Worksheets("Samples").Cells(j, 1).Value & "" <> ""
If Worksheets("Samples").Rows(j).Hidden = False And Worksheets("Samples").Cells(j, 6).Value = "Yes" Then
rsData.FindFirst ("LabNum='" & Worksheets("Samples").Cells(j, 3).Value & "'")
.Cells(intNCount, 1).Value = rsData.Fields("SampledFrom")
.Cells(intNCount, 2).Value = rsData.Fields("LabNum")
.Cells(intNCount, 7).Value = rsData.Fields("FieldNum")
'Increment intNCount so the next time around it displays data in the row below this one
intNCount = intNCount + 1
End If
j = j + 1
Loop
End With
End If