Sending data in a report to an Excel template

  • Thread starter Thread starter Ron V
  • Start date Start date
R

Ron V

I have an Access report that I would like to extract
certain fields and have them populate fields in an Excel
template.
For example: Let's say in my Access report I have [Field
x], [Field y] & [Field z].
Now in my Excel template I just have [Field x] in Cell B3
& [Field z] in Cell B4 with their labels in A3 & A4.
How would I have the Access report export Field x & z into
their corresponding fields in the Excel template.
Is that possible?
If so, what would be the best way to go about coding it?
Could you outline how the code should look be/c I am not
that code savvy.

Thanks,
Ron
 
hi guys.. i have a sample code here. Hope it is of some help to you.

Option Compare Database

Public Function ExportDataToExcel()
Dim objXL As Object
Dim objWB As Object
Dim CellAdd As String

Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim c As Integer
Dim rCount As Integer

Set objXL = CreateObject("Excel.Application")
Set objWB = objXL.Workbooks.Open("\\vinayak\sampleexcel.xls")

Set cnn = CurrentProject.Connection
Set rs = cnn.Execute("Product Query")

rCount = 3
While rs.EOF = False
CellAdd = "A" + Trim(Str(rCount))
objWB.Worksheets("Sheet1").Range(CellAdd) = rCount - 2
For c = 1 To rs.Fields.Count
CellAdd = Chr(Asc("A") + c) + Trim(Str(rCount))
objWB.Worksheets("Sheet1").Range(CellAdd) = rs.Fields(c - 1).Value

Next
rs.MoveNext
rCount = rCount + 1
Wend

Set rs = Nothing
Set objWB = Nothing
Set objXL = Nothing
End Function


reply to me at (e-mail address removed) incase this was helpful to you.. :-)
 
Back
Top