Excel VBA Change File Name

[myrandr]

OK...I have been a long time lurker, have done a forum search and can't seem to find the solution to my VBA issue.

Crux of what I am trying to accomplish - I have a workbook template I am distributing via SharePoint, once opened by the user I am firing the save code from a button. When this code fires, it creates a folder and saves the document to a "user" document library - this code works fine. The issue I am running into is doing the follow-up check when the user saves subsequently so if the customer name is updated or a customer number is changed (many times a customer number is not available on the initial save) it renames the folder and workbook to this new information.

So here is the issue I am running into: The code I have changes the directory folder and workbook name without issue but does not change the name of the document that is open (which is the document that is changing in the folder itself). This causes the "save" function to fail as the document no longer exists in SharePoint (as it's been renamed as I want it to be).

I know that's a lot of writing but hope it helps in understanding the issue. Here is the code that does the name change on the folder and file - if more code is needed to understand, please let me know. I am working in Excel 2003, and SharePoint 2003.

If Dir("\\las\LAS Document Library\" & CurrentDir, vbDirectory) = "" Then
MkDir ("\\las\LAS Document Library\" & FileNameSave)
ActiveWorkbook.SaveAs Filename:="http://las/LAS Document Library/" & FileNameSave & "/" & FileNameSave & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ElseIf UpdateFlag = 1 Then
'Change Name of file
Name "\\las\LAS Document Library\" & FileNameSave2 & "\" & FileNameSave2 & ".xls" As "\\las\LAS Document Library\" & FileNameSave2 & "\" & FileNameSave & ".xls"
'Change Name of directory
Name "\\las\LAS Document Library\" & FileNameSave2 As "\\las\LAS Document Library\" & FileNameSave
Else
ActiveWorkbook.Save
End If


Any help or suggestions would be greatly appreciated as I am stuck on how to fix.

Thank you!

 

[phagan]

why not use ActiveWorkbook.SaveCopyAs instead of .SaveAs

 

[myrandr]

why not use ActiveWorkbook.SaveCopyAs instead of .SaveAs

since I am distributing a template version of the workbook I did not think their was a difference - .SaveAs creates a copy of the template. If .SaveCopyAs performs differently than I will certainly switch that snippet of code.

The piece of code I am focused on, and should have identified more clearly is this:

Name "\\las\LAS Document Library\" & FileNameSave2 & "\" & FileNameSave2 & ".xls" As "\\las\LAS Document Library\" & FileNameSave2 & "\" & FileNameSave & ".xls"

Where FileNameSave2 is the original concatenation of the customer name and customer number and FileNameSave is the concatenation of the changed customer name and or customer number.

The code changes the name of the file within the folder- the file I have open - but does not change the name of the open file (I then essentially have a file open that is no longer in the folder).

 

[phagan]

.SaveCopyAs will save a copy of the workbook with whatever name you want but the activeworkbook will be the same as when you started.

It is the same as making a copy of the workbook in windows explorer and renaming it.

.SaveAs will save the workbook with whatever name and destination you assign it and make that the activeworkbook. So that it is no longer located in the original directory.

Which would be the same as clicking File, then Save As.

 

[myrandr]

I found a solution, well, work around for those interested. I moved the code to change the Dir and File name to the BeforeClose procedure.

 

[RickTerminal]

I found a solution, well, work around for those interested. I moved the code to change the Dir and File name to the BeforeClose procedure.

Well what do you know; all this time after your thoughtful last post explaining how you beat the problem and it did come in useful! Thanks for sharing the eventual solution