[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 226
  • Last Modified:

Excel Export Question

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")
xlObj.Workbooks.Add
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
    Next


Sheet.Range("A3").CopyFromRecordset rs  'this copy just the data

sheet.range("A1").Value=txtStartDt & "-" & txtEndDt
xlObj.Visible = True

Open in new window

0
snyperj
Asked:
snyperj
  • 2
1 Solution
 
WalkaboutTiggerCommented:
have you tried

Call WorkbookName!MacroName at the end of your code?

Or

Application.Run("'WorkBook Name With Spaces.xlsm'!MacroName")
0
 
snyperjAuthor Commented:
Yes, raises an error that says it can't find the procedure (Macro name)...

I also tried the full path to the .xlt file which is in a shared folder on the network.
0
 
Rey Obrero (Capricorn1)Commented:

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")
xlObj.Workbooks.Add
Set Sheet = xlObj.ActiveWorkbook.Worksheets(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
    Next


Sheet.Range("A3").CopyFromRecordset rs  'this copy just the data

sheet.range("A1").Value=txtStartDt & "-" & txtEndDt
xlObj.Visible = True


'ADD this line

xlobj.run "NameOfMacro"  '<<< YOU HAVE TO CHANGE  "NameOfMacro" with the ACTUAL NaME of the macro from the excel template





0
 
snyperjAuthor Commented:
Thanks...
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now