saving a file to path...if exist add (2), (3) , etc.....

tuktuk

Well-known Member
Joined
Nov 13, 2006
Messages
856
hey there. here is my save sub but it overwirtes the current file with the same name (if it exists) rather than saving another copy with the 'name' & "(2)", 'name' & "(3)", etc.

does that make sense?

Code:
Sub SaveFile()
Dim fPath As String, fName As String, SaveFile As String
'path
fPath = "\\Sr\SharedDocs\CSPSharedFILES\CPS Daily Invoice Log\"
fName = Format(Range("C2"), "(mm-dd-yyyy)") & " CPS Daily Invoice Log" & ".xls"
'full path and filename
SaveFile = fPath & fName
ActiveWorkbook.SaveCopyAs SaveFile
End Sub
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

MorganO

Active Member
Joined
Nov 21, 2006
Messages
483
If what you are trying to do is save versions of your file under different names eg:

TestFile(1).xls
TestFile(2).xls

You could add a file exists routine within your code to check for the existence of a previous version and then save your file as the next version. I've included an example below:

Code:
Sub SaveFile() 
Dim fPath As String, fName As String, SaveFile As String 
'path 
fPath = "\\Sr\SharedDocs\CSPSharedFILES\CPS Daily Invoice Log\" 
fName = Format(Range("C2"), "(mm-dd-yyyy)") & " CPS Daily Invoice Log" & ".xls" 

'Update version number
FileFound = True
Incrementor = 0
Do while FileFound = True
Incrementor = Incrementor + 1
TempfName = fname & ”(“ & Incrementor & ”)”
If Len(Dir(fpath&TempfName)) <= 0 Then 
  FileFound = False
  fName = TempFName
End If
Loop

'full path and filename 
SaveFile = fPath & fName 
ActiveWorkbook.SaveCopyAs SaveFile 
End Sub
 

tuktuk

Well-known Member
Joined
Nov 13, 2006
Messages
856
yep that is exactly what i am trying to o but i an having a Syntax error after adding your code.

i trying solving it by adding the following:

A) i defined "Dim TempfName As String, Incrementor As String".

do i need these?

B) i also mading the naming in your code to match the exactly caps as in the dim statement, namely fname to fName and fpath to fPath.

is that a correct change?
 

MorganO

Active Member
Joined
Nov 21, 2006
Messages
483
Since you did not include your new code in the post, I cannot attempt to recreate or decipher your syntax error.

A) You do not necessary need to dimension variables for the code to work, but it is good code practice to do so.

B) VBA is case insensitive so there is no need to match case (although VBA will change case to match other variables with the same name...)

If you could post your code I will look it over and give you an analysis.

Thanks.

Owen
 

tuktuk

Well-known Member
Joined
Nov 13, 2006
Messages
856
thanks...
here's the code:

Code:
Sub SaveFile()
Dim fPath As String, fName As String, SaveFile As String, TempfName As String, Incrementor As String

'path
fPath = "\\Sr\SharedDocs\CSPSharedFILES\POQuantityTrackingTool\"
fName = Format(Range("C2"), "(mm-dd-yyyy)") & " Daily Invoice Log" & ".xls"

'added repsonse from MrExcel

'Update version number
FileFound = True
Incrementor = 0
Do While FileFound = True
Incrementor = Incrementor + 1
TempfName = fName & "(" & Incrementor & ")"
If Len(Dir(fPath&TempfName)) <= 0 Then
  FileFound = False
  fName = TempfName
End If
Loop

'full path and filename
SaveFile = fPath & fName
ActiveWorkbook.SaveCopyAs SaveFile

End Sub
 

MorganO

Active Member
Joined
Nov 21, 2006
Messages
483
Ok, I found what is causing the syntax error. In the code you need to change this line:
Code:
If Len(Dir(fPath&TempfName)) <= 0 Then

to this:
Code:
If Len(Dir(fPath & TempfName)) <= 0 Then

(There needs to be a space before and after the &)

Also, I noticed one other error I had made. The code as is will append the (#) at the end of the .xls, which is not what you need. So you will also need to change some other areas of the code, so I am reposting the entire code (with all changes) below:
Code:
Sub SaveFile()
Dim fPath As String, fName As String, SaveFile As String, TempfName As String, Incrementor As String

'path
'fPath = "\\Sr\SharedDocs\CSPSharedFILES\POQuantityTrackingTool\"
fPath = "C:\Documents and Settings\Owen.Morgan\Desktop\Excel\"
fName = Format(Range("C2"), "(mm-dd-yyyy)") & " Daily Invoice Log"

'added repsonse from MrExcel

'Update version number
FileFound = True
Incrementor = 0
Do While FileFound = True
Incrementor = Incrementor + 1
TempfName = fName & "(" & Incrementor & ")" & ".xls"
If Len(Dir(fPath & TempfName)) <= 0 Then
  FileFound = False
  fName = TempfName
End If
Loop

'full path and filename
SaveFile = fPath & fName
ActiveWorkbook.SaveCopyAs SaveFile

End Sub

Take care.

Owen
 

Watch MrExcel Video

Forum statistics

Threads
1,122,371
Messages
5,595,784
Members
414,020
Latest member
Meghdad

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top