Macros - Can you use them across multiple workbooks?

jed776

New Member
Hello,

I have used macros in Excel before, but in the past they were always used on nice worksheets that had the macros ingrained. I have a different situation now though, and can't seem to wrap my head around it.

For setup, I have 2 documents I have created:
1. Data.xlsx (which I am manually turning into a .xlsm so this will work)
2. Macro File.xlsm

My goal is to use Macro File.xlsm as a configuration-type file for Data.xlsx. My process can turn Data.xlsx into Data.xlsm, but then I need to use the MS Excel VBO - Run Macro action to push the macro into Data.xlsm. I just can't figure it out though.

The Run Macro action says to provide Handle and Macro Name. But I don’t see how to apply that Macro name to a separate workbook like I need to do. Reading the information from the action it looks like all I should have to do is point to the handle of the workbook containing the macro, then for Macro Name give it - [Workbook Name]&”!”&[Macro Name] and it should connect, but it always errors out and tells me: “The macro may not be available in this workbook”. I've also tried getting more specific, writing - "'"&[Workbook Name]&"'!"&[Worksheet Name]&"."&[Macro Name] but I get the same error.

Has anybody ever managed to utilize this functionality successfully? What am I missing?
 
Did you check manually running the macro in the Data.xlsm file once your process turns the xlsx to xlsm? If they are available, try creating a separate handle for Data.xlsm file & close it once your activities are done.
 

jed776

New Member
Yes, the macro works just fine when I run it within excel itself. I assign handle 0 to the macro file, and handle 1 to the Data.xlsm file. Maybe Run Macro doesn't work the way I think it does, or I'm just mistaken on my syntax when trying to use it.
 
Top