I made code in ms access to create an excel file with MS Access data. This work fine, but when it has been created I want to start an Excel macro that is stored in personal.xlsb. Is this possible? I tried all the options I could find. The personel.xlsb was loaded when I created the Excel file. Here is a piece of the code I used:
dim xlApp as Excel.application
Set xlApp = CreateObject("Excel.Application")
'starting macro, underneath two of the lines I tried, but it will not start
xlApp.Run (ActiveWorkbook.Name & "!" & "Module2" & "My_macro"
xlApp.Run ("C:\users\myname\AppData...\PERSONAL.XLSB" & "!" & "Module2" & "My_macro"
The error message was that it could not find my macro (error 1004). The macro creates a form button and does some formatting.
xlApp.Run ActiveWorkbook.Name & "!" & "Module2" & "My_macro" will fail because there is no Active Workbook. The Personal workbook is never "active" - active means that it has the Focus. Same is true, btw, for Excel Addins (xlam).
Assuming that you have a Personal Workbook, the following should do:
xlApp.Run "Personal.XLSB!Module2.My_macro"
Note that you need to provide only the workbook name (Personal.XLSB), not the full name.