.NET Excel COM error

[mlinz16]

I am running the following relevant code in a Windows Service app designed to create reports by transforming XML data from a dataset into excel readable XML. Because Office XP has not been rolled out to all of our users, I have to automate Excel on the server in order to save the XML version of the file in an Excel Spreadsheet format.

It generates the following error the SECOND TIME it is run, never the first:
"The message filter indicated that the application is busy"

It errors on the line that includes "xlaApp.Ready" in this version, but generally errors on the first reference to the xlaApp Excel.Application variable following opening the workbook.

Here is the code:

GC.Collect()
xlaApp = New Excel.Application()
xlaApp.AlertBeforeOverwriting = False
xlaApp.DisplayAlerts = False
xlaApp.Visible = False
xlWB = xlaApp.Workbooks.Open(sFolder & sExcelXML)

Dim nlCount As Short = 0
While Not xlaApp.Ready
System.Threading.Thread.Sleep(500)
nlCount += 1
If nlCount >= 60 Then Throw New System.Runtime.InteropServices.COMException("EXCEL application not ready - timeout expired")
End While
xlWB.SaveAs(sFolder & sNewFile, 1)

xlWB.Close()
xlaApp.Quit()

System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWB)
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlaApp)

xlWB = Nothing
xlaApp = Nothing

GC.Collect()

 

[wakemaster]

Hi!!

Check in the TaskManager and see if the first Excel process really shuts down..

/Wakemaster

 

[mlinz16]

Yes it does. I have even tried explicitly killing all EXCEL processes. It seems like there is a potential memory leak somewhere. The reports that cause problems all have pivot tables in them. To create a pivot table through XML, you have to reference the filename the data is being pulled from. My guess now is that when Excel opens the XML file and then saves it in spreadsheet format, the COM component loses a memory reference to that file due to running in a multi-threaded apartment in the Windows Service. The above code runs fine when put in a windows form that is in a single-threaded apartment. Ive found documentation that COM components that do not handle their own synchronization cannot be run in a multi-threaded apartment (or should not run in one anyway). So I guess the new question is how to make a .NET windows service run in a single-threaded apartment - I set the attribute on the Main() sub to STA, but to no avail...