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?
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?