Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Capture Error 1004
Hi,
How do I capture a Run-time error '1004': Copy Method of Worksheet Class failed or the like so I retain control of the code? -- Thanks for your help. Karen53 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Capture Error 1004
On error resume next 'you're expecting that the next line MIGHT cause an error
'your code if err.number < 0 then msgbox err.description & vblf & err.number err.clear else 'no error occurred end if on error goto 0 'let excel handle the errors Karen53 wrote: Hi, How do I capture a Run-time error '1004': Copy Method of Worksheet Class failed or the like so I retain control of the code? -- Thanks for your help. Karen53 -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Capture Error 1004
You could put an error trap in your code or you can totally ignore your
errors like this: On Error Resume Next If you want an example of an error trap, take a look at the following example: Sub test() On Error GoTo test_Error ' your code goes here On Error GoTo 0 Exit Sub test_Error: MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure test of Module Module2" End Sub Mark |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Capture Error 1004
Hi Dave,
This is what I'm trying to do using part of what you posted. Will this work? I'm trying to capture when the worksheet copy errors out and prompt the user what to do. Sub GetTenantInfo() Dim wbkCopyFrom As Workbook Dim wbkCopyTo As Workbook Dim FromwbkPath Dim ShName As String Dim CopyWSError As Boolean CopyWSError = False Set wbkCopyTo = ThisWorkbook FromwbkPath = Application.GetOpenFilename On Error Resume Next Set wbkCopyFrom = Workbooks(FromwbkPath) If wbkCopyFrom Is Nothing Then Set wbkCopyFrom = Workbooks.Open(FromwbkPath) On Error GoTo 0 If wbkCopyFrom Is Nothing Then MsgBox "Cannot find originating file" Else Application.StatusBar = "Processing. Please Wait." Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlCalculationManual My Code 'copy the sheet Call AddSheets.UnProtectWkbook CAMMaster.Copy After:=Sheets(ShNumber) If Err.Number < 0 Then Err.Clear CopyWSError = True GoTo Finished Else 'no error occurred End If 'name the sheet ActiveSheet.Name = (ShName) Call ProtectSht(ShName) Call AddSheets.ProtectWkbook More Code Application.Calculation = xlCalculationAutomatic Application.EnableEvents = True Application.ScreenUpdating = True Application.StatusBar = False wbkCopyTo.Save End If End If Finished: If CopyWSError = True Then wbkCopyTo.Save MsgBox "Maximum tenant sheets copied." & vbLf _ & "Close workbook, reopen and restart 'Recreate Tenant Sheets'" End If End Sub -- Thanks for your help. Karen53 "Dave Peterson" wrote: On error resume next 'you're expecting that the next line MIGHT cause an error 'your code if err.number < 0 then msgbox err.description & vblf & err.number err.clear else 'no error occurred end if on error goto 0 'let excel handle the errors Karen53 wrote: Hi, How do I capture a Run-time error '1004': Copy Method of Worksheet Class failed or the like so I retain control of the code? -- Thanks for your help. Karen53 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Capture Error 1004
Two quick comments...
First, you should put these lines... Application.Calculation = xlCalculationAutomatic Application.EnableEvents = True Application.ScreenUpdating = True Application.StatusBar = False in the Finished error handler, otherwise they won't get turned back in if an error occurs. Second, you can't check the Err.Number after you execute On Error GoTo 0 as it will be 0. Rick "Karen53" wrote in message ... Hi Dave, This is what I'm trying to do using part of what you posted. Will this work? I'm trying to capture when the worksheet copy errors out and prompt the user what to do. Sub GetTenantInfo() Dim wbkCopyFrom As Workbook Dim wbkCopyTo As Workbook Dim FromwbkPath Dim ShName As String Dim CopyWSError As Boolean CopyWSError = False Set wbkCopyTo = ThisWorkbook FromwbkPath = Application.GetOpenFilename On Error Resume Next Set wbkCopyFrom = Workbooks(FromwbkPath) If wbkCopyFrom Is Nothing Then Set wbkCopyFrom = Workbooks.Open(FromwbkPath) On Error GoTo 0 If wbkCopyFrom Is Nothing Then MsgBox "Cannot find originating file" Else Application.StatusBar = "Processing. Please Wait." Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlCalculationManual My Code 'copy the sheet Call AddSheets.UnProtectWkbook CAMMaster.Copy After:=Sheets(ShNumber) If Err.Number < 0 Then Err.Clear CopyWSError = True GoTo Finished Else 'no error occurred End If 'name the sheet ActiveSheet.Name = (ShName) Call ProtectSht(ShName) Call AddSheets.ProtectWkbook More Code Application.Calculation = xlCalculationAutomatic Application.EnableEvents = True Application.ScreenUpdating = True Application.StatusBar = False wbkCopyTo.Save End If End If Finished: If CopyWSError = True Then wbkCopyTo.Save MsgBox "Maximum tenant sheets copied." & vbLf _ & "Close workbook, reopen and restart 'Recreate Tenant Sheets'" End If End Sub -- Thanks for your help. Karen53 "Dave Peterson" wrote: On error resume next 'you're expecting that the next line MIGHT cause an error 'your code if err.number < 0 then msgbox err.description & vblf & err.number err.clear else 'no error occurred end if on error goto 0 'let excel handle the errors Karen53 wrote: Hi, How do I capture a Run-time error '1004': Copy Method of Worksheet Class failed or the like so I retain control of the code? -- Thanks for your help. Karen53 -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Capture Error 1004
There's a lot of "more code", but this untested, uncompiled code may be closer:
Option Explicit Sub GetTenantInfo() Dim wbkCopyFrom As Workbook Dim wbkCopyFromName As String Dim wbkCopyTo As Workbook Dim FromwbkPath As Variant 'I like to see "As Variant" Dim ShName As String Dim CopyWSError As Boolean CopyWSError = False Set wbkCopyTo = ThisWorkbook FromwbkPath = Application.GetOpenFilename(Filefilter:="Excel Files, *.xls") If FromwbkPath = False Then Exit Sub 'user hit cancel End If 'just the filename wbkCopyFromName = Mid(FromwbkPath, InStrRev(FromwbkPath, "\") + 1) On Error Resume Next 'you don't use the whole path--just the filename here Set wbkCopyFrom = Workbooks(wbkCopyFromName) On Error GoTo 0 If wbkCopyFrom Is Nothing Then On Error Resume Next Set wbkCopyFrom = Workbooks.Open(Filename:=FromwbkPath) On Error GoTo 0 If wbkCopyFrom Is Nothing Then MsgBox "Cannot Open originating file--in use or wrong password?" Exit Sub Else Application.StatusBar = "Processing. Please Wait." Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlCalculationManual 'My Code 'copy the sheet Call AddSheets.UnProtectWkbook On Error Resume Next CAMMaster.Copy After:=Sheets(ShNumber) If Err.Number < 0 Then Err.Clear CopyWSError = True GoTo Finished Else 'no error occurred End If On Error goto 0 'name the sheet ActiveSheet.Name = ShName Call ProtectSht(ShName) Call AddSheets.ProtectWkbook More Code wbkCopyTo.Save End If End If Finished: If CopyWSError = True Then wbkCopyTo.Save 'save it even if there was an error????? MsgBox "Maximum tenant sheets copied." & vbLf _ & "Close workbook, reopen and restart 'Recreate Tenant Sheets'" End If Application.Calculation = xlCalculationAutomatic Application.EnableEvents = True Application.ScreenUpdating = True Application.StatusBar = False End Sub Karen53 wrote: Hi Dave, This is what I'm trying to do using part of what you posted. Will this work? I'm trying to capture when the worksheet copy errors out and prompt the user what to do. Sub GetTenantInfo() Dim wbkCopyFrom As Workbook Dim wbkCopyTo As Workbook Dim FromwbkPath Dim ShName As String Dim CopyWSError As Boolean CopyWSError = False Set wbkCopyTo = ThisWorkbook FromwbkPath = Application.GetOpenFilename On Error Resume Next Set wbkCopyFrom = Workbooks(FromwbkPath) If wbkCopyFrom Is Nothing Then Set wbkCopyFrom = Workbooks.Open(FromwbkPath) On Error GoTo 0 If wbkCopyFrom Is Nothing Then MsgBox "Cannot find originating file" Else Application.StatusBar = "Processing. Please Wait." Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlCalculationManual My Code 'copy the sheet Call AddSheets.UnProtectWkbook CAMMaster.Copy After:=Sheets(ShNumber) If Err.Number < 0 Then Err.Clear CopyWSError = True GoTo Finished Else 'no error occurred End If 'name the sheet ActiveSheet.Name = (ShName) Call ProtectSht(ShName) Call AddSheets.ProtectWkbook More Code Application.Calculation = xlCalculationAutomatic Application.EnableEvents = True Application.ScreenUpdating = True Application.StatusBar = False wbkCopyTo.Save End If End If Finished: If CopyWSError = True Then wbkCopyTo.Save MsgBox "Maximum tenant sheets copied." & vbLf _ & "Close workbook, reopen and restart 'Recreate Tenant Sheets'" End If End Sub -- Thanks for your help. Karen53 "Dave Peterson" wrote: On error resume next 'you're expecting that the next line MIGHT cause an error 'your code if err.number < 0 then msgbox err.description & vblf & err.number err.clear else 'no error occurred end if on error goto 0 'let excel handle the errors Karen53 wrote: Hi, How do I capture a Run-time error '1004': Copy Method of Worksheet Class failed or the like so I retain control of the code? -- Thanks for your help. Karen53 -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Capture Error 1004
Hi,
If I move it to a called procedure, would it work? 'copy the sheet Call wsCopy(ShNumber, CopyWSError) If CopyWSError = True Then wkbcopyfrom.Close SaveAs:=False Call AddSheets.UnProtectSht(Replace(MainPagepg.Name, "'", "''")) 'delete the errored row MainPagepg.Rows(iCtr).Delete (xlUp) Call AddSheets.ProtectSht(Replace(MainPagepg.Name, "'", "''")) 'reset Tenant's range MainPagepg.Range("C" & 14 & ":F" & iCtr - 1).Name = "Tenants" Application.Calculation = xlCalculationAutomatic Application.EnableEvents = True Application.ScreenUpdating = True Application.StatusBar = False wbkCopyTo.Save MsgBox "Maximum sheets copied." & vbLf _ & "Close workbook, reopen and" & vbLf & _ restart 'Recreate Tenant Sheets'" GoTo Finished Else 'no error occurred End If Finished: End Sub Sub wsCopy() 'copy the sheet Call AddSheets.UnProtectWkbook On Error Resume Next CAMMaster.Copy After:=Sheets(ShNumber) Call wsCopy(ShNumber, CopyWSError) If Err.Number < 0 Then Err.Clear CopyWSError = True Else 'no error occurred End If End Sub -- Thanks for your help. Karen53 "Rick Rothstein (MVP - VB)" wrote: Two quick comments... First, you should put these lines... Application.Calculation = xlCalculationAutomatic Application.EnableEvents = True Application.ScreenUpdating = True Application.StatusBar = False in the Finished error handler, otherwise they won't get turned back in if an error occurs. Second, you can't check the Err.Number after you execute On Error GoTo 0 as it will be 0. Rick "Karen53" wrote in message ... Hi Dave, This is what I'm trying to do using part of what you posted. Will this work? I'm trying to capture when the worksheet copy errors out and prompt the user what to do. Sub GetTenantInfo() Dim wbkCopyFrom As Workbook Dim wbkCopyTo As Workbook Dim FromwbkPath Dim ShName As String Dim CopyWSError As Boolean CopyWSError = False Set wbkCopyTo = ThisWorkbook FromwbkPath = Application.GetOpenFilename On Error Resume Next Set wbkCopyFrom = Workbooks(FromwbkPath) If wbkCopyFrom Is Nothing Then Set wbkCopyFrom = Workbooks.Open(FromwbkPath) On Error GoTo 0 If wbkCopyFrom Is Nothing Then MsgBox "Cannot find originating file" Else Application.StatusBar = "Processing. Please Wait." Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlCalculationManual My Code 'copy the sheet Call AddSheets.UnProtectWkbook CAMMaster.Copy After:=Sheets(ShNumber) If Err.Number < 0 Then Err.Clear CopyWSError = True GoTo Finished Else 'no error occurred End If 'name the sheet ActiveSheet.Name = (ShName) Call ProtectSht(ShName) Call AddSheets.ProtectWkbook More Code Application.Calculation = xlCalculationAutomatic Application.EnableEvents = True Application.ScreenUpdating = True Application.StatusBar = False wbkCopyTo.Save End If End If Finished: If CopyWSError = True Then wbkCopyTo.Save MsgBox "Maximum tenant sheets copied." & vbLf _ & "Close workbook, reopen and restart 'Recreate Tenant Sheets'" End If End Sub -- Thanks for your help. Karen53 "Dave Peterson" wrote: On error resume next 'you're expecting that the next line MIGHT cause an error 'your code if err.number < 0 then msgbox err.description & vblf & err.number err.clear else 'no error occurred end if on error goto 0 'let excel handle the errors Karen53 wrote: Hi, How do I capture a Run-time error '1004': Copy Method of Worksheet Class failed or the like so I retain control of the code? -- Thanks for your help. Karen53 -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Capture Error 1004
Hi Dave,
Thanks! I will be able to test it tomorrow at work. wbkCopyTo.Save 'save it even if there was an error????? The only error I want to catch is the worksheet copy error. The MasterCAM sheet is copied repeatedly for the tenant's sheets within the same workbook. Excel errors out after so many copies. The workbook then has to be saved, closed and reopened to resume. So, any sheets copied before the error are good and need to be saved. I can't use an external template because the MasterCAM contains links within the workbook. Plus, the client doesn't want to use an external template. So, depending on the number of tenant's sheets, the save, close, open and rerun "Recreate Tenant's Sheets" routine may have to run several times to get through all of the tenant's sheets. I'm trying to catch it and automate it as much a possible. -- Thanks for your help. Karen53 "Dave Peterson" wrote: There's a lot of "more code", but this untested, uncompiled code may be closer: Option Explicit Sub GetTenantInfo() Dim wbkCopyFrom As Workbook Dim wbkCopyFromName As String Dim wbkCopyTo As Workbook Dim FromwbkPath As Variant 'I like to see "As Variant" Dim ShName As String Dim CopyWSError As Boolean CopyWSError = False Set wbkCopyTo = ThisWorkbook FromwbkPath = Application.GetOpenFilename(Filefilter:="Excel Files, *.xls") If FromwbkPath = False Then Exit Sub 'user hit cancel End If 'just the filename wbkCopyFromName = Mid(FromwbkPath, InStrRev(FromwbkPath, "\") + 1) On Error Resume Next 'you don't use the whole path--just the filename here Set wbkCopyFrom = Workbooks(wbkCopyFromName) On Error GoTo 0 If wbkCopyFrom Is Nothing Then On Error Resume Next Set wbkCopyFrom = Workbooks.Open(Filename:=FromwbkPath) On Error GoTo 0 If wbkCopyFrom Is Nothing Then MsgBox "Cannot Open originating file--in use or wrong password?" Exit Sub Else Application.StatusBar = "Processing. Please Wait." Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlCalculationManual 'My Code 'copy the sheet Call AddSheets.UnProtectWkbook On Error Resume Next CAMMaster.Copy After:=Sheets(ShNumber) If Err.Number < 0 Then Err.Clear CopyWSError = True GoTo Finished Else 'no error occurred End If On Error goto 0 'name the sheet ActiveSheet.Name = ShName Call ProtectSht(ShName) Call AddSheets.ProtectWkbook More Code wbkCopyTo.Save End If End If Finished: If CopyWSError = True Then wbkCopyTo.Save 'save it even if there was an error????? MsgBox "Maximum tenant sheets copied." & vbLf _ & "Close workbook, reopen and restart 'Recreate Tenant Sheets'" End If Application.Calculation = xlCalculationAutomatic Application.EnableEvents = True Application.ScreenUpdating = True Application.StatusBar = False End Sub Karen53 wrote: Hi Dave, This is what I'm trying to do using part of what you posted. Will this work? I'm trying to capture when the worksheet copy errors out and prompt the user what to do. Sub GetTenantInfo() Dim wbkCopyFrom As Workbook Dim wbkCopyTo As Workbook Dim FromwbkPath Dim ShName As String Dim CopyWSError As Boolean CopyWSError = False Set wbkCopyTo = ThisWorkbook FromwbkPath = Application.GetOpenFilename On Error Resume Next Set wbkCopyFrom = Workbooks(FromwbkPath) If wbkCopyFrom Is Nothing Then Set wbkCopyFrom = Workbooks.Open(FromwbkPath) On Error GoTo 0 If wbkCopyFrom Is Nothing Then MsgBox "Cannot find originating file" Else Application.StatusBar = "Processing. Please Wait." Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlCalculationManual My Code 'copy the sheet Call AddSheets.UnProtectWkbook CAMMaster.Copy After:=Sheets(ShNumber) If Err.Number < 0 Then Err.Clear CopyWSError = True GoTo Finished Else 'no error occurred End If 'name the sheet ActiveSheet.Name = (ShName) Call ProtectSht(ShName) Call AddSheets.ProtectWkbook More Code Application.Calculation = xlCalculationAutomatic Application.EnableEvents = True Application.ScreenUpdating = True Application.StatusBar = False wbkCopyTo.Save End If End If Finished: If CopyWSError = True Then wbkCopyTo.Save MsgBox "Maximum tenant sheets copied." & vbLf _ & "Close workbook, reopen and restart 'Recreate Tenant Sheets'" End If End Sub -- Thanks for your help. Karen53 "Dave Peterson" wrote: On error resume next 'you're expecting that the next line MIGHT cause an error 'your code if err.number < 0 then msgbox err.description & vblf & err.number err.clear else 'no error occurred end if on error goto 0 'let excel handle the errors Karen53 wrote: Hi, How do I capture a Run-time error '1004': Copy Method of Worksheet Class failed or the like so I retain control of the code? -- Thanks for your help. Karen53 -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Capture Error 1004
Hi Dave,
If On Error Resume Next is used, how long does it stay in effect? Does it stay in effect for the entire procedure and any sub procedures it calls? What is it's scope? -- Thanks for your help. Karen53 "Dave Peterson" wrote: There's a lot of "more code", but this untested, uncompiled code may be closer: Option Explicit Sub GetTenantInfo() Dim wbkCopyFrom As Workbook Dim wbkCopyFromName As String Dim wbkCopyTo As Workbook Dim FromwbkPath As Variant 'I like to see "As Variant" Dim ShName As String Dim CopyWSError As Boolean CopyWSError = False Set wbkCopyTo = ThisWorkbook FromwbkPath = Application.GetOpenFilename(Filefilter:="Excel Files, *.xls") If FromwbkPath = False Then Exit Sub 'user hit cancel End If 'just the filename wbkCopyFromName = Mid(FromwbkPath, InStrRev(FromwbkPath, "\") + 1) On Error Resume Next 'you don't use the whole path--just the filename here Set wbkCopyFrom = Workbooks(wbkCopyFromName) On Error GoTo 0 If wbkCopyFrom Is Nothing Then On Error Resume Next Set wbkCopyFrom = Workbooks.Open(Filename:=FromwbkPath) On Error GoTo 0 If wbkCopyFrom Is Nothing Then MsgBox "Cannot Open originating file--in use or wrong password?" Exit Sub Else Application.StatusBar = "Processing. Please Wait." Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlCalculationManual 'My Code 'copy the sheet Call AddSheets.UnProtectWkbook On Error Resume Next CAMMaster.Copy After:=Sheets(ShNumber) If Err.Number < 0 Then Err.Clear CopyWSError = True GoTo Finished Else 'no error occurred End If On Error goto 0 'name the sheet ActiveSheet.Name = ShName Call ProtectSht(ShName) Call AddSheets.ProtectWkbook More Code wbkCopyTo.Save End If End If Finished: If CopyWSError = True Then wbkCopyTo.Save 'save it even if there was an error????? MsgBox "Maximum tenant sheets copied." & vbLf _ & "Close workbook, reopen and restart 'Recreate Tenant Sheets'" End If Application.Calculation = xlCalculationAutomatic Application.EnableEvents = True Application.ScreenUpdating = True Application.StatusBar = False End Sub Karen53 wrote: Hi Dave, This is what I'm trying to do using part of what you posted. Will this work? I'm trying to capture when the worksheet copy errors out and prompt the user what to do. Sub GetTenantInfo() Dim wbkCopyFrom As Workbook Dim wbkCopyTo As Workbook Dim FromwbkPath Dim ShName As String Dim CopyWSError As Boolean CopyWSError = False Set wbkCopyTo = ThisWorkbook FromwbkPath = Application.GetOpenFilename On Error Resume Next Set wbkCopyFrom = Workbooks(FromwbkPath) If wbkCopyFrom Is Nothing Then Set wbkCopyFrom = Workbooks.Open(FromwbkPath) On Error GoTo 0 If wbkCopyFrom Is Nothing Then MsgBox "Cannot find originating file" Else Application.StatusBar = "Processing. Please Wait." Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlCalculationManual My Code 'copy the sheet Call AddSheets.UnProtectWkbook CAMMaster.Copy After:=Sheets(ShNumber) If Err.Number < 0 Then Err.Clear CopyWSError = True GoTo Finished Else 'no error occurred End If 'name the sheet ActiveSheet.Name = (ShName) Call ProtectSht(ShName) Call AddSheets.ProtectWkbook More Code Application.Calculation = xlCalculationAutomatic Application.EnableEvents = True Application.ScreenUpdating = True Application.StatusBar = False wbkCopyTo.Save End If End If Finished: If CopyWSError = True Then wbkCopyTo.Save MsgBox "Maximum tenant sheets copied." & vbLf _ & "Close workbook, reopen and restart 'Recreate Tenant Sheets'" End If End Sub -- Thanks for your help. Karen53 "Dave Peterson" wrote: On error resume next 'you're expecting that the next line MIGHT cause an error 'your code if err.number < 0 then msgbox err.description & vblf & err.number err.clear else 'no error occurred end if on error goto 0 'let excel handle the errors Karen53 wrote: Hi, How do I capture a Run-time error '1004': Copy Method of Worksheet Class failed or the like so I retain control of the code? -- Thanks for your help. Karen53 -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Capture Error 1004
Chip Pearson covers it in great detail:
http://cpearson.com/excel/ErrorHandling.htm Karen53 wrote: Hi Dave, If On Error Resume Next is used, how long does it stay in effect? Does it stay in effect for the entire procedure and any sub procedures it calls? What is it's scope? -- Thanks for your help. Karen53 "Dave Peterson" wrote: There's a lot of "more code", but this untested, uncompiled code may be closer: Option Explicit Sub GetTenantInfo() Dim wbkCopyFrom As Workbook Dim wbkCopyFromName As String Dim wbkCopyTo As Workbook Dim FromwbkPath As Variant 'I like to see "As Variant" Dim ShName As String Dim CopyWSError As Boolean CopyWSError = False Set wbkCopyTo = ThisWorkbook FromwbkPath = Application.GetOpenFilename(Filefilter:="Excel Files, *.xls") If FromwbkPath = False Then Exit Sub 'user hit cancel End If 'just the filename wbkCopyFromName = Mid(FromwbkPath, InStrRev(FromwbkPath, "\") + 1) On Error Resume Next 'you don't use the whole path--just the filename here Set wbkCopyFrom = Workbooks(wbkCopyFromName) On Error GoTo 0 If wbkCopyFrom Is Nothing Then On Error Resume Next Set wbkCopyFrom = Workbooks.Open(Filename:=FromwbkPath) On Error GoTo 0 If wbkCopyFrom Is Nothing Then MsgBox "Cannot Open originating file--in use or wrong password?" Exit Sub Else Application.StatusBar = "Processing. Please Wait." Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlCalculationManual 'My Code 'copy the sheet Call AddSheets.UnProtectWkbook On Error Resume Next CAMMaster.Copy After:=Sheets(ShNumber) If Err.Number < 0 Then Err.Clear CopyWSError = True GoTo Finished Else 'no error occurred End If On Error goto 0 'name the sheet ActiveSheet.Name = ShName Call ProtectSht(ShName) Call AddSheets.ProtectWkbook More Code wbkCopyTo.Save End If End If Finished: If CopyWSError = True Then wbkCopyTo.Save 'save it even if there was an error????? MsgBox "Maximum tenant sheets copied." & vbLf _ & "Close workbook, reopen and restart 'Recreate Tenant Sheets'" End If Application.Calculation = xlCalculationAutomatic Application.EnableEvents = True Application.ScreenUpdating = True Application.StatusBar = False End Sub Karen53 wrote: Hi Dave, This is what I'm trying to do using part of what you posted. Will this work? I'm trying to capture when the worksheet copy errors out and prompt the user what to do. Sub GetTenantInfo() Dim wbkCopyFrom As Workbook Dim wbkCopyTo As Workbook Dim FromwbkPath Dim ShName As String Dim CopyWSError As Boolean CopyWSError = False Set wbkCopyTo = ThisWorkbook FromwbkPath = Application.GetOpenFilename On Error Resume Next Set wbkCopyFrom = Workbooks(FromwbkPath) If wbkCopyFrom Is Nothing Then Set wbkCopyFrom = Workbooks.Open(FromwbkPath) On Error GoTo 0 If wbkCopyFrom Is Nothing Then MsgBox "Cannot find originating file" Else Application.StatusBar = "Processing. Please Wait." Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlCalculationManual My Code 'copy the sheet Call AddSheets.UnProtectWkbook CAMMaster.Copy After:=Sheets(ShNumber) If Err.Number < 0 Then Err.Clear CopyWSError = True GoTo Finished Else 'no error occurred End If 'name the sheet ActiveSheet.Name = (ShName) Call ProtectSht(ShName) Call AddSheets.ProtectWkbook More Code Application.Calculation = xlCalculationAutomatic Application.EnableEvents = True Application.ScreenUpdating = True Application.StatusBar = False wbkCopyTo.Save End If End If Finished: If CopyWSError = True Then wbkCopyTo.Save MsgBox "Maximum tenant sheets copied." & vbLf _ & "Close workbook, reopen and restart 'Recreate Tenant Sheets'" End If End Sub -- Thanks for your help. Karen53 "Dave Peterson" wrote: On error resume next 'you're expecting that the next line MIGHT cause an error 'your code if err.number < 0 then msgbox err.description & vblf & err.number err.clear else 'no error occurred end if on error goto 0 'let excel handle the errors Karen53 wrote: Hi, How do I capture a Run-time error '1004': Copy Method of Worksheet Class failed or the like so I retain control of the code? -- Thanks for your help. Karen53 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
run time error 1004 general odbc error excel 2003 vba | Excel Programming | |||
Error Capture Problem | Excel Discussion (Misc queries) | |||
Capture OLE Error | Excel Programming | |||
Run Time Error 1004: Application or Object Defined Error | Excel Programming | |||
run-time error '1004': Application-defined or object-deifined error | Excel Programming |