vba - random fail on CreateObject

[ClimoC]

Hello,

does anyone know why 75% of the time:

Dim xlapp as object

retrypoint:
On error resume next
err.clear
set xlapp = createobject("Excel.Application")

if xlapp = empty then goto retrypoint

err.clear
on error goto 0

works perfectly well, and my 8 modules of macros run just fine, but the other 25% of the time, I either get "ActiveX cannot create the component", or "Automation Error, the remote call procedure failed" etc etc.

Previously, before I tried to get it to double check it successfully created the object, it would proceed along its merry way until it came time to actually use an excel application property or object, like 'xlapp.book.sheets(1).cells.sort [etc etc]', and then give me an error because the xlapp was never created?

We have a series of important macros used for keeping everything up to date, and though there are smarter ways to do it, this is what the boss says to do so I have to code it.

It's doing our collective heads in when it keeps failing.

ALSO: When it does fail, I debug, and drag the 'yellow arrow' thingy back up to the 'CreateObject' line, either step through that line or press play, it all works!

I've even tried putting in 'waits' in case it was running too quickly to catch up with itself (16GB ram and a quad core I'm working on), and it doesn't seem to make a difference if I clear out all processes of Excel first or not...

I have Office 2003, and my ref libraries being used are a couple of office object ones (Project, Word, and Excel), Microsoft Scripting Runtime, and Visual Basic For Applications Extensibility 5.3

Any ideas anyone? similar problems where it only sometimes fails?

Ta
C

 

[Norie]

Bob

The code is in MS Project and the OP's main problem seems to be not being able to create an instance of Excel right at the start.

That of course would lead to problems when trying to reference workbooks, worksheets etc because there is no Excel object.

The problem is made worse because of the use of On Error...

If that wasn't there then the OP would at least get some indication that an instance of Excel wasn't created.

Seeing the rest of the code would definitely help.

 

[boblarson]

Bob

The code is in MS Project and the OP's main problem seems to be not being able to create an instance of Excel right at the start.

That isn't clear because I read it that they may use the same function multiple times in a session and it can fail in there, not necessarily when the program is opened. The symptoms, in my mind, are clearly pointing to a failure to reference an instantiated object somewhere in the code.

CLIMOC -

Does the failure EVER occur the first time you run this code when you OPEN POWERPOINT from a closed state?

 

[Norie]

Bob

I'm pretty sure there are reference problems in the code.

The OP's original question was seemingly about why CreateObject wasn't working every time.

That might be connected to reference(s) problem later in the code, if the code went past that error.

Since the code On Error... it could actually be doing that, ie skipping the problem when creating the instance of Excel and carrying on it's merry way.

There's nothing wrong with using On Error... but perhaps when developing it's best not to, especially when there seems to be unexplained errors.

 

[ClimoC]

When I get a chance I'll post the whole module with anything sensitive removed.

But yeah Nories got it, the problem is why it works sometimes and not others. And to some of the other posters regarding the hash of a script I've formed (constantly coming back to it and not sticking to good coding principles, naught naughty), thank you for your comments. As far as I'm aware, it doesn't really matter how I declare the books and sheets, whether they be worksheets of a created object (therefore, using the object library) or as objects themselves. As it all stems back down to the xlapp object, they all work. When it does work, it doesn't fail on anything. But I can run it 10 times on the exact same source data, with or without excel already open or closed, and a few times out of that 10, 'CreateObject' simply won't create the object.

And the 'On error' for CreateObject was to get it to check if an Object WAS created when I asked it to. If it didn't, it should go back and try again. So maybe I just haven't got that bit sorted. Doesn't change the fact that it sometimes works, and occasionally doesn't.

Also doesn't matter which machine we run it on. (And every machine that tries to run it has the same security settings, the same object libraries, loaded in the same order)

 

[Norie]

Climo

If the problem is happening when you try and create the instance of Excel it might not actually have anything to do with this particular code.

Try using CreateObject in some other application and see if it works.

By the way, have you considered doing things the other way round?

eg have the code in Excel an automate Project.

 

[ClimoC]

I wish I was allowed to do this a completely different way. In fact, I wish it was 2 entirely different programs altogether, and that it wasn't set up so one is run on a mac, one on a pc, over a linux based server. That's what I wish!

It's one of those situations where not only what I'm doing, but how I do it and why I do it, and not the best ways of doing it. It's simply that those making the business decisions want the world to just 'go', So when they say "I want this, this this this this and this to happen this way and give me a commandbar button for it, all in Project", I have no say in the matter, I just have to fudge my way around it.

You should see my script for inserting new ResourceNames into the Gantt view in the correct alphabetical order... (Because Project, you can't sort sections or selections, only the entire gantt sheet or resources sheet)

CreateObject has worked perfectly well in heaps of other scripts we run. I'm beginning to wonder if it's a memory thing... trying to compile too much, too many objects. I dunno. I tried to get around that by actually Killing the Excel.exe when i'm done with it (to stop it winding up with 6 invisible instances of Excel.exe running) and you can do that with:

Sub Kill_Excel()

Dim sKillExcel As String

sKillExcel = "TASKKILL /F /IM Excel.exe"
Shell sKillExcel, vbHide

End Sub

(BTW - this doesn't affect the OP problem, take it out or leave it in, doesn't make a difference. I added it to see if it would help, and while it does clean up all the left overs each time you're done with Excel, it doesn't affect whether or not 'CreateObject' works)

 

[Norie]

Do you always end up with 'ghost' instances of Excel?

That could be caused by something as simple as incorrectly referencing a range.

Also, did you really just say that Excel and Project are running on different platforms?

I don't know much about Excel/Project/Office for Macs and I don't know why but I've got a feeling that this setup could be the problem.

If not the root of the problem, a part of it.

Do the people asking you to do this know that something like this could (I would say inevitably but...) lead to some serious problems?

I don't know why but I think they might not like the idea of losing a whole lot of data when Project and Excel blow up.

Mind you I'm sure they'll be overjoyed to bring in some data recovery specialists when that happens.