Opening an Excel Workbook and Running a Macro from a Personal Macro Workbook

[MarkGallie]

I need to open an Excel Workbook from an Access Program and then Run a Macro from an Personal Macro Workbook. I can run the macro manually from the Workbook and I can also run a macro stored locally in the Workbook through Access. Here is the code I'm using:


Public Function TestExcelMacro()
Dim objXLApp As Object
Dim XLApp As Object
Set XLApp = CreateObject("Excel.Application")
With XLApp
.Application.Visible = True
.UserControl = True
.Workbooks.Open "\\DATA-WW\Users\markgallie\My Documents\HireNumbersCJ(11.09.13).xlsx", False
.Application.Run "PERSONAL.XLSB!SetUpPage"
End With
End Function

I have also tried using the Full path of the Personal Macro Workbook in the ".Application.Run" Function. Can anyone shed any light on where I'm going wrong?

Thanks

Mark

 

[pr2-eugin]

Just use

.Run "[COLOR=Blue][B]SetUpPage[/B][/COLOR]"

Considering that is the name of the Macro.

 

[MarkGallie]

That would work if the macro was stored locally. I have tried this and it just says:

"Run-time error '1004': Cannot run the macro 'SetUpPage'. The macro may not be available in this workbook or all macros may be disabled."

When running the macro manually it is referred to as "PERSONAL.xlsb!SetUpPage" however this same reference doesn't work from Access. I know macros are not disabled because I can run it internally. Is there another way to reference an externally stored macro?

 

[pr2-eugin]

Hmmmm.. Try this without referring to the Current EXCEL object.

[B]Application.Run[/B] "PERSONAL.XLSB!SetUpPage"

 

[MarkGallie]

Still no luck I'm afraid. I get the same error. Also tried that one with the Full Path to PERSONAL.xlsb

 

[MarkGallie]

I have found a solution. All I needed to do was to also open PERSONAL.xlsb before running the macro. I had assumed that this wouldn't be required as you can usually access these macros from anywhere. Here is my finished code:

Public Function TestExcelMacro()
Dim objXLApp As Object
Dim XLApp As Object
Set XLApp = CreateObject("Excel.Application")
With XLApp
.Application.Visible = True
.UserControl = True
.Workbooks.Open "C:\Users\Mark Gallie\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.xlsb", True
.Workbooks.Open "\\DATA-WW\Users\markgallie\My Documents\HireNumbersCJ(11.09.13).xlsx", False

.Run "PERSONAL.xlsb!SetUpPage"
End With

Set XLApp = Nothing
End Function

 

[emdools]

Thanks for this bit of coding as I also wanted to run a macro from a personal macro workbook. However I also have the issue where there could be multiple users for the database so amended the coding and replace the persons name with UserNameWindows to look like this instead:

.Workbooks.Open "C:\Users\" & UserNameWindows & "\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.xlsb", True

With UserNameWindows in a module:

Function UserNameWindows() As String
UserNameWindows = VBA.Environ("USERNAME")
End Function