Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I learned how to rename workbook without saving it | Excel Programming | |||
How to rename workbook that I am editing without saving? | Excel Discussion (Misc queries) | |||
How to copy a sheet and rename it with the value of two cells from the source sheet? | Excel Programming | |||
How to set SAVE AS file name to equal A1 contents when rename file | Excel Discussion (Misc queries) | |||
Saving a Workbook: Forcing User to Rename before Saving | Excel Programming |