Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Nov 2001
    Location
    Twickenham, UK
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Automation (Access 97 & Excel 97)

    Hope someone can help...

    I have created an Excel macro (that is currently run from a custom excel button), but I would like to run the macro from Access.

    I have used the following code (where 'Format_Financial_Report' is the name of the macro and strFileName is the name of the excel document):

    Set XL = CreateObject("Excel.Application")
    With XL.Application
    .workbooks.Open strFileName
    .Visible = True
    .Run "Format_Financial_Report"
    .Quit
    End With
    Set XL = Nothing

    The Macro is saved to my 'Personal Macro Workbook' so that it is always available for the button, irrespective of what workbooks are open. The problem is that the above code fails to find the macro, because the 'Personal.xls' VBAPRoject isn't visible in the code windows until after you've run the macro once.

    Any ideas how to 'call' the macro from 'Personal Macro Workbook' ? or failing that, is there a way for me to 'call' the excel button rather than the macro (the button is always visible)

    I've looked through the posts to date, as well as the MS support pages and couldn't find anything.

    Yours hopefully

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Automation (Access 97 & Excel 97)

    Chris,

    You have to open the workbook witch contain the macro also.
    Something like this :

    Set XL = CreateObject("Excel.Application")
    With XL.Application
    .workbooks.Open "c:Program FilesMicrosoft OfficeOfficeXlStartPersonal.xls"
    .workbooks.Open strFileName
    .Visible = True
    .Run ("Personal.xls!Format_Financial_Report")
    .Quit
    End With
    Set XL = Nothing

    Hope this help
    Francois

  3. #3
    New Lounger
    Join Date
    Nov 2001
    Location
    Twickenham, UK
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Automation (Access 97 & Excel 97)

    Hi Francios,

    this worked a treat...

    (the only thing I had to change was taking the '.Visible=True' line out - as the code was creating 9 different spreadsheets and running the macro on each of them, it created too many annoying flashing screens)

    Many many thanks

    Chris D <img src=/S/joy.gif border=0 alt=joy width=23 height=23>

Posting Permissions

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