run-time error '2147417848 (80010108)': Automation error

[YounesB3]

Hello, I'm having this error when running a code: run-time error '2147417848 (80010108)': Automation error

I've highlighted in red where I get the error below:

Sub Z_YoY_Staffing()


Application.DisplayAlerts = False
Dim Sht As String
Dim x As Integer
Dim rangeName As Name


    Workbooks.Open "C:\ZTemp\Master File\Year Over Year - Staffing.xlsx"


    Windows("Year Over Year - Staffing.xlsx").Activate


    Sheets("TW").Select
    ActiveWindow.SelectedSheets.Delete
    Windows("Master File.xlsm").Activate
    Sheets("TW").Select
    Application.Run "'Master File.xlsm'!TW_PERM_CurrentW"
[COLOR=#ff0000]    Sheets("TW").Copy Before:=Workbooks("Year Over Year - Staffing.xlsx").Sheets(1)[/COLOR]
    Windows("Year Over Year - Staffing.xlsx").Activate
    ActiveSheet.Shapes.Range(Array("Button 1", "Button 6", "Button 2", "Button 3", "Button 4", "Button 5")).Select
    Selection.Delete
    
    Range("B3").Select
    Cells.Find(What:="STAFFING", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(-1, 6).Select
    x = ActiveCell.Row
    Rows("4:" & x).Select
    Selection.Delete Shift:=xlUp
    
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Sht = "TW"
    On Error Resume Next
        For Each rangeName In Application.Names
             rangeName.Delete
        Next rangeName
    On Error GoTo 0
    Range("D3").Select
    
    
    Sheets("PERM").Select
    ActiveWindow.SelectedSheets.Delete
    Windows("Master File.xlsm").Activate
    Sheets("PERM").Select
    Application.Run "'Master File.xlsm'!TW_PERM_CurrentW"
    Sheets("PERM").Copy After:=Workbooks("Year Over Year - Staffing.xlsx").Sheets(1)
    Windows("Year Over Year - Staffing.xlsx").Activate
    ActiveSheet.Shapes.Range(Array("Button 1", "Button 6", "Button 2", "Button 3", "Button 4", "Button 5")).Select
    Selection.Delete
    
    Range("B3").Select
    Cells.Find(What:="STAFFING", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(-1, 6).Select
    x = ActiveCell.Row
    Rows("4:" & x).Select
    Selection.Delete Shift:=xlUp
    
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Sht = "PERM"
    On Error Resume Next
        For Each rangeName In Application.Names
             rangeName.Delete
        Next rangeName
    On Error GoTo 0
    Range("D3").Select


    Workbooks("Year Over Year - Staffing.xlsx").Close SaveChanges:=True


Application.DisplayAlerts = True


End Sub

After some research, I've found a lot of people having the same error code with this at the end of the error: "The object invoked has disconnected from its clients."

I do not have that. I've tried some of the reg edit solutions and it doesn't work.

So what I did is check if my "Year Over Year - Staffing.xlsx" was corrupted somehow. I recreated the file with the tabs "TW" and "PERM". Here's the results of my little experiment: the macro worked the first time. I ran it again to make sure, but the second time, the macro stopped at the exact same place and with the exact same error.

What's up with that?

Thanks for your help!

Cross-post: http://www.excelforum.com/excel-pro...48-80010108-automation-error.html#post3742815

 

[YounesB3]

Not sure then. What if you qualify the worksheet to be copied?

Workbooks("Master File.xlsm").Sheets("TW").Copy Before:=Workbooks("Year Over Year - Staffing.xlsx").Sheets(1)

assuming I got the right one.

Crash stills. When I searched on the web, it talks about a forms.. I've also found this:

"If the code always appears to work the first time that it is run, and if the errors or the unexpected behaviors occur only during subsequent calls to the same code, an unqualified method call is the cause." and

"Qualifying the Code to Avoid Errors
The best guideline is to avoid using any Office object that you do not explicitly call from a parent object that you set in a specific variable. In other words, look for code that uses Office objects without qualifying which Office instance or document that it is supposed to refer to. For example, this code uses an unqualified call to display the count of open workbooks in Microsoft Excel"

I'm not sure to get what I need to change to narrow down the problem, but I'm guessing there's lines of code that corrupt the file "Year Over Year - Staffing.xlsx" somewhere somehow.

 

[Andrew Poulsom]

You need to qualify any unqualified properties/methods in your code and not rely on Activating/Selecting. You can use a With ... End With construct to avoid repeating the qualifications.

 

[YounesB3]

You need to qualify any unqualified properties/methods in your code and not rely on Activating/Selecting. You can use a With ... End With construct to avoid repeating the qualifications.

I finally had time to go back to this to try and remove the annoying 5 minutes weekly manual part that I've been doing to avoid the error.

So the macro bugs at the same place, but here's what's really been bugging it:

    On Error Resume Next[COLOR=#FF0000]        For Each rangeName In Application.Names[/COLOR]
             rangeName.Delete
        Next rangeName

Without this part, I can loop the macro fine without any issue. So I thought that the problem was the hidden names.

So I changed the code to:

    On Error Resume Next        For Each rangeName In Workbooks("Year Over Year - Pro.xlsx").Names
            If rangeName.Visible = True Then
            rangeName.Delete
            End If
        Next rangeName

But even with this, it keeps crashing at the same place, 2nd run (or more). So I did the opposite and changed the "True" in the above code to a "False" to narrow the problem to the visible Names. With "Visible = False", the macro runs fine.

The problem is then deleting the visible names which I'm baffled with to be honest. I even copy all the cells as values so why would it be an issue?

In any case, I ran some more test. Of the 5 visible range names, I deleted 1 at a time, running the macro between each deletion, to see which were ok and which weren't.

RangeName 1 = OK
RangeName 2 = OK
RangeName 3 = OK
RangeName 4 = OK
RangeName 5 = OK

All of them were OK. I switched back to "True" and ran twice just to make sure and it bugs again. Then what??? I'm stuck. Is there an invisible visible rangeName in the Workbook that I'm missing?

Note : Everytime I say I run it twice, is because I have a backup file that I can use.

 

[Andrew Poulsom]

Do you want to put your workbooks on a share like Box.com and post the URL so that we can take a look at them?

 

[YounesB3]

Do you want to put your workbooks on a share like Box.com and post the URL so that we can take a look at them?

Unfortunatly, I can't as there are some confidential data in there... Wouldn't there be any other way?

 

[Norie]

run-time error '2147417848 (80010108)': Automation error

 

[YounesB3]

Cross-post: run-time error '2147417848 (80010108)': Automation error

run-time error '2147417848 (80010108)': Automation error

Hey Norie, please look at my reply on the other thread. As you can see, I already put the cross-post reference.

Please stop hi-jacking threads...

 

[Norie]

Younes

I didn't hijack this thread.