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

[tuktuk]

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?

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

 

[MorganO]

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:

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]

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]

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]

thanks...
here's the 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]

Ok, I found what is causing the syntax error. In the code you need to change this line:

If Len(Dir(fPath&TempfName)) <= 0 Then

to this:

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:

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