Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
save as macro for same file name
Hello everybody
I wrote a macro that save the workbook as with the given name. I am encoutering a problem, if the file is already exists it is asking to overwrite. in this case what i want is to warn the user as the file already exists and prompt to enter new name to save as. The following is the code Sub itr12() Dim fpath As String Dim fname As String fpath = ActiveWorkbook.Path fname = Sheets("data").Range("b3") On Error Resume Next MkDir fpath & "\" & Format(Now, "dd-mmm") ActiveWorkbook.SaveAs (ActiveWorkbook.Path & "\" & Format(Now, "dd-mmm") & "\" & fname & ".XLS") End Sub The file containing macro is stored in the "fpath" directory. i am using off-203 any suggestions thanks in advance With best regards sreedhar |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
save as macro for same file name
You could try something like this. Note that this requires a reference to
"Microsoft Scripting Runtime". In your VBE, go to TOOLS - References and select the reference. I'm finding that I need to go through code that forces a save to .xls to allow for other extensions for Office 2007 because we're in the middle of a migration. Keep that in mind if/when you convert. Sub itr12() Dim fpath As String Dim fname As String Dim folderpath As String Dim filepath As String fpath = ActiveWorkbook.Path If fpath = "" Then MsgBox ("Save Workbook and try again") Exit Sub End If fname = Sheets("data").Range("b3") folderpath = fpath & "\" & Format(Now, "dd-mmm") If Not myFolderExists(folderpath) Then MkDir (folderpath) End If filepath = folderpath & "\" & Format(Now, "dd-mmm") & ".xls" If Not myFileExists(filepath) Then ActiveWorkbook.SaveAs (filepath) End If End Sub Function myFileExists(myPath As String) as string 'Requires Reference to Microsoft Scripting Runtime Dim FSO As FileSystemObject myFileExists = False Set FSO = CreateObject("Scripting.FileSystemObject") If FSO.FileExists(NewDir) Then myFileExists = True End If End Function Function myFolderExists(myFolderPath As String) as string 'Requires Reference to Microsoft Scripting Runtime Dim FSO As FileSystemObject Set FSO = CreateObject("Scripting.FileSystemObject") myFolderExists = False If FSO.FolderExists(myFolderPath) Then myFolderExists = True End If End Function -- HTH, Barb Reinhardt "yshridhar" wrote: Hello everybody I wrote a macro that save the workbook as with the given name. I am encoutering a problem, if the file is already exists it is asking to overwrite. in this case what i want is to warn the user as the file already exists and prompt to enter new name to save as. The following is the code Sub itr12() Dim fpath As String Dim fname As String fpath = ActiveWorkbook.Path fname = Sheets("data").Range("b3") On Error Resume Next MkDir fpath & "\" & Format(Now, "dd-mmm") ActiveWorkbook.SaveAs (ActiveWorkbook.Path & "\" & Format(Now, "dd-mmm") & "\" & fname & ".XLS") End Sub The file containing macro is stored in the "fpath" directory. i am using off-203 any suggestions thanks in advance With best regards sreedhar |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
save as macro for same file name
I've corrected the CODE. I must be brain dead this morning. For the
Functions, change it from Function .... ( ... as String) as String to Function .... ( ... as string) as BOOLEAN I added the last "as string" once I copied it to this thread. Sorry about any confusion. Barb Reinhardt "Barb Reinhardt" wrote: You could try something like this. Note that this requires a reference to "Microsoft Scripting Runtime". In your VBE, go to TOOLS - References and select the reference. I'm finding that I need to go through code that forces a save to .xls to allow for other extensions for Office 2007 because we're in the middle of a migration. Keep that in mind if/when you convert. Sub itr12() Dim fpath As String Dim fname As String Dim folderpath As String Dim filepath As String fpath = ActiveWorkbook.Path If fpath = "" Then MsgBox ("Save Workbook and try again") Exit Sub End If fname = Sheets("data").Range("b3") folderpath = fpath & "\" & Format(Now, "dd-mmm") If Not myFolderExists(folderpath) Then MkDir (folderpath) End If filepath = folderpath & "\" & Format(Now, "dd-mmm") & ".xls" If Not myFileExists(filepath) Then ActiveWorkbook.SaveAs (filepath) End If End Sub Function myFileExists(myPath As String) as string 'Requires Reference to Microsoft Scripting Runtime Dim FSO As FileSystemObject myFileExists = False Set FSO = CreateObject("Scripting.FileSystemObject") If FSO.FileExists(NewDir) Then myFileExists = True End If End Function Function myFolderExists(myFolderPath As String) as string 'Requires Reference to Microsoft Scripting Runtime Dim FSO As FileSystemObject Set FSO = CreateObject("Scripting.FileSystemObject") myFolderExists = False If FSO.FolderExists(myFolderPath) Then myFolderExists = True End If End Function -- HTH, Barb Reinhardt "yshridhar" wrote: Hello everybody I wrote a macro that save the workbook as with the given name. I am encoutering a problem, if the file is already exists it is asking to overwrite. in this case what i want is to warn the user as the file already exists and prompt to enter new name to save as. The following is the code Sub itr12() Dim fpath As String Dim fname As String fpath = ActiveWorkbook.Path fname = Sheets("data").Range("b3") On Error Resume Next MkDir fpath & "\" & Format(Now, "dd-mmm") ActiveWorkbook.SaveAs (ActiveWorkbook.Path & "\" & Format(Now, "dd-mmm") & "\" & fname & ".XLS") End Sub The file containing macro is stored in the "fpath" directory. i am using off-203 any suggestions thanks in advance With best regards sreedhar |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
save as macro for same file name
Barb you suggestion solved my problem. Thanks alot
warm regards sreedhar "Barb Reinhardt" wrote: You could try something like this. Note that this requires a reference to "Microsoft Scripting Runtime". In your VBE, go to TOOLS - References and select the reference. I'm finding that I need to go through code that forces a save to .xls to allow for other extensions for Office 2007 because we're in the middle of a migration. Keep that in mind if/when you convert. Sub itr12() Dim fpath As String Dim fname As String Dim folderpath As String Dim filepath As String fpath = ActiveWorkbook.Path If fpath = "" Then MsgBox ("Save Workbook and try again") Exit Sub End If fname = Sheets("data").Range("b3") folderpath = fpath & "\" & Format(Now, "dd-mmm") If Not myFolderExists(folderpath) Then MkDir (folderpath) End If filepath = folderpath & "\" & Format(Now, "dd-mmm") & ".xls" If Not myFileExists(filepath) Then ActiveWorkbook.SaveAs (filepath) End If End Sub Function myFileExists(myPath As String) as string 'Requires Reference to Microsoft Scripting Runtime Dim FSO As FileSystemObject myFileExists = False Set FSO = CreateObject("Scripting.FileSystemObject") If FSO.FileExists(NewDir) Then myFileExists = True End If End Function Function myFolderExists(myFolderPath As String) as string 'Requires Reference to Microsoft Scripting Runtime Dim FSO As FileSystemObject Set FSO = CreateObject("Scripting.FileSystemObject") myFolderExists = False If FSO.FolderExists(myFolderPath) Then myFolderExists = True End If End Function -- HTH, Barb Reinhardt "yshridhar" wrote: Hello everybody I wrote a macro that save the workbook as with the given name. I am encoutering a problem, if the file is already exists it is asking to overwrite. in this case what i want is to warn the user as the file already exists and prompt to enter new name to save as. The following is the code Sub itr12() Dim fpath As String Dim fname As String fpath = ActiveWorkbook.Path fname = Sheets("data").Range("b3") On Error Resume Next MkDir fpath & "\" & Format(Now, "dd-mmm") ActiveWorkbook.SaveAs (ActiveWorkbook.Path & "\" & Format(Now, "dd-mmm") & "\" & fname & ".XLS") End Sub The file containing macro is stored in the "fpath" directory. i am using off-203 any suggestions thanks in advance With best regards sreedhar |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2007 Macro to Open File, Delete Contents, Save New File | Excel Discussion (Misc queries) | |||
Macro to save Excel file with date and time in the file name? | Excel Programming | |||
Macro Save File (Unique file name) | Excel Worksheet Functions | |||
Macro to insert values from a file and save another sheet as a .txt file | Excel Programming | |||
Automate open file, update links, run macro, close and save file | Excel Programming |