Automation Error

**kreitzig** · 12-24-2012, 11:09 AM

Hi all, I'm getting an automation error, but the confusing part is that my code had been working fine. I made a change and got the error. I removed the change, but the error has remained. I went back to a previous version of the file, which never had the change, and I get the error. I'm starting to wonder if I broke Excel...lol. Just to be safe, I did verify that a few other of my macros work, and they do.

Moderator's Note:
Cross Post: http://www.mrexcel.com/forum/excel-q...ion-error.html

The line of code that it is giving the error on is:

Please Login or Register  to view this content.

(on .Quit)

Here is the code for the sheet:

Please Login or Register  to view this content.

**AlphaFrog** · 12-24-2012, 11:44 AM

Try commenting out the line .ActiveWindow.Close

Please Login or Register  to view this content.

My guess is there is some sort of implicit connection to the Word app that doesn't get resolved properly when you close the document window, but I really have no idea why that works.

**kreitzig** · 12-24-2012, 11:53 AM

lol..and it was that easy. Funny, it runs faster, too. I am still wondering why it would work without issue for so long, though. And why changing back didn't seem to help, after the issue began. But I won't look a gift horse in the mouth.

Thanks again, and Happy Holidays.

**AlphaFrog** · 12-24-2012, 12:03 PM

Originally Posted by kreitzig

I am still wondering why it would work without issue for so long

You're welcome.

Did you have the Word app visible when it did work? It seems to only error when the Word app is not visible. and you close the document first. Again, don't know why.

**kreitzig** · 12-24-2012, 12:25 PM

Nope, I never made the Word doc visible. Basically, what's going on is that I have data on a spreadsheet (using it as my database) that I am populating into a Word doc. It already runs a tad slow, because I it needs to evaluate multiple rows on the spreadsheet to determine which entries to make on the Word doc. So I'm trying speed it up where I can.

**AlphaFrog** · 12-24-2012, 12:41 PM

One suggestion to make it run faster is to create your WordApp before your records loop and quit it after the loop. Add a new doc to the word app within the records loop.

**kreitzig** · 12-24-2012, 02:12 PM

As long as you're letting me pick your brain, any chance you know a good way of pulling complicated data?

The way I was thinking of it was that I would grab data from my row. As long as the first three columns match I'd work my way down, adding as needed (certain things get added, if the conditions are right). Afte getting all the data I would just skip the line when it starts the main loop again. Right now I'm getting an Object error. Hopefully this makes sense.

Please Login or Register  to view this content.

**kreitzig** · 12-24-2012, 02:24 PM

Hmmm...I declared it a range and I no longer get the error, but now it loops indefinitely.

lol...because I forgot to use "Set". Let me see if this works. But please feel free to post if you have a more elegant way of doing this.

**kreitzig** · 12-24-2012, 02:38 PM

Hmmm...when I moved WordApp outside the loop I had some issues. The first one was just that I forgot to more "Set WordApp = nothing" outside the loop as well. But I still had an issue after that. I moved it back in.

**AlphaFrog** · 12-24-2012, 03:09 PM

Instead of looping trough your records for each set of criteria, I would suggest using the AutoFilter feature and transfer the filtered results to the Word doc. This should be much faster than looping.

Excel Filters -- AutoFilter VBA

Other than that, I don't fully understand the nature of your data or the desired end result.