• Launch Excel spreadsheet and macro from Access

    Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » Launch Excel spreadsheet and macro from Access

    Author
    Topic
    #1768232

    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!

    Viewing 0 reply threads
    Author
    Replies
    • #1778312

      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?

      • #1778315

        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!

      • #1778345

        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.

        • #1778353

          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

          • #1778354

            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.

            • #1778356

              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.

            • #1778409

              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 Office automation samples and whitepapers 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.

            • #1778431

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

    Viewing 0 reply threads
    Reply To: Launch Excel spreadsheet and macro from Access

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: