?
Solved

export query to excel then run macro over it

Posted on 2007-10-15
3
Medium Priority
?
949 Views
Last Modified: 2013-11-27
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?

thanks.
0
Comment
Question by:Moother
3 Comments
 
LVL 6

Expert Comment

by:chipconsult
ID: 20077368
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
0
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 2000 total points
ID: 20077397
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
xlApp.Quit
Set xlApp = Nothing




Regards,

Patrick
0
 
LVL 3

Author Comment

by:Moother
ID: 20077743
Thanks Patrick!:)
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

809 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