export query to excel then run macro over it

Posted on 2007-10-15
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?

Question by:Moother
    LVL 6

    Expert Comment

    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
    LVL 92

    Accepted Solution

    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


    LVL 4

    Author Comment

    Thanks Patrick!:)

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
    Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    746 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

    14 Experts available now in Live!

    Get 1:1 Help Now