Running personal.xlsb macro without opening excel

Question

I would like to run macro stored in personal file without opening my actual excel file.

How do i modify the following code to achieve this

Set objExcel = CreateObject("Excel.Application")
objExcel.Application.Run "'D:\Sample SSRS\power View\AlertHistory.xlsm'!Module1.getDataImported"
objExcel.DisplayAlerts = False
objExcel.Application.Quit
Set objExcel = Nothing

My other question is I have tried recording a macro that opens the excel mysql option and appends data but that functionality is not being recorded. I would like to append all data from sheet 1 to my mysql table

Answer 1

I was struggling with the same problem and I eventually meandered my way to a solution.

Basically, I:

• programmatically opened the workbook in which I needed to run the Personal Macro,

• used Application.Run to call up the Personal Macro file (PERSONAL.XLSB),

• then specified the module and then macro to run within it.

It seems to work ... I don't know if it's a super elegant solution, though.

Below is the code. Note that Jan2017xlsm is the folder location of the file I needed to run the personal macro in.

Answer 2

The code is right, you just need to change the path and maybe the module name. An easy and robust method of getting your personal path is to unhide your personal file (View > Unhide...):

Then do a 'save as'. Don't actually save it. Just hold shift and right-click on the file > click 'copy as path':

Then paste into your VBScript. This will give you the file path including filename. In the code, the 'Module1' is the name of the module that contains the code you want to run. The 'getDataImported' is the name of the macro (within the module) that you want to run. Change those as required.

Remember to view > hide your personal file again, otherwise it will always be visible every time you open Excel.