saving And rename source file
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, |
saving And rename source file
'/===============================/
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 '/===============================/ -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "trigunner" wrote: 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, |
All times are GMT +1. The time now is 04:57 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com