• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 227
  • 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
 
Darrell PorterEnterprise Business Process ArchitectCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

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