Automation error?

[R Kumana]

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

 

[JP]

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

 

[OssieMac]

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]

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").Value

'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

 

[R Kumana]

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]

I have to agree w/ OssieMac, a macro that self-deletes is bad news.
Your results are unpredictable.

--JP