Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code to save gives error 1004???
I have the following code that creates a folder then saves th spreadsheet with the same name that is in A1. The problem I have is ho to handle the error message when I save the same file again and selec the NO option (Yes to replace works fine)and get the error code 100 that wants me to end or debug. Here is the code: The case statemen did not work. Can I have the saveas dialog box appear on error 1004? error is at the Activeworkbook.......... Code ------------------- Sub Macro2() On Error GoTo Errhandler ' Macro2 Macro ' Macro recorded 11/24/2003 by na ' Dim ans As String Dim ans2 As String Dim number ans = "C:\" & Range("playsave").Value ans2 = Range("playsave").Value & ".xls" MkDir ans 'makes new folder saveitnow: ChDir ans 'sets path to new folder ActiveWorkbook.SaveAs Filename:=ans & "\" & ans2, _ FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False Exit Sub Errhandler: Select Case Err.number ' Evaluate error number. Case 1004 ' "File already open" error. MsgBox "OK" Resume saveitnow Case Else GoTo saveitnow 'takes care of error from when folder already End Select exists. End Sub ------------------- ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code to save gives error 1004???
Try adding these lines to your code
Application.DisplayAlerts = False 'turns off error checking ActiveWorkbook.SaveAs Filename:=ans & "\" & ans2, _ FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False Application.DisplayAlerts = False 'turns error checking back on D.S. "Richard m" wrote in message ... I have the following code that creates a folder then saves the spreadsheet with the same name that is in A1. The problem I have is how to handle the error message when I save the same file again and select the NO option (Yes to replace works fine)and get the error code 1004 that wants me to end or debug. Here is the code: The case statement did not work. Can I have the saveas dialog box appear on error 1004? error is at the Activeworkbook.......... Code: -------------------- Sub Macro2() On Error GoTo Errhandler ' Macro2 Macro ' Macro recorded 11/24/2003 by na ' Dim ans As String Dim ans2 As String Dim number ans = "C:\" & Range("playsave").Value ans2 = Range("playsave").Value & ".xls" MkDir ans 'makes new folder saveitnow: ChDir ans 'sets path to new folder ActiveWorkbook.SaveAs Filename:=ans & "\" & ans2, _ FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False Exit Sub Errhandler: Select Case Err.number ' Evaluate error number. Case 1004 ' "File already open" error. MsgBox "OK" Resume saveitnow Case Else GoTo saveitnow 'takes care of error from when folder already End Select exists. End Sub -------------------- ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
runtime error code 1004 | Excel Worksheet Functions | |||
Error Code 1004 when exporting from VB app to Excel 2007 | Excel Discussion (Misc queries) | |||
Runtime error '1004' General ODBC error | New Users to Excel | |||
[Q] Save As throws type mismatch error in control's code? | Excel Discussion (Misc queries) | |||
Excel 2003 Macro Error - Runtime error 1004 | Excel Discussion (Misc queries) |