Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Location
    Massachusetts
    Posts
    16

    Unanswered: Access Query To Excel

    I am using the following code to run a Query and create an Excel spreadsheet and it works pretty good.

    DoCmd.OutputTo acOutputQuery, stDocName, _
    acFormatXLS, "c:\test\test.xls"

    Once the Excel spreadsheet has been created is there a way to run a macro I have created in my PERSONAL.XLS! FixExcel to reformat the EXCEL spreadsheet before I open it to save the steps of running the macro in EXCEL?

    A DoCmd that follows the above.

    Is there another DoCmd or something that would then OPEN the Excel spreadsheet without the user having to open it?

    Not much VBA knowledge

    Thanks

  2. #2
    Join Date
    Feb 2005
    Location
    England.
    Posts
    232
    It can be done.

    Here is a sample of code I've done. It's a module, so create a new one, call it whatever you like. You must note that the call inside of the form must call the Sub name and not the module name!!

    Form ...

    DoCmd.OutputTo acOutputQuery, stDocName, _
    acFormatXLS, "c:\test\test.xls"

    Call mdlRunExcelCode

    Module ...

    Sub mdlRunExcelCode()

    Dim XL As Excel.Workbook

    Set XL = GetObject("c:\test\test.xls", "Excel.Sheet")
    XL.Application.Visible = True
    XL.Application.Run "PERSONAL.xls!{Enter your macro name here}"

    End Sub



    Hope this helps! If you need anything else just ask.

  3. #3
    Join Date
    Mar 2004
    Location
    Massachusetts
    Posts
    16

    Thanks

    I'll give it a try and let you know.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •