export query to excel then run macro over it

Question: Hi,

I am exporting with .OutputTo from Access 2000...

After the export i need to run an Excel macro over the new file to format the table.

The excel Macro is in a different file on the server...

Is this possible?

Who is Participating?
Patrick MatthewsConnect With a Mentor Commented:
Hello Moother,

Yes it is possible.  The code below is assumed to run from Access, and assumed to execute
immediately after your OutputTo, and that the macro you call takes the name of the "data
workbook" as an argument...

Dim xlApp As Object
Dim xlWbData As Object
Dim xlWbCode As Object

Set xlApp = CreateObject("Excel.Application")
Set xlWbData = xlApp.Workbooks.Open(PathToDataWb)
Set xlWbCode = xlApp.Workbooks.Open(PathToCodeWb)

xlApp.Run "'" & xlWbCode.Name & "'!NameOfTheMacro", xlWbData.Name

xlWbData.Close True
xlWbCode.Close True

Set xlWbData = Nothing
Set xlWbCode = Nothing
Set xlApp = Nothing


Instead of pushing from Access it could be an idea to pull from Excel.
Create a macro that imports from Access using aa Access-query and let this macro call the formatting macro.

Claus Henriksen
MootherAuthor Commented:
Thanks Patrick!:)
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.

All Courses

From novice to tech pro — start learning today.