Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2008
    Posts
    78

    Unanswered: Calling Excel from Access

    I export a query using DoCmd.TransferSpreadsheet

    After that I email it using olattach.Add and then olitem.send

    Outlooks pops up and I allow it to send the email.

    Of course I'm using highly abbreviated code in the above - I just want to dewscribe my environment.


    All this is working fine but before sending, I now wish to run an excel macro to format the XL file.


    I figured the best way was to create an excel macro and then call it right before sending out.



    Dim oAppXL As Object
    Set oAppXL = CreateObject("Excel.Application")
    oAppXL.Run "PCMacros.xls!Macro1"


    When it hits the oAppXL.Run statement, access complains that it cannot find the macro. I'm sure it's there.

    I have already fully qualified the filename of the XL file in the code and it still cannot find it.

    Am I getting the syntax wrong? I've tried so many combinations....

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,420
    Provided Answers: 7
    have you open the PCMacros.xls file
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I figured the best way was to create an excel macro and then call it right before sending out.
    I would be doing the code in Access and running it either immediately before or after exporting data to Excel. Preferably before.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Aug 2008
    Posts
    78
    Quote Originally Posted by myle
    have you open the PCMacros.xls file

    Sure, opens fine, runs the macro and formats the output as expected...

  5. #5
    Join Date
    Aug 2008
    Posts
    78
    Quote Originally Posted by StarTrekker
    I would be doing the code in Access and running it either immediately before or after exporting data to Excel. Preferably before.

    The data is in the form of a query. I don't have a lot of formatting options for queries. It's easier to output to an excel file, then format it later.

    Before I posted my original question I tried pasting the vb code from excel into a module in access, then linking a macro to it via "RunCode". I cant seem to get the linkage right, I keep getting access errors.

    "The expression you entered has a function name that Microsoft Office Access can't find"

    I'd much rather call an excel macro to do the formatting for two reasons

    1. It already works

    2. Someone in another department has written that macro and maintains it. Every time there is a cosmetic change to their output, they can handle it themselves rather than asking me. I want to keep all the formatting issues outside of the stuff I've written.


    Going back to my original question, is my syntax wrong? Is there something there I'm missing. I know you'd take a different approach but I definitely want to call an excel macro to format this excel file...

  6. #6
    Join Date
    Sep 2005
    Posts
    19
    The key here is that you need to use the Excel VB commands to make this work. Try doing something like this before the call to the macro:

    Dim oAppXL As Object
    Set oAppXL = CreateObject("Excel.Application")
    oAppXL.workbooks.open filename:= "PCMacros.xls"
    oAppXL.Run "PCMacros.xls!Macro1"

    Depending on where it's saved, you may need to include the full path to the PCMacros file.

    I'm sure there are other solutions, but I've used this great success! Hope it helps.

  7. #7
    Join Date
    Aug 2008
    Posts
    78
    Quote Originally Posted by newblu
    The key here is that you need to use the Excel VB commands to make this work. Try doing something like this before the call to the macro:

    Dim oAppXL As Object
    Set oAppXL = CreateObject("Excel.Application")
    oAppXL.workbooks.open filename:= "PCMacros.xls"
    oAppXL.Run "PCMacros.xls!Macro1"

    Depending on where it's saved, you may need to include the full path to the PCMacros file.

    I'm sure there are other solutions, but I've used this great success! Hope it helps.
    It's awful .. I'm STILL getting the macro not found error.. man.. if this is a typo I'm goign to be so mad at myself... is there a way to fully qualify the macro name like including the sheet number?

    Thanks!

  8. #8
    Join Date
    Aug 2008
    Posts
    78
    Quote Originally Posted by PonPending
    It's awful .. I'm STILL getting the macro not found error.. man.. if this is a typo I'm goign to be so mad at myself... is there a way to fully qualify the macro name like including the sheet number?

    Thanks!



    Code:
    Dim oAppXL As Object
    Set oAppXL = CreateObject("Excel.Application")
    oAppXL.workbooks.open filename:= "C:\fc006t\dev\SBUX\PT-Macros.xls"
    oAppXL.Run "PT-Macros.xls!EODFormatting"
    Produces --> *** Error Not Found ***


    Code:
    Dim oAppXL As Object
    Set oAppXL = CreateObject("Excel.Application")
    oAppXL.workbooks.open filename:= "C:\fc006t\dev\SBUX\PT-Macros.xls"
    oAppXL.Run "C:\fc006t\dev\SBUX\PT-Macros.xls!EODFormatting"
    Produces --> *** Error Not Found ***



    Code:
    Dim oAppXL As Object
    Set oAppXL = CreateObject("Excel.Application")
    oAppXL.workbooks.open filename:= "C:\fc006t\dev\SBUX\PT-Macros.xls"
    oAppXL.Run "EODFormatting()"
    Produces --> *** No Error, No Result ***

    Code:
    Dim oAppXL As Object
    Set oAppXL = CreateObject("Excel.Application")
    oAppXL.workbooks.open filename:= "C:\fc006t\dev\SBUX\PT-Macros.xls"
    oAppXL.Run "EODFormatting"
    Produces --> *** SUCCESS ! ***

    I left off the XL file name, the fully qualified name and the () and it works...

Posting Permissions

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