Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
the file already exists - do you want to replace the existing file?
I've got a VBA procedure that saves a record to disk using
Application.GetSaveAsFilename. However, if the file it's trying to save already exists, the Windows API generates the above message, and the user's only option is to either replace the existing file, or manually rename the existing file in the File Save As. What I'd really like to do is modify the code so that when it's run, it takes care of renaming the existing file (if it exists) by adding a "1" to the end of the filename, then saving the file in memory with the original name, all without requiring the user to do anything. For simplicity, I'd be happy to keep appending the character "1" to the end of any previously renamed files in case this operation is performed multiple times, instead of trying to increase the appended digit by 1 each time. Is there any VBA code I can use to accomplish this? Thanks in advance. Paul |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
the file already exists - do you want to replace the existing file?
Paul James wrote:
I've got a VBA procedure that saves a record to disk using Application.GetSaveAsFilename. However, if the file it's trying to save already exists, the Windows API generates the above message, and the user's only option is to either replace the existing file, or manually rename the existing file in the File Save As. Something along these lines: Dim V Dim stOther As String Dim iVer As Integer V = Application.GetSaveAsFilename(.....) If TypeName(V)="Boolean" Then Exit Sub ' cancelled ' does it exist? If Dir(V)<"" Then ' yes stOther = V ' remove extension if present If Right(LCase(V),4)=".xls" Then stOther=Left(stOther,Len(stOther)-4) ' find an unused numerical suffix to the filename iVer = 1 Do While Dir(stOther & iVer & ".xls")<"" iVer = iVer + 1 Loop ' rename the existing file Name V As stOther & iVer & ".xls" End If ' save the current file ActiveWorkbook.SaveAs V Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - reply in newsgroup |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
the file already exists - do you want to replace the existing file?
Great code, Bill - works just fine. It looks like more than a little
thought went into creating it. Thank you so much for sharing it with me and the rest of the group. For the benefit of others who may be interested in this procedure, I wrote the following line: Name V As stOther & iVer & ".xls" as V = stOther & iVer & ".xls" and the parameters I used for GetSaveAsFilename(.....) a GetSaveAsFilename("MyData.xls", filefilter:="Excel Files (*.xls), *.xls") Paul |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
the file already exists - do you want to replace the existing file?
Paul James wrote:
For the benefit of others who may be interested in this procedure, I wrote the following line: Name V As stOther & iVer & ".xls" as V = stOther & iVer & ".xls" OK - I thought your aim was to rename the existing file. Your modification changes the name of the file you are saving to. Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - reply in newsgroup |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
the file already exists - do you want to replace the existing file?
Actually, Bill, you had it exactly right - my preference was to rename the
existing file. But when I first tried running your original code, I got an error message. So I rewrote it as I described in my last message, and at least it ran, although not exactly as desired. However, your follow up message inspired me to take another shot at getting the "Name V As stOther . . . " line to work, and, voila!!! It works just as you originally wrote it in your message, and it does exactly what I had hoped to accomplish: it renames the existing file on disk, and saves the open file with the specified file name. I don't know what I was doing the first time to cause the error, but it works fine now. So many thanks again. Paul |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
User replaces existing file with blank during file open | Excel Discussion (Misc queries) | |||
Importing text file, only option to edit existing file | Excel Discussion (Misc queries) | |||
can i save an existing .xls file as a .csv file using command line | Excel Discussion (Misc queries) | |||
How do you replace an existing file, saying "yes" in a macro? | Excel Programming | |||
Automatically replace existing file on save | Excel Programming |