Creating an Instance of Excel using VBS to run a VBA Workbook

**DumbLittlePunk** · 09-09-2011, 09:41 AM

Afternoon all,
I'm using the following code in a .vbs file to create an instance of Excel, then open a VBA project in said instance.

Set xl = CreateObject("Excel.application")

xl.Application.Workbooks.Open "\\KWDSTR09\GRP\Samkt\Allsamkt\Dave Ulliott\Demand Analysis\EST Demand Analysis.xls"
xl.Application.Visible = True

Set xl = Nothing

At various point in the project, I am encountering something very odd - with no warning, error handling, anything, the workbook and Excel instance simply disappear. As in, completely. They're not hidden, the processes disappear from Task Manager, but I get no crash report or anything.

This is no consistency here, I could push a button 50 times and it would do as it should 47 times... I've even encountered it a few times while stepping through my code - no debug message just a *poof* and away it goes.

I reckon it's something to do with the project being opened via this method - it only occurs when opened through the VBS script, but as you can see it's the most basic bit of code I've every seen - what could go wrong?

Any ideas??

Thanks!
Dave

**TMS** · 09-09-2011, 09:52 AM

What does the code do in the VBA Project?

In my experience, if the code works most of the time, it's likely to be data related. If you've got error trapping enabled, it might just be encountering an error and going through a "tidy" close down.

If there's an error handling module, try putting a Msgbox and a Stop in there so you know it's going through the routine. You might be able to determine what column/row/cell is being processed at the time.

Regards

**DumbLittlePunk** · 09-09-2011, 10:01 AM

Hiya, thanks for your reply
That was also my first assumption, but the closedown has occured at various points, seemingly at random, and no error event is triggered. Sometimes it happens when literally no data is being processed (initializing a form, selecting a multipage etc) - I'm sure it's to do with opening the workbook in an Excel Object triggered by the VBS script - I guess my question would be 'Has anybody had this problem before, and is there an alternative way of opening an Excel object in a slightly more stable way'?