How to save existing Workbook with a new new name, without changingthe open Workbook name?

[HammerJoe]

Hi,

I am using a regular spreadsheet called tracker and every week it is
reset and the values entered are deleted
But before the table is deleted I would like to be able to save it
with as week range name. (ie jan28-Feb02.XLS)
I used record a macro to get the code but the problem with this is
that it also changes the open workbook file from Tracking.xls to jan28-
Feb02.XLS.

How do I save to a new name without changing the open file name?

 

[JLatham]

I think this will do it for you. It will ask you for a new name and you give
it one (leaving the entry blank or hitting the [Cancel] button will abort the
operation). It verifies that it has a .xls file extension, and that all
characters used in the name are legitimate (any illegal ones are converted to
the underscore character: _ )

After saving with the new name, it saves it again using the original name so
that it almost looks like nothing happened except that the computer became
unresponsive for a while (while it was doing the double file save).

Sub SaveWithNewName()
Dim newName As String
Dim oldName As String
Dim LC As Integer
Dim illegalFilenameCharacters As String

illegalFilenameCharacters = "?[]/\=+<>:;,*|^" & Chr$(34)
'note: ^ is legal in Windows world,
'but not in Mac world, included for cross-platform compatibility
newName = InputBox("Enter name to save the file as (i.e.
04Feb-08Feb.xls)", _
"Save With New Name", "")
'remove any leading/trailing spaces
newName = Trim(newName)
If newName = "" Then
Exit Sub ' no new name provided, exit without saving
End If
'validate the user input
'this assumes pre Excel 2007 file type with .xls as filename extension
If Len(newName) < 4 Or UCase(Right(newName, 4)) <> ".XLS" Then
newName = newName & ".xls" ' seemed to be missing
End If
'replace any illegal characters with the underscore character
For LC = 1 To Len(newName)
If InStr(illegalFilenameCharacters, Mid(newName, LC, 1)) <> 0 Then
newName = Replace(newName, Mid(newName, LC, 1), "_")
End If
Next
'before saving as new name, remember the current name
oldName = ThisWorkbook.Name
ThisWorkbook.SaveAs newName
'now save again as the original name so it looks just like before
'but don't display the "File Exists, overwrite?" prompt
Application.DisplayAlerts = False
ThisWorkbook.SaveAs oldName
Application.DisplayAlerts = True ' turn them back on
MsgBox "File was archived as: " & newName
End Sub

 

[Tim Zych]

Another approach is to save the workbook first, then copy the file as a new
name. This is the equivalent of copy/paste in Windows Explorer.

An MS support article shows how to copy files that are open, since FileCopy,
VBA's built in file copier, might not work if the file is open. Since the XL
file being copied is always open (presumably in Read/Write mode), FileCopy
will result in a Permission Denied error. That can be circumvented by
changing the XL source file mode to ReadOnly before using FileCopy, e.g.:

ThisWorkbook.Save
ThisWorkbook.ChangeFileAccess xlReadOnly
FileCopy ThisWorkbook.FullName, ThisWorkbook.Path & "\Dec.xls"
ThisWorkbook.ChangeFileAccess xlReadWrite

Alternatively:

FileCopy Statement May Not Copy Open Files
http://support.microsoft.com/default.aspx?scid=kb;en-us;172711

So I'd imagine it would be:

Result = apiCopyFile(ThisWorkbook.FullName, ThisWorkbook.Path &
"\jan28-Feb02.XLS", True)

Not that it's that easy, since there are other considerations, such as
checking to make sure the file being copied to is not open read/write by
anyone (e.g. is replaceable), and new filename and/or directory validation
(see JLatham's post for filename validation). From your description it
appears as though most of that is not a concern, so I'm going to grant
myself a little laziness and not write out a big-ass macro that would go
mostly unused.

 

[Peter T]

Just last December I also posted a cunningly clever way to achieve the
objective then along came Ron de Bruin with simply this -

SaveCopyAs

Regards,
Peter T

 

[Tim Zych]

Of course..that's what I was looking for, but didn't find it in the UI.
Damn, wish I posted that.

 

[JLatham]

Leave it to Ron de Ruin to run around bustin' balloons! I hadn't even
thought about SaveCopyAs ... probably because I don't generally use it. But
it's a damned good suggestion in this situation (and in some of my older
files where I use the method I laid out earlier).

 

[HammerJoe]

Brilliant.

Who would thought that it would be that easy?

Thanks.
Jlatham I like your code thou... Nice work.

 

[JLatham]

Combine my 'get filename from user' and filename validation code along with
Ron's easy-fix and you should have a solid function at your disposal.