PDA

View Full Version : [SOLVED] How To Load Macro onto Exported Excel Sheet



cereal
04-11-2018, 07:01 AM
Hi, so I have a lengthy macro designed to take in data from a data report and create a summary. The macro works well enough for what I want it to do, but I need the macro to be available on every Excel workbook so that I can use the macro in an efficient manner. Currently, I have the macro in a Personal workbook, so the macro is loaded in every time I open excel because the Personal workbook opens at the same time hidden in the background. However, there is a significant problem.
I use a separate application that finds the data and exports the data into an Excel .xls file. My macro is designed to deal with this file, however, the Personal workbook does not open when the exported Excel file is automatically opened after exporting. If I save, close, and reopen, the Personal workbook will be open, but that is inconvenient and a hassle. Is there a way to either open the personal workbook when the new Excel workbook is exported or ensure that my macro will automatically be present in the new file? Thanks!

TL;DR Personal workbook doesn't open when Excel workbook is exported from another application. How do I get my macro to be on the file?

Bob Phillips
04-11-2018, 11:46 AM
Why not merge the separate application into personal.xlsb?

cereal
04-11-2018, 12:40 PM
How does that work?

cereal
04-11-2018, 12:42 PM
I'm not sure I understand. The separate application is a actual program. It's a software application that connects to a machine to collect and analyze data

SamT
04-12-2018, 06:49 PM
Step 1... Other Application creates a new Workbook.
Step 2...You want a macro put in that new Workbook before you open it with Excel.

:devil2:

Only solution I can think of is to open Excel from Windows Start menu, then Open the New workbook from Excels File >> Open menu

That's the way I have to open Excel >= 2007 files I DL.

offthelip
04-13-2018, 04:18 AM
Another way you could solve the problem is to put the macro into a separate file and save it as an addin file (.XLAM)
you can then save it in the special location for VBa addins which in win 7 is c/user/appdata/Roamiing/microsoft/addins

then use the EXCEL options /addins to selects and install it as an addin.

I do this for all my userdefined functions and a number of macros and

cereal
04-13-2018, 08:07 AM
Thanks for all your help!

I took offthelip's advice and just made the macro an add-in file. It even still works with the keyboard shortcut I had set for the macro, so this works perfectly. Thanks again!