Calling Excel Macro in Access 1

[mikew71]

I'm running a HR database which draws its data from several Spreadsheets that are linked to SAP. I've created a code within excel that updates these (we're not allowed to ODBC unsupported software into main systems) and want to be able to activate this from access, I've seen this done once many years ago but have forgotton the code. Does anyone have any ideas?

 

[bernie10]

I think its:

Dim xlApp As Object
xlApp.Run "'MySpreadsheet.xls'!MyMacro"

Collen

 

[mikew71]

Thanks but this dosen't want to work, I get error 91.

 

[bernie10]

In the earlier post I didn't put all the declarations and all that down- I was just trying to get you started.

Anyway try this- let's say the spreadsheet you want to run the macro from is in a folder in your C: drive called "ExcelDirectory"-

    Dim xlApp As Object
    Dim wb As Object
    Dim ws As Object
    Set xlApp = CreateObject("Excel.Application")
    Set wb = xlApp.Workbooks.Open("C:\ExcelDirectory\MySpreadsheet.xls", False, False)
    xlApp.Visible = True
    xlApp.Run "'C:\ExcelDirectory\MySpreadsheet.xls'!MyMacro"
    wb.Close (False)
    xlApp.Quit
    Set xlApp = Nothing

See if that works.

Collen

 

[mikew71]

Cheers, thats worked a charm. Its been a while since I've written code!