Excel Automation

[cable]

I've got a routine that successfully imports various data from an excel sheet.

But I'm having problems with the excel program being left loaded!

Currently I use this to shut it all down:

Excel.Workbooks.Close
Excel.Application.Quit
Set wsSheet = Nothing
Set wsBHSheet = Nothing
Set wbExcel = Nothing

Anything wrong with that?

Its really annoying, excel wont even open manually/normally it flashes up then disappears.

Running the code again and again, sometimes reports RPC Automation errors

 

[cavscout]

I don't know how easy it would be to integrate my approach into what you already have but I have built a DB that imports data from several existing spreadsheets using a series of macros and queries. Excel is never actually opened that I'm aware of.

I used the TransferSpreadsheet Action under Macros. Then I just washed it thru a compare query to weed out anything that is already in the DB.

If you're interested, I'll try to be more informative.

 

[Dugantrain]

Your problem is not new; I've had many problems with Excel not cleaning up after running an Automation procedure. I've found that using Late rather than Early-Binding (which is what you're using) cuts down on the problem. I've also read articles in the MS Knowledge Base on getting around this; unfortunately, I don't have an actual link on-hand.

 

[cable]

ah yes late binding...i'll look into that.

as for importing the lot, I'm not sure if that would work...its a set of about 5 worksheets laid out as a form...so a lot of unnecessary crap would be imported...but I'll keep it in mind.