• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

SaveCopyAs in VBA

David Evans

David Evans

Active Member
I use ActiveWorkbook.SaveCopyAs Filename to store a copy of the worksheet under a different name, while the "original worksheet" continues looping through other work.

Obviously the original worksheet is VBA controlled, but I don't necessarily want that VBA to function on the ActiveWorkbook.SaveCopyAs - i tried getting it t save the file with an .xlsx extension which it does, but it won't open ...

Anyone got any thoughts? Part of the issue is that the ActiveWorkbook.SaveCopyAs File Auto Opens but fails as the filename has now changed - should I just have a check in the Auto Open - if the filename is not equal to what the Mother file is, then stop all code?

You folks around here are usually full of elegant solutions, so I thought I'd try you ....:awesome::DD
 
Luke M said:
Hi David,

It looks like doing a SaveAs may be the best method.
http://stackoverflow.com/questions/...-containing-macro-to-a-workbook-without-macro

code from linked answer
Code: 
Application.DisplayAlerts = False
ThisWorkbook.CheckCompatibility = False
ThisWorkbook.SaveAs Filename:="D:\DOCUMENTS\Book1.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Application.DisplayAlerts = True
Click to expand...

My question is this - and I'll try out shortly, but does it change the name of the current Open Workbook? If so, it defeats my purpose as the "Mother of all Workbooks" has to loop through about a thousand iterations :eek:
 
David Evans said:
My question is this - and I'll try out shortly, but does it change the name of the current Open Workbook? If so, it defeats my purpose as the "Mother of all Workbooks" has to loop through about a thousand iterations :eek:
Click to expand...

Yes - it does change the name of the current file, which is what i need to avoid - that's why I was using Save copy as .... Thanks for trying anyway, Luke. Keep grinding your thinking gears!
 
I thought there might be a problem, but one last shot....
When you save a file that was previous .xlsm into a .xlsx, the code doesn't immediately go away, as long as file is still open. So, the code could still keep running.

What I think might happen:

MasterBook.xlsm gets opens
Start code...
Being loop...
Save as Book.xlsx
code keeps running
save as Book2.xlsx
....
save as Book1000.xslx
end loop

At this point, you could either re-open the MasterBook from whereever it is stored, and close the book running the code (which has changed names several times, but was originally the master), or you can just close the current book.

Does that sound feasible?
 
Luke M said:
I thought there might be a problem, but one last shot....
When you save a file that was previous .xlsm into a .xlsx, the code doesn't immediately go away, as long as file is still open. So, the code could still keep running.

What I think might happen:

MasterBook.xlsm gets opens
Start code...
Being loop...
Save as Book.xlsx
code keeps running
save as Book2.xlsx
....
save as Book1000.xslx
end loop

At this point, you could either re-open the MasterBook from whereever it is stored, and close the book running the code (which has changed names several times, but was originally the master), or you can just close the current book.

Does that sound feasible?
Click to expand...
The issue with this is that as soon as the Workbook name changes, I run into issues with the code referring to the original Workbook name .... which i could probably clean up ... I need to ponder it further ...
 
My recommendation is to try and avoid using names for things. When in a Sheet module, you can do this with the "Me" object
Code: 
Me.Range("A1").Select

With workbooks, some people tend to use ActiveWorkbook, but since we can't always guarantee which workbook is "active" (in case other books get opened), it's better to use the ThisWorkbook obect, which always refers to the workbook from which the code is running. So, even though the workbook names keeps changing you could do:
Code: 
ThisWorkbook.Worksheets(1).Select
'Or even before hand, do this
Dim myWB as Workbook
Set myWB = ThisWorkbook

myWB.Worksheets(1).Select
Does that make sense? Usually I find I'm able to do a big Find&Replace to change from:
Workbooks("My book.xls")
to:
ThisWorkbook
 
Thanks for the reply - I've often thought using ThisWorkbook, but never quite got there ...

The issue I was having is that the code I used to create the files is not really necessary once a file has been created. You can't use the expediency of just making a file an .xlsx and hoping the code gets lost in the shuffle. So what I do is have the Auto_open test for the filename and if its not the "Mother File" it just shuts down .... may not be elegant, but it works :cool:
 
You must log in or register to reply here.
Back
Top