Running Macros from a separate Workbook

[Rabi]

Hello geeks

This time, I came to you guys with another challenge.

Our department receives weekly and fortnighly updated workbooks from a number of different departments. Each workbook contains 10s of worksheets. Our department analyzes the data and generates reports from those worksheets.

I have developed a few macros to automate the report generation for making the computing of my colleagues easier.

Now, the problem is that all the macros are developed behind individual sheets and some modules. For this reason, my colleagues have to copy each updated sheet and paste over the older ones individually, each and every time. It is really quite time-consuming task - deleting older data from all the worksheets and then copying fresh data on each of them, one by one.

They want to make their life easier by just replacing the entire workbooks with the new ones and still want the macros functioning, as usual.

My concern is how I can put all the code in a separate workbook and how to reference data from the other workbook.

Or if there is an entirely different solution, please suggest.

Please help me in resolving this issue.

Regards,

[royUK]

It's difficult to say without knowing what the code is. The best way to share the code is to create an Excel addin which can be installed on each PC.

To do this you must place all your code in a new workbook, but make sure that all references refer to the ActiveWorkBook, etc. Then probably you wil need to add a menu & when completely tested save the workbook as an Excel addin (*.xla)

[Rabi]

It seems a brilliant idea to use Add-in for the shared code. Unfortunately, I am a novice in Excel (not coding) and therefore, I will need further assistance in how to create an Add-in.

Further help will be highly appreciated.

[gjcase]

I have done a similare thing. I just located the code in a separate workbook and then linked buttons on the other workbooks to that. It works fine so long as the workbook containing the code is available to the person trying to execute the code. Don't know whether there may be conflicts if sevearl tried to access at once, but I expect it should work.

---GJC

[royUK]

It seems a brilliant idea to use Add-in for the shared code. Unfortunately, I am a novice in Excel (not coding) and therefore, I will need further assistance in how to create an Add-in.

Further help will be highly appreciated.

As I said, the first thing is to place the code in a new workbook, check it works OK on the new workbooks -i.e make sure all references to sheets, ranges etc refer to the active workbook. Then decide how the user will trigger the macros. Buttons aren't much use because they won't be in the workbook anyway so you need to create a menu. Then save it as an addin.

Create a menu:

http://www.excelforum.com/showthread...highlight=menu

[Rabi]

Thanks to royUK for the support. I have built a small Add-In with a single Menu Option and it is successfully generating reports. Though I am still stuck with one problem, as below:

I want my Add-In see the names of the worksheets in the workbook; which is using this Add-In. If the required worksheets are not found, it should inform the user about the situation.

Please help.

I request gjcase to elaborate more on his solution; how he linked buttons to another workbook.

[royUK]

This Fnction checks if a sheet exists, place it in a Standard Module in your addin.

Please Login or Register  to view this content.

Example tests if a sheet exists, this code will be in your addin too

Please Login or Register  to view this content.

[Rabi]

I have to extend my gratitude to royUK for his very professional help in completing my Add-In code.

[royUK]

I have to extend my gratitude to royUK for his very professional help in completing my Add-In code.

Thank you Rabi

[gjcase]

Re your question on linking buttons to another worksheet: You do this the same way as linking them to macros in the existing worksheet. Create the button, right-click it, select Assign Macro. However, ensure that the workbook with the macro is also open; then select the macro worksheet from the Macros in: dropbox. Hit OK & that's it. Note that the user of the spreadsheet with the button must have read access to the worksheet with the macro in order for this to work.

The reason I have done this vs the add-in was that I wanted to restrict use of the macro to a cerain set of people, and it was easy for me to do that by controlling access to the file with the macros. Otherwise, the add-in is probably a better solution.

Hope this helps.

---GJC