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
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,217,674
Messages
6,137,924
Members
450,099
Latest member
Pushbutton

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
Back
Top