Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automation error?
Hello,
I have an error that comes up at the end of this macro. It says "Run-time error '-2147221080 (800401a8)': Automation error" This happens after the workbook "OldScorecard" is closed (see code) and the worksheet "Updater" is deleted. I suspect it may have to do with the fact that the code is located on the Updater worksheet. This is intentional. I want to macro to delete itself (and the sheet it runs from) once it is run once. I tried it before and had no issues, so I am not completely sure that this is causing it, but it is certainly happening after the deletion. I am also getting a message saying there is a lot of information on the clipboard (presumably when I close the other workbook). How can I force "No" on this message? Thanks for helping! '================================================= ============== 'Cleanup section '--------------- 'Close old scorecard without saving changes OldScorecard.Close savechanges:=False 'if we put this sheet on the new scorecard. 'This turns off alerts then deletes the updater sheet Application.DisplayAlerts = False NewScorecard.Worksheets("Updater").Delete Application.DisplayAlerts = True Exit Sub '================================================= ============== 'Error handling and message section |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automation error?
You didn't post enough code to help us diagnose your problem.
ps- You tell Excel to chose the default option on a dialog box by using "Application.DisplayAlerts = False" as you have done elsewhere in your code. HTH, JP On Nov 5, 8:24 pm, R Kumana wrote: Hello, I have an error that comes up at the end of this macro. It says "Run-time error '-2147221080 (800401a8)': Automation error" This happens after the workbook "OldScorecard" is closed (see code) and the worksheet "Updater" is deleted. I suspect it may have to do with the fact that the code is located on the Updater worksheet. This is intentional. I want to macro to delete itself (and the sheet it runs from) once it is run once. I tried it before and had no issues, so I am not completely sure that this is causing it, but it is certainly happening after the deletion. I am also getting a message saying there is a lot of information on the clipboard (presumably when I close the other workbook). How can I force "No" on this message? Thanks for helping! '================================================= ============== 'Cleanup section '--------------- 'Close old scorecard without saving changes OldScorecard.Close savechanges:=False 'if we put this sheet on the new scorecard. 'This turns off alerts then deletes the updater sheet Application.DisplayAlerts = False NewScorecard.Worksheets("Updater").Delete Application.DisplayAlerts = True Exit Sub '================================================= ============== 'Error handling and message section |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automation error?
I would suggest that you have your macro in a completely separate workbook
and activate and process the other workbooks from it. You can then save and close the other workbooks and then close the one with the macro as the last operation. That way there is no need to delete the workbook with the macro. Regards, OssieMac "R Kumana" wrote: Hello, I have an error that comes up at the end of this macro. It says "Run-time error '-2147221080 (800401a8)': Automation error" This happens after the workbook "OldScorecard" is closed (see code) and the worksheet "Updater" is deleted. I suspect it may have to do with the fact that the code is located on the Updater worksheet. This is intentional. I want to macro to delete itself (and the sheet it runs from) once it is run once. I tried it before and had no issues, so I am not completely sure that this is causing it, but it is certainly happening after the deletion. I am also getting a message saying there is a lot of information on the clipboard (presumably when I close the other workbook). How can I force "No" on this message? Thanks for helping! '================================================= ============== 'Cleanup section '--------------- 'Close old scorecard without saving changes OldScorecard.Close savechanges:=False 'if we put this sheet on the new scorecard. 'This turns off alerts then deletes the updater sheet Application.DisplayAlerts = False NewScorecard.Worksheets("Updater").Delete Application.DisplayAlerts = True Exit Sub '================================================= ============== 'Error handling and message section |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automation error?
JP
Sorry, here is the entire routine: Private Sub UpdateButton_Click() '================================================= ============== 'Variables '--------- Dim OldScorecardPath As Variant Dim NewRev As Double Dim ScoreCardRev As Double Dim AcceptRev As Double Dim CountRev As Integer Dim FindScorecard As FileDialog Dim NewScorecard As Workbook Dim OldScorecard As Workbook Dim OldSettings As Worksheet Dim OldData As Worksheet Dim NewSettings As Worksheet Dim NewData As Worksheet Dim StaticRangesSett(40) As Variant Dim StaticRangesData(10) As Variant Dim RangeHolder As Variant Dim DynamicRangesSett(20) As String Dim DynamicRangesData(5) As String Dim RangeMarker As Integer Set NewScorecard = ActiveWorkbook 'Current revision. This is coded in to ensure that future versions 'have the macros rewritten to update correctly. This means that this 'can only be done by someone familiar with MS VBA NewRev = 4 'Acceptable revisions to update. If the code only works with some 'revisions, list them here. AcceptRev = 3.1 '================================================= ============== 'File selection and validation section '------------------------------------- 'Create a FileDialog object to select the target scorecard and open it 'On Error GoTo FileDialogCancel Set FindScorecard = Application.FileDialog(msoFileDialogOpen) 'Check to see if file selected is an excel file before executing FindScorecard.Show OldScorecardPath = FindScorecard.SelectedItems.Item(1) If Right(OldScorecardPath, 4) = ".xls" Then FindScorecard.Execute Else GoTo FileBad End If 'message ' MsgBox "The path is: " & OldScorecardPath 'Set the dialog object variable to Nothing. Set FindScorecard = Nothing 'Check to see if the version is identifiable Set OldScorecard = Workbooks(Workbooks.Count) 'On Error GoTo Invalidscorecard ScoreCardRev = OldScorecard.Worksheets("Revision").Range("b1").Va lue 'message 'MsgBox "The current version is " & ScoreCardRev 'If the user is trying to update a new scorecard, stop If ScoreCardRev = NewRev Then GoTo Updated If ScoreCardRev < AcceptRev Then GoTo NotUpdatable 'If the version of the old scorecard cannot be updated by this code, stop 'For CountRev = 0 To UBound(AcceptRev()) ' If ScoreCardRev = AcceptRev(CountRev) Then GoTo Updatable 'Next CountRev 'GoTo NotUpdatable 'Updatable: '================================================= ============== 'Scorecard updating section '-------------------------- 'Edit this section to change how the scorecard is updated to the new _ revision 'On Error GoTo Invalidscorecard 'Point to target worksheets Set OldSettings = OldScorecard.Worksheets("Settings") Set OldData = OldScorecard.Worksheets("Data Entry") Set NewSettings = NewScorecard.Worksheets("Settings") Set NewData = NewScorecard.Worksheets("Data Entry") 'Copy data 'Includes source and target ranges for unmodified values. Add up to 50 settings and 10 data ranges. StaticRangesSett(0) = Array("C3:C5", "C3:C5") StaticRangesSett(1) = Array("I3:I6", "I3:I6") StaticRangesSett(2) = Array("N3", "N3") StaticRangesSett(3) = Array("N5", "N5") StaticRangesSett(4) = Array("X3", "R5") StaticRangesSett(5) = Array("N5", "N5") StaticRangesSett(6) = Array("D11:J11", "D11:J11") StaticRangesSett(7) = Array("L11:M11", "L11:M11") StaticRangesSett(8) = Array("D13:M32", "D13:M32") StaticRangesSett(9) = Array("R11:S11", "U11:V11") StaticRangesSett(10) = Array("R13:S32", "U13:V32") StaticRangesSett(11) = Array("U11:V11", "X11:Y11") StaticRangesSett(12) = Array("U13:V32", "X13:Y32") StaticRangesSett(13) = Array("X11:Y11", "AA11:AB11") StaticRangesSett(14) = Array("X13:Y32", "AA13:AB32") StaticRangesSett(15) = Array("AA11:AB11", "AD11:AE11") StaticRangesSett(16) = Array("AA13:AB32", "AD13:AE32") StaticRangesSett(17) = Array("AD13:AE32", "AG13:AH32") StaticRangesSett(18) = Array("AH11", "R11") StaticRangesSett(19) = Array("AH13:AH32", "R13:R32") StaticRangesSett(20) = Array("D35:E35", "D35:E35") StaticRangesSett(21) = Array("H35:I35", "H35:I35") StaticRangesSett(22) = Array("L35:M35", "L35:M35") StaticRangesSett(23) = Array("Q35", "Q35") StaticRangesData(0) = Array("C5:BC7", "C5:BC7") StaticRangesData(1) = Array("C9:BC58", "C9:BC58") StaticRangesData(2) = Array("C60:BC109", "C60:BC109") StaticRangesData(3) = Array("C111:BC111", "C111:BC111") StaticRangesData(4) = Array("D112:BC114", "D112:BC114") For RangeMarker = 0 To 40 If Not IsEmpty(StaticRangesSett(RangeMarker)) Then RangeHolder = StaticRangesSett(RangeMarker) OldSettings.Range(RangeHolder(0)).Copy NewSettings.Range(RangeHolder(1)).PasteSpecial xlPasteValues End If Next RangeMarker For RangeMarker = 0 To 10 If Not IsEmpty(StaticRangesData(RangeMarker)) Then RangeHolder = StaticRangesData(RangeMarker) OldData.Range(RangeHolder(0)).Copy NewData.Range(RangeHolder(1)).PasteSpecial xlPasteValues End If Next RangeMarker '================================================= ============== 'Cleanup section '--------------- 'Close old scorecard without saving changes OldScorecard.Close savechanges:=False 'if we put this sheet on the new scorecard. 'This turns off alerts then deletes the updater sheet Application.DisplayAlerts = False NewScorecard.Worksheets("Updater").Delete Application.DisplayAlerts = True Exit Sub '================================================= ============== 'Error handling and message section '---------------------------------- Troubleshoot: MsgBox Trouble Exit Sub NotUpdatable: MsgBox "The version of the file selected cannot be updated this way. Please contact the scorecard manager if you feel this message is in error.", vbOKOnly, "Current Revision?" OldScorecard.Close savechanges:=False Exit Sub Updated: MsgBox "The file selected is already up to date. Please contact the scorecard manager if you feel this message is in error.", vbOKOnly, "Current Revision?" OldScorecard.Close savechanges:=False Exit Sub Invalidscorecard: MsgBox "The file selected is either not a valid scorecard, or has been altered by the user. Please contact the scorecard manager if you feel this message is in error.", vbOKOnly, "Invalid Scorecard File?" OldScorecard.Close savechanges:=False Exit Sub FileDialogCancel: MsgBox "File selection canceled by user.", vbOKOnly, "No file selected?" Exit Sub FileBad: MsgBox "The file you selected was not even an Excel file...", vbOKOnly, "Huh?" End Sub "JP" wrote: You didn't post enough code to help us diagnose your problem. ps- You tell Excel to chose the default option on a dialog box by using "Application.DisplayAlerts = False" as you have done elsewhere in your code. HTH, JP On Nov 5, 8:24 pm, R Kumana wrote: Hello, I have an error that comes up at the end of this macro. It says "Run-time error '-2147221080 (800401a8)': Automation error" This happens after the workbook "OldScorecard" is closed (see code) and the worksheet "Updater" is deleted. I suspect it may have to do with the fact that the code is located on the Updater worksheet. This is intentional. I want to macro to delete itself (and the sheet it runs from) once it is run once. I tried it before and had no issues, so I am not completely sure that this is causing it, but it is certainly happening after the deletion. I am also getting a message saying there is a lot of information on the clipboard (presumably when I close the other workbook). How can I force "No" on this message? Thanks for helping! '================================================= ============== 'Cleanup section '--------------- 'Close old scorecard without saving changes OldScorecard.Close savechanges:=False 'if we put this sheet on the new scorecard. 'This turns off alerts then deletes the updater sheet Application.DisplayAlerts = False NewScorecard.Worksheets("Updater").Delete Application.DisplayAlerts = True Exit Sub '================================================= ============== 'Error handling and message section |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automation error?
I've been trying to troubleshoot:
'This turns off alerts then deletes the updater sheet 'Close old scorecard without saving changes Application.DisplayAlerts = False OldScorecard.Close savechanges:=False NewSettings.Activate On Error GoTo CantDelete NewScorecard.Worksheets("Updater").Unprotect ("SSCC") NewScorecard.Worksheets("Updater").Delete On Error GoTo CantDisplay Application.DisplayAlerts = True On Error GoTo cantexit Exit Sub CantDelete: MsgBox "Cant delete error.", vbOKOnly, "Current Revision?" Exit Sub CantDisplay: MsgBox "Can't disply", vbOKOnly, "Current Revision?" Exit Sub cantexit: MsgBox "Cant exit" End Sub I've isolated the trouble to the following line: Application.DisplayAlerts = True what could be causing this? "JP" wrote: You didn't post enough code to help us diagnose your problem. ps- You tell Excel to chose the default option on a dialog box by using "Application.DisplayAlerts = False" as you have done elsewhere in your code. HTH, JP On Nov 5, 8:24 pm, R Kumana wrote: Hello, I have an error that comes up at the end of this macro. It says "Run-time error '-2147221080 (800401a8)': Automation error" This happens after the workbook "OldScorecard" is closed (see code) and the worksheet "Updater" is deleted. I suspect it may have to do with the fact that the code is located on the Updater worksheet. This is intentional. I want to macro to delete itself (and the sheet it runs from) once it is run once. I tried it before and had no issues, so I am not completely sure that this is causing it, but it is certainly happening after the deletion. I am also getting a message saying there is a lot of information on the clipboard (presumably when I close the other workbook). How can I force "No" on this message? Thanks for helping! '================================================= ============== 'Cleanup section '--------------- 'Close old scorecard without saving changes OldScorecard.Close savechanges:=False 'if we put this sheet on the new scorecard. 'This turns off alerts then deletes the updater sheet Application.DisplayAlerts = False NewScorecard.Worksheets("Updater").Delete Application.DisplayAlerts = True Exit Sub '================================================= ============== 'Error handling and message section |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automation error?
I have to agree w/ OssieMac, a macro that self-deletes is bad news.
Your results are unpredictable. --JP On Nov 6, 9:43 am, R Kumana wrote: JP Sorry, here is the entire routine: Private Sub UpdateButton_Click() '================================================= ============== 'Variables '--------- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run-Time Error'-2147221080(800401a8)': Automation Error | Excel Programming | |||
Compile error automation error | Excel Programming | |||
RunTime Error (Automation Error) | Excel Programming | |||
Automation Error, Unknown Error. Error value - 440 | Excel Programming | |||
Unknown where is the problem on the Runtime error - Automation error | Excel Programming |