saving And rename source file

  • Thread starter Thread starter trigunner
  • Start date Start date
T

trigunner

Hello
I have a module that opens a master file that I get data from. is there a
way to save the file in a different location with the date at the end of the
file?
the source is
C:\Documents and Settings\My Documents\Reports\Master 2010.XLS
I would like it to be
C:\Documents and Settings\My Documents\Saved Reports\OCPOC - Master 2010 -
Date.XLS
Also if there is a file with the same date add a number of file

Thanks,
 
'/===============================/
Sub SaveMyFile()
Dim blnFileExists As Boolean
Dim i As Integer
Dim strSave2File As String

'set up default value for whether file exists
blnFileExists = False

'create the name/location of file to be saved
strSave2File = _
"C:\Documents and Settings\My Documents\" & _
"Saved Reports\OCPOC - Master 2010 -" & _
Format(Date, "yyyy_mm_dd") & _
".xls"

'check whether file exists.
' If no - continue
' If yes - add # to filename and check again
Do
'call private UDF below to see if file already exists
blnFileExists = FileExists(strSave2File)
If blnFileExists = True Then
i = i + 1
strSave2File = _
Left(strSave2File, Len(strSave2File) - 4) & i & ".xls"
Else
'if file does NOT exist, continue on
Exit Do
End If
Loop

'save the file
ActiveWorkbook.SaveAs Filename:=strSave2File, _
FileFormat:=xlNormal, Password:= _
"", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False

End Sub
'/===============================/
Private Function FileExists(strFileName As String) _
As Boolean
FileExists = False
If Dir(strFileName) <> "" Then
FileExists = True
End If
End Function
'/===============================/
 
Back
Top