run excel macro from access vba without creating excel object RRS feed

  • Question

  • i have created a macro in access which exports 4 queries to an excel workbook and saves it.  i then need to run a macro from a 2nd excel workbook but i do not want to create the excel object as this tends to hang up my excel.  is there another way to run an excel macro from access vba without creating the excel object?

    here is the code which is hanging up my excel;

    Dim XL As Object
    Set XL = CreateObject("Excel.Application")
    XL.Workbooks.Open ("C:\MultipleReports\Template\FACLoadview Template_multiple.xlsm")
    XL.Visible = False
    XL.Run "Links"
    XL.ActiveWorkbook.Close (False)
    Set XL = Nothing

    Tuesday, August 25, 2015 12:37 PM

All replies

  • You could call the macro automatically when you open the Excel workbook using:

    Sub Workbook_Open()
      Call MyMacro
    End Sub

    Then have Access execute the Excel calling the workbook.  You can use WScript.Shell to run it.  It will look something like this.

    CreateObject("").Run ""C:\Program Files (x86)\Microsoft Office\Office14\excel.exe MyWorkbook.xlsx"", 1, True
    You will have to play around with the format of the command.  You can also quit Excel once the command has executed.

    Tuesday, August 25, 2015 1:31 PM
  • The workbook would need to be open in an Excel instance to run a macro, you can open it in a second instance, which is what your macro does. Your code looks fine, it should work.

    There could be all sorts of reasons why it fails but without further information it's not possible to say. Which line does your code fail on if you step through. For debugging make the second instance visible. Is there a particular reason for opening the file in a second instance, which seems to contradict what you say you don't want to do.

    Tuesday, August 25, 2015 3:51 PM