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

 

[ClimoC]

Norie - yeah originally didn't have the on error stuff and it still fell over about 75% of the time.

The reason I use the array is that my company uses MSProject in ways it was never (as far as I can understand) meant to be used. Because of this, we've tried and tried but Projects own Export-Mapping doesn't really work, and on top of that we have about 10,000 tasks (+ the 2000 odd summary rows) - so for speed's sake, I try and load as many operations and arrays as I can into memory. From our experience it can mean the difference between something taking 15 minutes or taking 30 seconds.

And no, I don't find anything you've said to be really negative. No need to apologise! Like I said above, the whole setup is very bespoke, and I have tried hundreds of times to tell people that the way things are in place and how they want them to look/feel/work is not in line with good coding principles. Such is usually the difference between Management and Tech....

The above code though, inefficient as it may come across, does only take about 2 seconds to populate an entire 10,000 row array that's 15 or so columns wide...

Ultimately, our workplace needs Project Server, but instead I have to write all these import/export/comparison/synchronisation scripts to work with a server database they use. I have something like 110 pages of A4 worth of code for all this... getting a bit ridiculous but the only place it fails is this CreateObject crap - and I didn't think you could dump arrays to excel sheets and then run comparisons on 2 excel sheets without creating the application first....?

 

[Norie]

So have you are have you not tried my suggestion?

Which is basically - don't use CreateObject and take advantage of the Excel Object library.

Not saying it's going to magically make things work but it might be fun, and it won't cause any harm.

Normally the only reason you would have to use CreateObject and late-binding is when you are dealing with different versions of Excel.

Are you doing that?

By the way I've used Project with Acess and Excel, though it was a long time ago.

Can't remember whose idea it was to use Project but after some initial problems I managed to get things working fine, communicating between all 3 applications.

You mention the mapping - do you mean the manual mapping or doing it in code.

I was just thinking that doing it manually might have limitations that you could possibly overcome doing things in code.

As for the arrays etc, like I said there isn't plenty of scope for improving them - from what I've seen so far anyway.

Even a few loops perhaps.

For example I notice in the code you seem to be populating arrays with the same values.

Also, why are you creating 2 new workbooks?

Can't you just create 1 workbook and add worksheets as necessary.

Finally, I would recommend you don't use ActiveSheet, ActiveWorkbook or ActiveProject.

The first 2 might have something to do with the Create... problem and the last, well it kind of makes sense to me - I'm sure there's a Project equivalent of Excel VBA's ThisWorkbook.

Again sorry for the negativity.

I swear next time I'll actually post some code.

Now was that Project or Visio I downloaded the other day...

 

[Norie]

Well it turns out ActiveProject does appear to be the equivalent of ThisWorkbook.

 

[ClimoC]

Yeah - Project isn't one of the 'big 5' (Access, Outlook, Word, Excel, Powerpoint) and as such, if you compare the size of the object libraries... Project has like 1/10 of the objects, properties, actions, commands etc that Excel does.

The 2 array thing is because there are 5 different project files for 5 different staff, who all need to synchronise with each other without overwriting each others values, one array is everything (used for comparisons) and the other is just tasks that pertain to that staff member (i.e. their responsibility)

Like I said, they should be using Project Server. But I'm not in a position to make such business decisions, and the people who are would rather 'make do' with this fudge of a workaround that is my 6 x 700line modules running one after the other

On your earlier point, I've seen people using all sorts of combinations of "New Excel Application", "Create Object" etc etc. But in my experience, "CreateObject("Excel.Application")" doesn't work without the library loaded anyway - and I had never bothered to figure out which one was better. In googling for an answer to me current problem, I found a thread where someone asked the question "Which is better? Create or New?" and no-one there seemed to have an answer, just a bunch of arguments!

The multiple workbooks is because the server database import script is an OS X application, which can handle Excel files, but couldn't ever possibly navigate or specify which sheets. It can't export multiple sheets in the same book either.

The work environment here is messy... servers and databases are Linux, most users are OS X, but the staff using this stuff are running XP through a VM-Ware image file, and I'm writing all this code in Windows 7....

Fun all round.

 

[Norie]

Well I said I'd post some code:

            arrfields = Array("RecordID", "fmidx", "job", "name", "task", "Start", _
                                    "actualEnd", "userName", "Dept", "Work", "taskNotes", _
                                    "uniqueID", "Unit", "cost1", "cost2", "EXTdelay", "statusMaster", _
                                    "taskStatus", "loginID", "subsetStart", "subsetDue", "ScheduleChange")
            
            For i = LBound(arrfields) To UBound(arrfields)
                OutArray(count, i) = arrfields(i)
                OutArrayALL(count3, i) = arrfields(i)
                newtaskarray(count2, i) = arrfields(i)
            Next i

As for the CreateObject I can pretty much guarantee that you don't need to have the library referenced.

Unless there are some special circumstances, which is something I've not heard of.

Also if you do have the reference why don't you seem to be using it?

As for the object libraries, there are more objects in Excel than Project, but that's because there are more objects in Excel...

I think you might find the same for other applications, don't know which wins overall but I'd put my money on Word.

 

[xenou]

One idea:
If you have problems creating Excel, maybe put that stuff first in your code so at least you have the answer right away if its working or not - create all the workbooks you will need right away and set all the references. Drop it into a function so you can get a return value to check for the success of the operation.

Three flags:

