Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveWorkbook.SaveAs - dealing with file already exists
Using Excel 97 SR-1.
I am using ActiveWorkbook.SaveAs to save an ".xls" file and I cannot find any documentation on how to handle file-already-exists errors. My intent is to try and save the file but if the filename is already used then to alter the filename and save again, repeating with different filenames until either the SaveAs works or the code chooses to give up. Currently, Excel pops up a message asking whether I want to replace the file, after clicking "no" I get another popup asking whether I want to debug the code. I do not want any pop ups. I just want to handle the file-already-exists case in the code. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveWorkbook.SaveAs - dealing with file already exists
check if the file exists, if not then use SaveAs, if it does, add a number,
and retest, incrementing the number until there's no existing file Sub tester() SaveFileAs "MyFile", "C:\Test\" End Sub Function SaveFileAs(sFilename As String, sPAth As String) As Boolean Dim fn As String Dim check As String Dim ok As Boolean Dim index As Long fn = sPAth & sFilename & ".xls" check = Dir(fn) ok = (check = "") Do Until ok index = index + 1 fn = sPAth & sFilename & index & ".xls" check = Dir(fn) ok = (check = "") Loop ThisWorkbook.SaveAs fn End Function "Adrian" wrote: Using Excel 97 SR-1. I am using ActiveWorkbook.SaveAs to save an ".xls" file and I cannot find any documentation on how to handle file-already-exists errors. My intent is to try and save the file but if the filename is already used then to alter the filename and save again, repeating with different filenames until either the SaveAs works or the code chooses to give up. Currently, Excel pops up a message asking whether I want to replace the file, after clicking "no" I get another popup asking whether I want to debug the code. I do not want any pop ups. I just want to handle the file-already-exists case in the code. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveWorkbook.SaveAs - dealing with file already exists
Hi
Here is some compilation from 2 of my projects .... ' Asking for file name Const fTitle = "Select File!" Const FilterList = "Templates (*.XLS), *.XLS" fFullName = Application.GetOpenFilename(Title:=fTitle, FileFilter:=FilterList) If fFullName = "False" Then MsgBox ("No file to open!") ActiveWindow.Close Exit Sub End If ' Storing file name and path, and sheet name varFile = fFullName Do While InStr(fName, "\") 0 fName = Mid(fName, CLng(InStr(fName, "\") + 1)) Loop varPath=Left(fFullName,Len(varFile)) varSheet = "MySheet" .... ' Checking, that file exist, and opening it or creating a new one Set fs = Application.FileSearch With fs .LookIn = varPath .Filename = varFile If .Execute(SortBy:=msoSortByFileName, SortOrder:=msoSortOrderAscending) 0 Then ' If found, then open Workbooks.Open (varPath & varFile) Else ' If not found, then create a new workbook with sheet MySheet in it, and save it Workbooks.Add ActiveWorkbook.Sheets("Sheet1").Name = varSheet ActiveWorkbook.SaveAs (varPath & varFile) End If Workbooks(varFile).Activate End With .... -- Arvi Laanemets ( My real mail address: arvil<attarkon.ee ) "Adrian" wrote in message oups.com... Using Excel 97 SR-1. I am using ActiveWorkbook.SaveAs to save an ".xls" file and I cannot find any documentation on how to handle file-already-exists errors. My intent is to try and save the file but if the filename is already used then to alter the filename and save again, repeating with different filenames until either the SaveAs works or the code chooses to give up. Currently, Excel pops up a message asking whether I want to replace the file, after clicking "no" I get another popup asking whether I want to debug the code. I do not want any pop ups. I just want to handle the file-already-exists case in the code. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveWorkbook.SaveAs - dealing with file already exists
Thank you, Patrick. The "dir" function achieves what I wanted. I knew
that there should be such a function but I could not find it. Also, to Arvi, I will study Application.FileSearch and the rest of the code that you posted. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveWorkbook.SaveAs - dealing with file already exists
the DIR( ) function works well for determining if ANY file or path exists
well too. I needed a way to know where the FTP.EXE program supplied with the windows install resided. Different operating systems keep it in different places... CheckWindows = Dir("C:\Windows\System32\FTP.EXE") CheckWinNT = Dir("C:\WinNT\System32\FTP.EXE") allowed me to alter the way I call the FTP so my spreadsheet can now work deployed on any of the current operating systems with out having to make changes. -- Regards, John "Adrian" wrote: Thank you, Patrick. The "dir" function achieves what I wanted. I knew that there should be such a function but I could not find it. Also, to Arvi, I will study Application.FileSearch and the rest of the code that you posted. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SaveAs and File Exists | Excel Programming | |||
How check for No/Cancel Button when SaveAs and file already exists? | Excel Programming | |||
Saveas where file already exists | Excel Programming | |||
activeworkbook.saveas | Excel Programming | |||
SaveAs command when a file already exists? | Excel Programming |