Run an Excel Macro from Access

[Skip Bisconer]

I have a linked Excel file that has to be cleared at month end and updated with a .csv file downloaded from a credit card company. I have all of that covered but currently start the process from Access and I manually run a macro while in Excel to import the .csv file do a bunch of formating and other data imput which I do with a macro in Excel.

I would like the objXL.visible to be false to make the process seemless to the Access user but I need that macro to fire to complete the process.

I can't run it under Workbook_Open as the file needs to be opened regularly at times directly from Excel and also, there are a lot of lines of data to remove before it can fire and it takes a minute for that complete before I can fire the macro. I figure maybe I could get the macro to run from the Access code. I do a lot of data manipulation currently but I have no idea if what I want can be done. So before I do a bunch of trial and error maybe someone out there has already accomplished the task and would like to share.

Any advice would be appreciated.

 

[Mutdogus]

You can run a macro that is in an Excel Workbook from Access.

objXL.Application.Run "TestMacros.xls!Macro1"

where "TestMacros.xls" is the name of the excel file and "Macro1" is the name of the macro.

 

[Skip Bisconer]

Thanks for the help.

 

[ST4RCUTTER]

I'm trying to do the same thing here. When running the code below off an On-click event I received a "Run-time Error '424': Object required

Sub On_Click_Run Excelmacro()

objXL.Application.Run "P:\ROC Reports\Network OSP Reports\QAM64 Migration\files\Formatting_macro.xls!format_cf"

End Sub


Any idea why? My guess is that Access cannot resolve the file from this string.

 

[Skip Bisconer]

Try this. I believe you have not defined objXL as Excel.
Sub RunMacro()
Dim objXL As Object
Set objXL = CreateObject("Excel.Application")
Set xlWB = objXL.Workbooks.Open("P:\ROC Reports\Network OSP Reports\QAM64 Migration\files\Formatting_macro.xls")

objXL.Application.Run "P:\ROC Reports\Network OSP Reports\QAM64 Migration\files\Formatting_macro.xls!format_cf"

End Sub

 

[ST4RCUTTER]

I keep getting Run-time error of '1004' or "cannot be found". I double checked my folder names, filename, and macro name and all seems well. Not sure why this is happening. Here was my code:

Private Sub open_excel_formatting_Click()
    
Dim objXL As Object
Set objXL = CreateObject("Excel.Application")
Set xlWB = objXL.Workbooks.Open("P:\ROC Reports\Network OSP Reports\QAM64 Migration\files\Formatting_macro.xls")

objXL.Application.Run "P:\ROC Reports\Network OSP Reports\QAM64 Migration\files\Formatting_macro.xls!format_cf"

End Sub

 

[ST4RCUTTER]

Problem solved through the code provided in this thread.