Results 1 to 9 of 9
  1. #1
    drjabear
    Guest

    Launch Excel spreadsheet and macro from Access

    Good Morning,
    What is the easiest way to launch an Excel spreadsheet and then macro from a macro or code within access. Thanks for the help!

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Launch Excel spreadsheet and macro from Access

    That depends. What version of Access and Excel are you using, and are you trying to launch an Excel macro after you open the sheet? Do you want the sheet visible to the user or do you want to run something automagically?
    Charlotte

  3. #3
    drjabear
    Guest

    Re: Launch Excel spreadsheet and macro from Access

    I have macros in access to automatically export (using Transferspreadsheet action) to the Excel workbook. After exporting to the workbook I would like Access to open the Excel workbook that I exported to and launch a maco that is already created in Excel. Yes I would like the end user to view a "splash page" I have created in Excel I am using Office 2000. Thanks for your help and insight!

  4. #4
    drjabear
    Guest

    Re: Launch Excel spreadsheet and macro from Access

    Charlotte, I do not want to be a bother, but I would like to know if my response to inquiry regarding my problem made sense, again, thanks for your help.

  5. #5
    New Lounger
    Join Date
    Dec 2000
    Location
    Indiana
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Launch Excel spreadsheet and macro from Access

    My preference is to write the excel macro in access and run from there, but you probably have a lot of work tied up in this already. So here's something that should suit your situation.

    Sub Run_Excel_Macro() 'TransferredSS As String) 'The argument is the path and file name of the
    'workbook you created with the TransferSpreadSheet Action. I have assumed the action has
    'already been done.

    'Set a reference to excel in order to run this macro. From the Tools menu,
    ' select References.... , then check the MS Excel x.0 Object Library box.
    Dim xla As Excel.Application
    Dim xlsM As Workbook 'this is for the workbook that contains the macro that you want to run.
    Dim xls As Workbook 'for the Transfered Spreadsheet

    Dim TransferredSS As String
    Dim MacroWBPathAndFileName As String 'for the Macro WorkBook Path And File Name
    Dim MacroName As String 'for the name of the macro to run
    'Lets give these some value
    MacroWBPathAndFileName = "C:My DocumentsExcelTest1.xls"
    MacroName = "Macro1"
    TransferredSS = "C:My DocumentsExcelBook1.xls"


    'Launch Excel
    Set xla = New Excel.Application
    'Make it visible
    xla.Visible = True

    'Open the Transferred Spreadsheet
    Set xls = xla.Workbooks.Open(TransferredSS)


    'Open the macro workbook
    Set xlsM = xla.Workbooks.Open(MacroWBPathAndFileName)
    'While xlsM should be the active workbook, lets just make sure because the "Run" command
    ' that follows is meaningful only in the context of the active sheet.
    xlsM.Activate
    xla.Run MacroName

    'The macro runs. Whether control returns to Access automatically depends on your excel macro.

    'Assuming you want to save and close everything,
    xls.Save: xls.Close
    xlsM.Save: xlsM.Close

    xla.Quit
    'That's all folks.
    End Sub

  6. #6
    drjabear
    Guest

    Re: Launch Excel spreadsheet and macro from Access

    Wow, thanks for the detail... that looks great. Now would you reccommend that I write the code in the access application or the excel application? What would be easiest? Again, thanks for your help.

  7. #7
    New Lounger
    Join Date
    Dec 2000
    Location
    Indiana
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Launch Excel spreadsheet and macro from Access

    Moving the code from excel VBA to an access module is something I would strive to do. It can be tricky, though. You have to know your excel objects and reference them with object variables in access -- something that is not exactly intuitive. Start with something simple and get it working from access. Use the macro recorder and the object browser. Learn as you go.

    Before long you will cast recordsets into ranges and spin them into pivot tables with charts and trendlines and... You get the idea. Good luck.

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Launch Excel spreadsheet and macro from Access

    Back up a minute! There is NO macro recorder in Access. Are you talking about the macro recorder in Excel? That's an excellent way to get the macro actions sketched in, although some things will be changed a bit if you run them from Access using Excel as an automation server.

    You can download <A target="_blank" HREF=http://download.microsoft.com/download/office2000dev/sample/2/WIN98/EN-US/OFFAUTMN.EXE> Office automation samples and whitepapers</A> from Microsoft, and you may find that they will answer a lot of your questions. The Offautmn.exe file contains both Office 97 and Office 2000 information and samples.
    Charlotte

  9. #9
    New Lounger
    Join Date
    Dec 2000
    Location
    Indiana
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Launch Excel spreadsheet and macro from Access

    I spoke of the excel macro recorder. Thanks for the clarification.

Posting Permissions

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