Solved

Excel Export Question

Posted on 2011-09-06
4
213 Views
Last Modified: 2012-05-12
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
Comment
Question by:snyperj
  • 2
4 Comments
 
LVL 15

Expert Comment

by:WalkaboutTigger
Comment Utility
have you tried

Call WorkbookName!MacroName at the end of your code?

Or

Application.Run("'WorkBook Name With Spaces.xlsm'!MacroName")
0
 

Author Comment

by:snyperj
Comment Utility
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
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility

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
 

Author Closing Comment

by:snyperj
Comment Utility
Thanks...
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now