Excel Problem

[aziz rasul]

In the OnClick event of a listbox, I use automation to open an instance of XL, do some stuff, and close XL.

The problem that I have is that when I try to open the resulting XL file it won't open. I have to go to the Task Manager to delete the EXCEL.EXE process before I can open the XL file. In addition, I can't get the code in basExcelExport to work properly i.e. the With...End With section.

Can anyone help? See attachment.

 

[Dugantrain]

This should definitely be in the FAQ as just about anyone who has attempted Excel Automation (myself included) has had this problem. I haven't looked at your example, but I can almost assuredly tell you that your problem lies within not setting UserControl=True like

Excel.Application.UserControl=True

You must do this before you set the Excel Object =Nothing or, even though you may have released the object, told the Excel application to Quit, etc., it will STILL be hanging around in memory.

 

[aziz rasul]

Many thanks for that Dugantrain.

 

[JoshuaAnthony]

Just to confirm, should Excel.Application.UserControl = True only be called when exiting on error? It's just that if I place it in the actual code that seems to CAUSE problems rather than fix any (if a successful exit and I try to enter excel file after changing UserControl only Excel tool/menu bars open - worksheet itself is nowhere to be seen). Although if something goes wrong and I set userControl in my error handler everything seems to work fine.