Making prior macros available to new workbook generated by client application

[RStevenson]

Hello,

My first post here. I've seen variations of this post on the forum but referring to Excel 2010, and without a solution I can use.

I'm now working with a third party application which can send any output, from any part of the system, to Excel (i.e. not always in a fixed layout). I have Excel 2013 and have written some VBA which allows me to perform certain generic tasks on the data, e.g. remove blank cols, prompt to sort, reorder cols etc.


Having gone to this trouble and saved in personal macro book I now found out that personal macro book isn't loaded if excel if launched from a 3rd party app.

Someone here said I could save, close, restart excel & reload. This will cause me no end of temporary workbooks & (surely!) extra effort. At least personal macro book would be available.

Alternatively, another user said if I had excel already open, personal macro book would be available to the newly spawned window. I tried this, with Book1, and personal macro workbook is visible there, but when I create a second Excel window from my third party app and view Macros/All Open Workbooks from there, personal macro workbook isn't listed.

Can anyone suggest how I can create a macro which can be run against an Excel sheet which is created by another program?

Thanks
Rob

 

[AkaTrouble]

you can export you macros and then import them into the new workbook

this could also be done by creating a copy of your personal XL workbook and importing the sheets from newly created workbook into that copy, in esscence creating a merged workbook

containing both your personal macros and the new data sheets.

this can be done using VBA

my coding not that good but have replied to let you know there are solutions to this many solutions infact i have only mention a couple

 

[RStevenson]

Thanks for the swift reply. I agree that would work, but I was hoping for the same functionality as if I open a new workbook from the Excel program, i.e. have access to all Person Macro Workbook macros automatically, since this process will be happening 50+ times per day, per user!

I just want to be able to automate, and in fact I have the VBA to automate, I just can't get to it!

Cheers
Rob