• I don't think you should use activeworkbook or activesheet at all. Set a reference and use the reference only. Don't rely on activated status of objects if at all possible to avoid this. It's probably too late for this advice but you really don't want to to that with MS Project code either, or any significant vba project - it's a invitation for bugs.
• I am noticing that you set two references to everything - it's possible that you are closing one object and inadvertently losing the other at the same time without knowing it. Anyway, its another thing that could be causing problems.
• I would not mix early and late binding (referenced variables to the excel library and references to objects created through automation). It's possible you are using two different handles to two different objects when you do this - perhaps this is another troublespot here. Not sure.

Here's some admittedly inelegant code but you could use it to create your objects all at first and make sure there were not problems.

[COLOR="Navy"]Function[/COLOR] CreateExcelObjects(ByRef ExcelApp [COLOR="Navy"]As[/COLOR] Object, _
    NewBook [COLOR="Navy"]As[/COLOR] Object, Sheet [COLOR="Navy"]As[/COLOR] Object, _
    newtasks [COLOR="Navy"]As[/COLOR] Object, newtsheet [COLOR="Navy"]As[/COLOR] Object, _
    AllTasks, alltsheet [COLOR="Navy"]As[/COLOR] Object) [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Boolean[/COLOR]
    
    [COLOR="Navy"]Set[/COLOR] ExcelApp = CreateObject("Excel.Application")
    
    [COLOR="Navy"]Set[/COLOR] NewBook = ExcelApp.Workbooks.Add
    [COLOR="Navy"]Set[/COLOR] Sheet = NewBook.Worksheets(1)
    
    [COLOR="Navy"]Set[/COLOR] newtasks = ExcelApp.Workbooks.Add
    [COLOR="Navy"]Set[/COLOR] newtsheet = newtasks.Worksheets(1)
    
    [COLOR="Navy"]Set[/COLOR] AllTasks = ExcelApp.Workbooks.Add
    [COLOR="Navy"]Set[/COLOR] alltsheet = AllTasks.Worksheets(1)

[COLOR="Navy"]If[/COLOR] [COLOR="Navy"]Not[/COLOR] ExcelApp [COLOR="Navy"]Is[/COLOR] [COLOR="Navy"]Nothing[/COLOR] [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]If[/COLOR] [COLOR="Navy"]Not[/COLOR] NewBook [COLOR="Navy"]Is[/COLOR] [COLOR="Navy"]Nothing[/COLOR] [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] [COLOR="Navy"]Not[/COLOR] Sheet [COLOR="Navy"]Is[/COLOR] [COLOR="Navy"]Nothing[/COLOR] [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]If[/COLOR] [COLOR="Navy"]Not[/COLOR] newtasks [COLOR="Navy"]Is[/COLOR] [COLOR="Navy"]Nothing[/COLOR] [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]If[/COLOR] [COLOR="Navy"]Not[/COLOR] newtsheet [COLOR="Navy"]Is[/COLOR] [COLOR="Navy"]Nothing[/COLOR] [COLOR="Navy"]Then[/COLOR]
                    [COLOR="Navy"]If[/COLOR] [COLOR="Navy"]Not[/COLOR] AllTasks [COLOR="Navy"]Is[/COLOR] [COLOR="Navy"]Nothing[/COLOR] [COLOR="Navy"]Then[/COLOR]
                        [COLOR="Navy"]If[/COLOR] [COLOR="Navy"]Not[/COLOR] alltsheet [COLOR="Navy"]Is[/COLOR] [COLOR="Navy"]Nothing[/COLOR] [COLOR="Navy"]Then[/COLOR]
                            CreateExcelObjects = True
                        [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
                    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
                [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
            [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
        [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Function[/COLOR]

 

[Norie]

xenou

Where in the code is early and late binding getting mixed up?

Did I miss something?

 

[xenou]

I saw something in the declarations so that's what prompted the concern:

Dim newtsheet As [COLOR="RoyalBlue"]Object[/COLOR], newtasks As Object, AllTasks As Object
Dim theoutput As [COLOR="Green"]WorkBook[/COLOR], theoutput2 As WorkBook, alloutput As WorkBook

This would at least explain why the code doesn't work without a reference to the Excel library.

 

[Norie]

xenou

Doesn't compile without the reference perhaps, but I suppose that's the same thing anyway.

I see you picked up on the mutliple instances thing, I couldn't quite work it out so let it be.

It probably doesn't do any harm, but it is pretty confusing.

 

[boblarson]

Sorry, I didn't see that there were responses here.

First of all, I do believe that you can't declare the workbook and worksheet objects as such if you are using Object for the other and you expect them to work correctly.

I could be wrong so we'll leave that alone for now.

The reason that your code fails at times is that you are using code that is not attached to an instantiated object and that will cause problem after problem because Excel has to use a hidden application instance which it will open when you refer to things that are not attached to an instantiated object and you are using those things WITH an instantiated object.

So, without seeing all of the code I can't try to find where that might be but it only takes one little bit to be out of place and it can honk things up. I don't know if SHEET is a "reserved word" in Excel but it could be and therefore causing problems when you overload it to be a variable name in your procedure. (just a guess there).

This instantiated/non-instantiated reference problem is a problem with any kind of automation but I have an article I wrote which was also posted by the Microsoft Access Team to their Blog which explains it in the context of Microsoft Access and Excel. But if you are using code to refer to a specifically instantiated object in Excel from Excel you could likely still have the problem.