[sims] Excel Reports - Macro's do not exist! Help please?

[rhysduk]

Hi all,

I have an Excel report that dishes out Assessment Results from SIMS.

The workbook has an Auto_Open macro that runs (correctly) and does everything it needs to, however any other macro's (for navigating) worksheets do not exist in the workbook when it is run via SIMS. They are working just fine in the template.

Macro's are enabled (otherwise the Auto_Open) macro won't run!

 

Any ideas please?

 

Thanks

Rhys

[MrMat]

This is actually how it's designed to work which makes it a little difficult to resolve.

 

The template is actually just the middle man, all the macro contained within doesn't actually end up in the resulting Excel document. Think of it like this:

 

- The report is ran within SIMS. This outputs a Txt file containing all the data

- Your template is then opened and the macros are run

- One of the jobs of the Auto_Open macro is to create a new Excel document and copy all of the Text file into this new document. The template file never actually contains the data from SIMS, all it's Macros are ran against the newly created Excel document

- Once it's done it's job. The Excel template is then closed (along with your navigation macros!)

- You're left with a nice tidy newly created Excel document containing zero macros!

 

I think what you will need to do is write some VB that copies your navigation macros and any command buttons etc across from the template doc into the new doc.

[Damon]

MrMat is correct but I believe the last statement raises complications unless Excel has Trust access to the VBA project object model enabled (in trust centre > macro settings). You may wish to open the reportdata then copy the data back into the template rather than the traditional CAPITA approach. There are a few things to overcome but it can be done.

[Damon]

Actually, scrap that, you could put the code you need to transfer into the worksheets within the project as opposed to the modules.