I am using a variation of the code below to send some data out to an excel template.
On the excel template, I have a command button that runs a macro.
Was wondering if this code could be modified in such a way that it automatically fires the macro tied to the command button? I could then get rid of the command button...
Placing the macro in the workbook_open event is not working because the workbook is opened before the data is pasted in...
Dim db As dao.Database, rs As dao.Recordset
Dim i As Integer, j As Integer
Dim xlObj As Object
Dim Sheet As Object
Set db = currentdb
Set rs = db.OpenRecordset("yourQuery")
Set xlObj = CreateObject("Excel.Application")
Set Sheet = xlObj.ActiveWorkbook.sheets(1)
'copy the headers
Dim iRow, iCol
iRow = 2
For iCol = 0 To rs.Fields.Count - 1
Sheet.Cells(iRow, iCol + 1).Value = rs.Fields(iCol).Name
Sheet.Range("A3").CopyFromRecordset rs 'this copy just the data
sheet.range("A1").Value=txtStartDt & "-" & txtEndDt
xlObj.Visible = True