| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rename rather than overwrite existing file
Hello all,
I have a workbook that saves a file with the following code: Function DoesPathExist(myPath As String) As Boolean Dim TestStr As String If Right(myPath, 1) < "\" Then myPath = myPath & "\" End If TestStr = "" On Error Resume Next TestStr = Dir(myPath & "nul") On Error GoTo 0 DoesPathExist = CBool(TestStr < "") End Function Dim myParentFolder As String Dim myFolder As String myParentFolder = "U:\" Application.DisplayAlerts = False If DoesPathExist(myParentFolder) Then On Error Resume Next MkDir myParentFolder & mycsj MkDir myParentFolder & mycsj & "\Pay Reports" MkDir myParentFolder & mycsj & "\Pay Reports" & "\" & Range("name") On Error GoTo 0 myFolder = myParentFolder & mycsj & "\Pay Reports" & "\" & Range("name") myFileName = Range("file") myFileName = myFolder & "\" & myFileName & ".xls" ActiveWorkbook.SaveAs Filename:= _ myFileName, FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False MsgBox "File Saved to " & myFileName Else myParentFolder = "C:\" On Error Resume Next MkDir myParentFolder & mycsj MkDir myParentFolder & mycsj & "\Pay Reports" MkDir myParentFolder & mycsj & "\Pay Reports" & "\" & Range("name") On Error GoTo 0 myFolder = myParentFolder & mycsj & "\Pay Reports" & "\" & Range("name") myFileName = Range("file") myFileName = myFolder & "\" & myFileName & ".xls" ActiveWorkbook.SaveAs Filename:= _ myFileName, FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False MsgBox "File Saved to " & myFileName End If Application.DisplayAlerts = True End Sub This works fine, however when I need to create a new workbook to continue where the old one left off it simply overwrites the old one. I need it to have the same name only add a "2" to the end of the old file name, and if another is created then add a "3" and so on, any help is greatly appreciated. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Rename rather than overwrite existing file
maybe like this:
i = 2 myfilename = Range("file") While Dir(myfolder & "\" & myfilename & i & ".xls") < "" i = i + 1 Wend myfilename = myfolder & "\" & myfilename & i & ".xls" -- Hope that helps. Vergel Adriano "jnf40" wrote: Hello all, I have a workbook that saves a file with the following code: Function DoesPathExist(myPath As String) As Boolean Dim TestStr As String If Right(myPath, 1) < "\" Then myPath = myPath & "\" End If TestStr = "" On Error Resume Next TestStr = Dir(myPath & "nul") On Error GoTo 0 DoesPathExist = CBool(TestStr < "") End Function Dim myParentFolder As String Dim myFolder As String myParentFolder = "U:\" Application.DisplayAlerts = False If DoesPathExist(myParentFolder) Then On Error Resume Next MkDir myParentFolder & mycsj MkDir myParentFolder & mycsj & "\Pay Reports" MkDir myParentFolder & mycsj & "\Pay Reports" & "\" & Range("name") On Error GoTo 0 myFolder = myParentFolder & mycsj & "\Pay Reports" & "\" & Range("name") myFileName = Range("file") myFileName = myFolder & "\" & myFileName & ".xls" ActiveWorkbook.SaveAs Filename:= _ myFileName, FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False MsgBox "File Saved to " & myFileName Else myParentFolder = "C:\" On Error Resume Next MkDir myParentFolder & mycsj MkDir myParentFolder & mycsj & "\Pay Reports" MkDir myParentFolder & mycsj & "\Pay Reports" & "\" & Range("name") On Error GoTo 0 myFolder = myParentFolder & mycsj & "\Pay Reports" & "\" & Range("name") myFileName = Range("file") myFileName = myFolder & "\" & myFileName & ".xls" ActiveWorkbook.SaveAs Filename:= _ myFileName, FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False MsgBox "File Saved to " & myFileName End If Application.DisplayAlerts = True End Sub This works fine, however when I need to create a new workbook to continue where the old one left off it simply overwrites the old one. I need it to have the same name only add a "2" to the end of the old file name, and if another is created then add a "3" and so on, any help is greatly appreciated. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Rename rather than overwrite existing file
Thanks for the response, but where would I insert your code into mine?
"Vergel Adriano" wrote: maybe like this: i = 2 myfilename = Range("file") While Dir(myfolder & "\" & myfilename & i & ".xls") < "" i = i + 1 Wend myfilename = myfolder & "\" & myfilename & i & ".xls" -- Hope that helps. Vergel Adriano "jnf40" wrote: Hello all, I have a workbook that saves a file with the following code: Function DoesPathExist(myPath As String) As Boolean Dim TestStr As String If Right(myPath, 1) < "\" Then myPath = myPath & "\" End If TestStr = "" On Error Resume Next TestStr = Dir(myPath & "nul") On Error GoTo 0 DoesPathExist = CBool(TestStr < "") End Function Dim myParentFolder As String Dim myFolder As String myParentFolder = "U:\" Application.DisplayAlerts = False If DoesPathExist(myParentFolder) Then On Error Resume Next MkDir myParentFolder & mycsj MkDir myParentFolder & mycsj & "\Pay Reports" MkDir myParentFolder & mycsj & "\Pay Reports" & "\" & Range("name") On Error GoTo 0 myFolder = myParentFolder & mycsj & "\Pay Reports" & "\" & Range("name") myFileName = Range("file") myFileName = myFolder & "\" & myFileName & ".xls" ActiveWorkbook.SaveAs Filename:= _ myFileName, FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False MsgBox "File Saved to " & myFileName Else myParentFolder = "C:\" On Error Resume Next MkDir myParentFolder & mycsj MkDir myParentFolder & mycsj & "\Pay Reports" MkDir myParentFolder & mycsj & "\Pay Reports" & "\" & Range("name") On Error GoTo 0 myFolder = myParentFolder & mycsj & "\Pay Reports" & "\" & Range("name") myFileName = Range("file") myFileName = myFolder & "\" & myFileName & ".xls" ActiveWorkbook.SaveAs Filename:= _ myFileName, FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False MsgBox "File Saved to " & myFileName End If Application.DisplayAlerts = True End Sub This works fine, however when I need to create a new workbook to continue where the old one left off it simply overwrites the old one. I need it to have the same name only add a "2" to the end of the old file name, and if another is created then add a "3" and so on, any help is greatly appreciated. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Rename rather than overwrite existing file
replace this part of your code:
myFileName = Range("file") myFileName = myFolder & "\" & myFileName & ".xls" with the one I gave. Also, don't forget to declare i as an integer Dim i as Integer -- Hope that helps. Vergel Adriano "jnf40" wrote: Thanks for the response, but where would I insert your code into mine? "Vergel Adriano" wrote: maybe like this: i = 2 myfilename = Range("file") While Dir(myfolder & "\" & myfilename & i & ".xls") < "" i = i + 1 Wend myfilename = myfolder & "\" & myfilename & i & ".xls" -- Hope that helps. Vergel Adriano "jnf40" wrote: Hello all, I have a workbook that saves a file with the following code: Function DoesPathExist(myPath As String) As Boolean Dim TestStr As String If Right(myPath, 1) < "\" Then myPath = myPath & "\" End If TestStr = "" On Error Resume Next TestStr = Dir(myPath & "nul") On Error GoTo 0 DoesPathExist = CBool(TestStr < "") End Function Dim myParentFolder As String Dim myFolder As String myParentFolder = "U:\" Application.DisplayAlerts = False If DoesPathExist(myParentFolder) Then On Error Resume Next MkDir myParentFolder & mycsj MkDir myParentFolder & mycsj & "\Pay Reports" MkDir myParentFolder & mycsj & "\Pay Reports" & "\" & Range("name") On Error GoTo 0 myFolder = myParentFolder & mycsj & "\Pay Reports" & "\" & Range("name") myFileName = Range("file") myFileName = myFolder & "\" & myFileName & ".xls" ActiveWorkbook.SaveAs Filename:= _ myFileName, FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False MsgBox "File Saved to " & myFileName Else myParentFolder = "C:\" On Error Resume Next MkDir myParentFolder & mycsj MkDir myParentFolder & mycsj & "\Pay Reports" MkDir myParentFolder & mycsj & "\Pay Reports" & "\" & Range("name") On Error GoTo 0 myFolder = myParentFolder & mycsj & "\Pay Reports" & "\" & Range("name") myFileName = Range("file") myFileName = myFolder & "\" & myFileName & ".xls" ActiveWorkbook.SaveAs Filename:= _ myFileName, FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False MsgBox "File Saved to " & myFileName End If Application.DisplayAlerts = True End Sub This works fine, however when I need to create a new workbook to continue where the old one left off it simply overwrites the old one. I need it to have the same name only add a "2" to the end of the old file name, and if another is created then add a "3" and so on, any help is greatly appreciated. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Rename rather than overwrite existing file
Thanks for your help, right after I sent the last reply I figured out where
to place it and tested your code, it worked perfectly, again thanks. "Vergel Adriano" wrote: replace this part of your code: myFileName = Range("file") myFileName = myFolder & "\" & myFileName & ".xls" with the one I gave. Also, don't forget to declare i as an integer Dim i as Integer -- Hope that helps. Vergel Adriano "jnf40" wrote: Thanks for the response, but where would I insert your code into mine? "Vergel Adriano" wrote: maybe like this: i = 2 myfilename = Range("file") While Dir(myfolder & "\" & myfilename & i & ".xls") < "" i = i + 1 Wend myfilename = myfolder & "\" & myfilename & i & ".xls" -- Hope that helps. Vergel Adriano "jnf40" wrote: Hello all, I have a workbook that saves a file with the following code: Function DoesPathExist(myPath As String) As Boolean Dim TestStr As String If Right(myPath, 1) < "\" Then myPath = myPath & "\" End If TestStr = "" On Error Resume Next TestStr = Dir(myPath & "nul") On Error GoTo 0 DoesPathExist = CBool(TestStr < "") End Function Dim myParentFolder As String Dim myFolder As String myParentFolder = "U:\" Application.DisplayAlerts = False If DoesPathExist(myParentFolder) Then On Error Resume Next MkDir myParentFolder & mycsj MkDir myParentFolder & mycsj & "\Pay Reports" MkDir myParentFolder & mycsj & "\Pay Reports" & "\" & Range("name") On Error GoTo 0 myFolder = myParentFolder & mycsj & "\Pay Reports" & "\" & Range("name") myFileName = Range("file") myFileName = myFolder & "\" & myFileName & ".xls" ActiveWorkbook.SaveAs Filename:= _ myFileName, FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False MsgBox "File Saved to " & myFileName Else myParentFolder = "C:\" On Error Resume Next MkDir myParentFolder & mycsj MkDir myParentFolder & mycsj & "\Pay Reports" MkDir myParentFolder & mycsj & "\Pay Reports" & "\" & Range("name") On Error GoTo 0 myFolder = myParentFolder & mycsj & "\Pay Reports" & "\" & Range("name") myFileName = Range("file") myFileName = myFolder & "\" & myFileName & ".xls" ActiveWorkbook.SaveAs Filename:= _ myFileName, FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False MsgBox "File Saved to " & myFileName End If Application.DisplayAlerts = True End Sub This works fine, however when I need to create a new workbook to continue where the old one left off it simply overwrites the old one. I need it to have the same name only add a "2" to the end of the old file name, and if another is created then add a "3" and so on, any help is greatly appreciated. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Create a new file, name it and overwrite an existing name without confimation | Excel Programming | |||
| accidentally click YES to overwrite an existing file | Excel Discussion (Misc queries) | |||
| Overwrite existing file without prompt | Excel Programming | |||
| Overwrite existing file without prompt | Excel Programming | |||
| Using SaveAs Statement to overwrite existing File | Excel Programming | |||
Hybrid Mode
