Pass a file name as a variable

[TrafficGuy]

I am calling a sub that I want to do a SaveCopyAs using a variable as the file name but can't get it to work. The file name displays properly in a message box but when I do a SaveCopyAs it does nothing.

This code works:

Please Login or Register  to view this content.

This code does not work:

Please Login or Register  to view this content.

The variable is NextWorkbook

What's my problem here? Do I have a syntax error or is it that I simply can't usr a file name passed as a variable?

Thanks for the help.

[Richard Buttrey]

If 'NextWorkbook' is declared as a string, and evaluates to "C:\TEMP\XXXX.XLS", then does

Please Login or Register  to view this content.

work?

Or is NextFileName just the xxxx.xls bit and you need to prefix it with the path/temp folder?

HTH

[TrafficGuy]

Thanks for the response.

OK, I’ll try to explain what I’m doing. I have a one destination workbook and several source workbooks. The destination workbook has a bunch of graphs and pivot tables that use data in one spreadsheet. The source workbooks have only one spreadsheet in each of them.

I am running a For Each/Next subroutine that works through all the open workbooks, copies the one spreadsheet into the destination workbook, does some stuff, then saves the destination workbook but with the name of the source workbook so the destination workbook isn't altered, each workbook copy is unique in terms of data and name. The For Each/Next subroutine is where the variable name comes form which I called NextWorkbook.

The name of the source workbook is passed as a variable to the destination workbook.

All the code runs from the destination workbook.

So, I hard coded the "C:\TEMP\XXXX.XLS" just to test my code and see if I could get it to work, the location and path are not relevant to this project.

I was simply trying to save it in whatever location it was originally opened from. I did the same thing using SaveAs and it worked fine but I don't want to change the name of my destination file that's why I went to SaveCopyAs but it doesn't seem to work the same.

So, what do you think, am I missing something simple, do I need to get the file path somehow, and if so how, any ideas?

[Richard Buttrey]

Hi,

In your loop, when you've copied the stuff into the destination workbook and if that's the active workbook at that moment, use

Please Login or Register  to view this content.

or directly refer to it if not active with either

Please Login or Register  to view this content.

Or if when you originally open the Destination workbook you set a workbook variable

Please Login or Register  to view this content.

then your code would ne

Please Login or Register  to view this content.

But if you're using the name of the source Workbook to pass as a variable, won't you be overwriting the source workbook, or does that not matter since you've effectively copied its data over anyway.

HTH

[TrafficGuy]

Thanks again for the help. Yes I am overwriting the source file on purpose for exactly the reason you suspected.

For some reason the problem seems to be in some difference between SaveAs and SaveCopyAs. SaveAs saves the destination file with a new name but SaveCopyAs seems to just end running the code and does nothing. The problem is that if I use SaveAs the destination file is saved as another name and the original destination file is closed causing the looping code to error.

This works:

Please Login or Register  to view this content.

This doesn't work:

Please Login or Register  to view this content.

So, what do you think?

[TrafficGuy]

I'm thinking that when I use SaveAs it knows the path of the file being saved but when I use SaveCopyAs it doesn't. Does that make sence?

How can I get the path for the location I opened the destination file from?

[TrafficGuy]

OK, so I hard coded the path and concatinated it to the file name that was passed as a variable so it looks like the difference is that SaveCopyAs doesn't know the path so it does nothing with out it.

Concatination:

Please Login or Register  to view this content.

So, how do I get the path of the folder that I opened the workbooks from, basically the path that the active workbook was opend from?

[Richard Buttrey]

Hi,

Use the CurDir instruction.

HTH

[Kuskush]

Try this:

Please Login or Register  to view this content.

Hope it helps

[TrafficGuy]

Thanks a lot, that worked perfect.