Copy master workbook and catch error from copied one

[jaryszek]

Hi Guys,

i have master workbook with code like here:

In module:

Please Login or Register  to view this content.

and in ThisWorkbook event:

Please Login or Register  to view this content.

Now macro is copying master workbook, doing calculations there (whatever!) and what i want to catch is error while copy workbook is closed.
So if error (in example i am reising it especially) pass copy master name to main sub into: "CopyWorkbookError" procedure.

How can i do this?

Best,
Jacek

[Bernie Deitrick]

I'm not sure what you want to do - you can pass values between modules by declaring a variable at the top or your module as Global, like

Global strWBName As String

When you assign a value to strWBName, that value will be available to all procedures (to read or to change).

You could also make your workbook into a template and then when it is opened, the user will be forced to save it with a different name.

[jaryszek]

Thank you Bernie!

Please Login or Register  to view this content.

i know but what about passing errors from one master workbook to its copy?

You could also make your workbook into a template and then when it is opened, the user will be forced to save it with a different name.

this is not want i want.

Generally every master copy has closing trigger --> i am refreshing tables there.
I have also code from master which is copying master workbook and doing some code and when they are closed --> trigger is also fired to refresh table.

And my core issue here is that sometimes in copy i can get error while refreshint tables - i have to log this somehow.
For examply i have 5 different copies and running code from master.
In copy number 4 i am getting error, how to pass it to master code? How to raise it in master, not a copy?
It is possible?

Jacek

[jaryszek]

Hi Guys,

anyone?

Jacek

[Bernie Deitrick]

If you are running the code from the master workbook, you should not have any code in the copies - save the copies as .xlsx files, then pass their workbook object as a parameter to your code in the master. Then any error raised will be in the master.

[jaryszek]

Hi Bernie,

i am not entirely understand:

If you are running the code from the master workbook, you should not have any code in the copies

If you are creating copy from xlsm to xlsm it will have VBA code inside after copying. You mean to not copy as xlsm only as xlsx?

then pass their workbook object as a parameter to your code in the master.

you mean workbooks open method?

Ok but i will return to my source question.
It is possible to :
do a copy for master, run code on this opened copy and if error raised --> pass variable into master ?

Jacek

[Bernie Deitrick]

When you make the copy, use code like this to create an object that you can address in your code - when you want to process the copy, use the wbCopy object - then, any error raised in your will be raised in the workbook with the code

Please Login or Register  to view this content.

[jaryszek]

thank you,

it is good idea.

And this is only one way to do this and pass error from copied version?

Jacek

[Bernie Deitrick]

No - but all the methods require some inter-workbook communication. If you can't think of a dozen more ways to do any task in Excel then you're not really trying But the way that I outlined would be my preferred method.

[jaryszek]

thank you Bernie.

Best,
Jacek