Global Macros in Excel v.X.

[David Vega]

I receive a spreadsheet at least once a month from a client, and I
have developed a macro that works within this sheet to simplify
certain processes. The client wants the spreadsheet back without
macros.

Currently I:
1) open their spreadhseet
2) create a new macro and copy in my macro
3) run the macro and do whatever I need to do
4) remove the macro and save
5) curse if I forget to do something meaning I must repeat 1-4.

Is there anyway I can do either ...
1) have the macro globally accessible by any spreadsheet
2) have my macro loaded automatically via another spreadsheet or
something.

Thank You In Advance
David V Vega

 

[Bernard Rey]

David Vega wrote :

I receive a spreadsheet at least once a month from a client, and I
have developed a macro that works within this sheet to simplify
certain processes. The client wants the spreadsheet back without
macros.

Currently I:
1) open their spreadhseet
2) create a new macro and copy in my macro
3) run the macro and do whatever I need to do
4) remove the macro and save
5) curse if I forget to do something meaning I must repeat 1-4.

Is there anyway I can do either ...
1) have the macro globally accessible by any spreadsheet
2) have my macro loaded automatically via another spreadsheet or
something.

Yas, you can easily do so. Copy your macro on any sheet in order to have it
running from this one, including the "Personal Macro Workbook" (opens every
time Excel is launched) if you want this macro to be ready at any moment.
But if it's only once a month, you can simply keep it attached to another
workbook, maybe with a Toolbar button to launch it more easily...

But you'll probably have to make some changes inside your macro, in order to
have it running correctly: where you probably have some references to the
concerned workbook, it may have to be mentioned more precisely. But that
depends a lot upon your macro and the instructions inside.

Maybe you could post a couple of lines from your Macro in order to let us
see the best way to help you out.

 

[JE McGimpsey]

I receive a spreadsheet at least once a month from a client, and I
have developed a macro that works within this sheet to simplify
certain processes. The client wants the spreadsheet back without
macros.

Currently I:
1) open their spreadhseet
2) create a new macro and copy in my macro
3) run the macro and do whatever I need to do
4) remove the macro and save
5) curse if I forget to do something meaning I must repeat 1-4.

Is there anyway I can do either ...
1) have the macro globally accessible by any spreadsheet
2) have my macro loaded automatically via another spreadsheet or
something.

What I would do (actually, what I do) is put the macros in an add-in. An
add-in is simply a workbook that has all its sheets hidden.

To do this, put the macro(s) in a blank workbook and save it. In the
Save dialog, choose Excel add-in in the Format dropdown. Choose either
the Add-in folder (Microsoft Office X:Office:Add-ins), or, I recommend
putting it in your MUD folder, so that if you ever have to reinstall
Office you won't lose your add-in. When you want to load the add-in,
choose Tools/Add-ins... and check the appropriate checkbox.

If you want the macros available all the time, put the add-in in your
Startup folder (Microsoft Office X:Office:Startup:Excel).

 

[David Vega]

What I would do (actually, what I do) is put the macros in an add-in. An

add-in is simply a workbook that has all its sheets hidden.

To do this, put the macro(s) in a blank workbook and save it. In the
Save dialog, choose Excel add-in in the Format dropdown. Choose either
the Add-in folder (Microsoft Office X:Office:Add-ins), or, I recommend
putting it in your MUD folder, so that if you ever have to reinstall
Office you won't lose your add-in. When you want to load the add-in,
choose Tools/Add-ins... and check the appropriate checkbox.

If you want the macros available all the time, put the add-in in your
Startup folder (Microsoft Office X:Office:Startup:Excel).

Thanks for the advice. One question, what should I change in the
macro? I currently use ThisWorkbook.Sheets("sheet1").Range for
example. ThisWorkbook doesn't work.

Regards,
David

 

[Bernard Rey]

David Vega wrote :

Thanks for the advice. One question, what should I change in the
macro? I currently use ThisWorkbook.Sheets("sheet1").Range for
example. ThisWorkbook doesn't work.

ThisWorkbook refers to the workbook to which the macro is attached. If the
Workbook you want to refer to is the frontmost one when you launch the
macro, you can refer to ActiveWorkbook instead.

If the Workbook your client send to you always has the same name, you can of
course use its name, something like: Workbooks("MyClientsBook.xls")