VBA Run-time error -2147414848 - Unqualified object problem???


Board Regular
Jun 8, 2005
This one is driving me crazy, so any and all help is appreciated.

I have to create individualized reports for our reps. To do so I have created one master template that contains the data that I need. The idea is to use VBA to perform the following steps:
- Loop through a list of the reps
- Filter the data in the template for selected rep
- Recalculate
- Save off that rep-specific version
- Open the just-saved rep-specific version
- Loop through each sheet in rep report and delete rows that contain no data (all 0's)
- Save Rep version
- Close Rep version

I have code that works effectively...sometimes. As in I can run the module successfully for all reports, but on subsequent runs I receive the dreaded "Run-time Error -2147417848 (80010108): Method PasteSpecial of object Range failed" when a I attempt a PasteSpecial on the first rep-specific version I open. At various times while messing with the code I have also received the same run-time error, but with the message "Automation Error. The object invoked has disconnected from its clients"

After reading every post I could find on this, I was directed to MSKB Q319832, dealing with unqualified code and global objects in Office. I'm almost positive this is the issue I have - that my references to the rep-specific workbook object is unqualified, hence causing the run-time errors. My problem is that I don't understand how to qualify objects correctly. This is what I'm hoping someone can help me with.

The following is what I believe to be the part of my code that is relevant to this issue
    Dim wbORep As Workbook
    Dim wsORep As Worksheet
    Dim strFileName As String    ' path for rep-version report

    Set wbORep = Nothing

        'Do a SaveAs in rep folder (file will recalculate during save)
        ActiveWorkbook.SaveCopyAs strFileName
        'Open saved file
        Set wbORep = Workbooks.Open(strFileName)
        Application.ScreenUpdating = False

            'Cycle through sheets
            For Each wsORep In wbORep.Worksheets
                    If wsORep.Name = "WorkbookData" Then Exit For
                    'Freeze sheets
                    wsORep.Range("a1:aj1000").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False   <----THIS IS WHERE IT FAILS
                    Application.CutCopyMode = False
                    Application.Goto reference:=wsORep.Range("A1")
                    wsORep.Visible = sheetVisible
                    'Hide all rows of customers that are all 0's
                    rowSubtotal = wsORep.Range("F:F").Find(What:="Customer", _
                                LookIn:=xlValues, _
                                LookAt:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=False).Row + 1    'Determine header row and start loop one row lower
                    rowLastSummary = wsORep.Cells(rowSubtotal, 6).End(xlDown).Row
                    For c = rowSubtotal To rowLastSummary
                        If wsORep.Cells(c, 6).Value = "Grand Total" Then Exit For
                            Do While Application.CountIf(wsORep.Range(wsORep.Cells(c, 10), wsORep.Cells(c, 30)), "<>0") = 0
                    Next c
            Next wsORep
            wbORep.Sheets("WorkbookData").Visible = xlHidden
            wbORep.Sheets("Curr Qtr").Activate
            wbORep.Close SaveChanges:=True
Even though it errors on the PasteSpecial line, I assume that the problem starts with
        Set wbORep = Workbooks.Open(strFileName)
Does the problem start here because the workbook object isn't qualified? If so, how should I alter this code to do so? I'm out of element here.

Oh, and for the record, I started this code by using With blocks for the wbORep and wsORep instead of qualifying them all the time. I got rid of them when trying to troubleshoot and will fix later. Even with the With blocks I received the same errors. Thanks.

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Ivan, yes, I do have that statement at the end of my code, right before the sub routine ends. Sorry, should have included that.
Upvote 0
What if you did this instead of the copy and paste:

wsORep.Range("A1:AJ1000").formula = wsORep.Range("A1:AJ1000").value
Upvote 0
Sorry Ragnar, but that just results in the same type of error, except this one is "Method 'Formula' of object 'Range' failed, which to me seems to reinforce the assumption that the problem is with the unqualified workbook object.
Upvote 0
When the code breaks, what does the variable wsORep say when you place your mouse over it?

Are any of the sheets protected?

Are any of the sheets hidden/veryhidden?.

Are any of the rows filtered?

Something must be funny in one of the sheets, does the for/next loop do any of them, or does it break right away?
Upvote 0
Afraid it doesn't say anything when I place my mouse over it, but I added it in the Watch section, and when the code breaks the Name for wsORep = "Curr Qtr", which is what I want, since its the first sheet in the workbook. Also when the code breaks, wbORep.Name equals the name of the first rep-specific report, so it also appears correct.

None of the sheets are protected.

Yes, there are a number of hidden worksheets in the rep-specific workbook. Most of them contain links to Access tables.

No, the rows are not filtered, although they are a number of subtotals

When the code does not run successfully, it breaks on the first sheet of the For/Next loop.
Upvote 0
So what did you change to make it work?

I read the KB article and it looks like you need to make sure you reference everything specifically, which it looks like you already have.
Upvote 0
Yeah, basically the problem was exactly as it was described in the article. By opening new workbooks all the time, the unqualified methods were referencing the wrong application object, causing the errors when I tried to do a paste or something like that. While that's what I figured the problem was, I was unfamiliar with creating application objects, and that's what I was looking for direction on. The second MSKB article helped me figure it out. So essentially I added

Dim xlApp As Excel.Application

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True

and then qualified my workbook and worksheet objects like

Set wbORep = xlApp.Workbooks.Open(strFileName)
xlApp.Goto reference:=wsORep.Range("A1")
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back