Execute EXCEL Macro From ACCESS

Question

I am attempting to automate some processes here.  One of the things that would really speed things up is automating transferring data from ACCESS to Excel.

I want to, via VBA, export a table to an Excel file, open the newly created spreadsheet and an existing spreadsheet and copy the data from the new spreadsheet into the appropriate places in the existing spreadsheet.

I have a macro in the existing spreadsheet that will do the copying, but I cannot get the VBA in access to execute it.  What am I missing?

Thanks for any help/guidance!

-ding k

Option Compare Database

Option Explicit

Dim ApXL As Object

Dim xlWBk1 As Object

Dim xlWBk2 As Object

Dim xlWSh1 As Object

Dim xlWSh2 As Object

---

Private Sub CopyToExcel()

On Error GoTo CopyToExcel_Err

'

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "tblITR_Data", "C:\Documents and Settings\All Users\Documents\test.xls", True

'

'create Excel object

'

  Set ApXL = CreateObject("Excel.Application")

'

'Open Excel sheets to be used

'

  Set xlWBk1 = ApXL.Workbooks.Open("C:\Documents and Settings\All Users\Documents\test.xls")

  Set xlWBk2 = ApXL.Workbooks.Open("C:\Documents and Settings\All Users\Documents\Test_ExistFile.xls")

'

'Set Excel to visible so can see what is going on

'comment out once all is working

'

  ApXL.Visible = True

'

'Execute macro "GetAccessOutput" in Test_ExistFile.xls

'

  ApXL.Application.Run xlWBk2 & "'!GetAccessOutput"

'

CopyToExcel_Exit:

  ApXL.Workbooks.Close

  ApXL.Quit

  Set ApXL = Nothing

  Exit Sub

'

CopyToExcel_Err:

    MsgBox Err.Number & ": " & Err.Description

    Resume CopyToExcel_Exit

'

End Sub

Answer 1

Try:

ApXL.Application.Run "Test_ExistFile.xls!GetAccessOutput"

Answer 2

I keep getting an error that access can't find the macro and that it may be missing or that macros are all disabled.  Macros are by default disabled, is there a way to bypass that prompt when excel opens?

Answer 3

Thank you, Daniel!  I can't believe it was that easy!