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

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:

Please Login or Register  to view this content.

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.mrexcel.com/forum/excel-q...ml#post3848518

Originally Posted by TMS

Originally Posted by TMS

When you open a workbook, it will automatically be the Active Workbook, you don't need to activate it.

Fixed.

Originally Posted by TMS

You don't need Application.Run to execute a macro in the current workbook

Yeah, I actually migrated the code from another Workbook, but I forgot to change that. Thanks! Fixed.

Originally Posted by TMS

Cells.Select will select the whole worksheet (1 million+ rows)

Does it matter?

Originally Posted by TMS

You don't need to select a cell/range to operate on it

I'm guessing this is for productivity purpose? Fixed.

Thanks for all the tips, I really like those as it permits me to learn more on VBA and how it operates. I appreciate it.

It didn't solve my problem about the run-time error though..

Originally Posted by TMS

Maybe, maybe not. Selecting and operating on 1,000,000+ rows x 16,000+ columns must have a memory overhead. Personally, I think it would make sense just to manipulate only the cells that you have data in.

Yes, selecting cells slows the whole process down.

Can't really do much about that. I don't have anything to test.


Regards, TMS

Originally Posted by 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.

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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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.

Originally Posted by TMS

I think the named ranges belong to the workbook, not the application.

There are some hidden ranges but that shouldn't cause a problem.


Regards, TMS

Hello and thanks for your answer. Do you have an Idea what could cause the problem then?

Originally Posted by TMS

Both these routines work for me, regardless of whether or not there are any Named Ranges present in the workbook.

Regards, TMS

They don't here :P I still get the same error...

Originally Posted by Norie

http://www.mrexcel.com/forum/excel-q...n-error-2.html

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

Originally Posted by YounesB3

Cross-post: http://www.mrexcel.com/forum/excel-q...ml#post3848518

...

(10 characters)