New workbook needs to know name of the workbook that opened it

[Cumberland]

Hi, I have a macro that opens a workbook. The newly opened workbook needs to know the name of the workbook that opened it. How do I pass the name of the original workbook to the new one?

Thanks

[Norie]

How is the first workbool opening the second?

Why does the second need to know the name of the first?

[Legend Rubber]

Is it always the same workbook that opens it?

[Sean Thomas]

you can use either

Please Login or Register  to view this content.

for path and name

or

Please Login or Register  to view this content.

for just the name of the workbook and file type

[Cumberland]

The source workbook can change, but it will always open the same destination workbook.

The source workbook opens the destination workbook via the Workbooks.Open command. The destination workbook then carries on the macro, but needs to switch to the source workbook a few times. Because the source workbook can be different, it needs to know which one opened it so that it can switch to the correct source workbook during the macro.

If anyone knows the anwser?

[Norie]

What do you mean the destination workbook carries on the macro?

Why no have all the code in one of the workbooks?

Or even in separate workbook that opens both the source and destination.

[Cumberland]

The source workbook can be one of 400 workbooks, so it would be impractical to have the code in all 400. If I ever had to change the code, I would have to change it 400 times.

The destination workbook (let's call it the 'adjusting' workbook) has to make some changes to the source workbook. The source workbook (whichever one it might be at the time) has a button that the user clicks which literally opens the 'adusting' workbook. When the 'adjusting' workbook opens, its own macro code starts to run, to make the necessary adjustments to the source workbook.

does that help?

[Norie]

The destination workbook needs to change the source workbook?

Isn't it usually the other way round?

I can see it being impractical to have the same code in all 400 workbooks, so what about a separate workbook that allows the user to select
the source and destination files, does the adjustment and closes both files.

Where do you actually have the code at the moment anyway?

You seem to be saying it's in the source workbook.

[Cumberland]

I understand that this sounds confusing, so let me try and put it another way.

The source workbook contains data that the user manually changes. The user could insert or delete rows, amongst other things, and therefore the printing setup gets warped. Once they have made their changes, they click a button at the top of the workbook (all 400 workbooks have this button) which has a one-line command to open the 'adjusting' workbook. The 'adusting' workbook then executes its own code and makes the necessary changes to the source workbook to ensure that the printing setup is corrected.

So the source workbook only has one command, behind a button, that opens the 'adjusting' workbook. No other code. The 'adusting' workbook contains all of the code to make the adjustments to the source workbook.

Like I say, I could have the adjusting code in all 400 workbooks, but if any changes to the code were needed, it would be a nightmare. So the idea is that I only need to change the code in one workbook if ever I need to.

So, the 'adjusting' workbook needs to know the name of the source workbook so that it can activate it during the code run.

I hope that clarifies what I'm trying to do.

[Norie]

If the source workbook is the only workbook open when you open the 'adjusting' workbook you can refer to it with Workbooks(1).

If it's not then you'll need to either put it's name somewhere in the 'adjusting' workbook, perhaps in a name.

Then you would need to rewrite the code in the 'adjusting' workbook to use the name.

An alternative would be to rewrite the code to take the source workbook as an argument.

Then you can add another line of code in the source workbook to call the code in the adjusting workbook and pass the source to ti.

[Cumberland]

Thanks, I've worked it out now. The code behind the button now has three lines of code which copies the source workbook name into the adjusting workbook and then invokes the adjusting workbook macro code and it picks the filename up there. I can cope with three lines of code in 400 workbooks!

Anyway, it's working now. Thanks for your help.