Excel Automation from COM+ (VB) - "Method ~ of ~ failed"

Posted by Bert Nieves on October 12, 2001 10:15 AM

PLATFORM: Windows 2000 Advanced Server (SP2)
OFFICE: Excel 2000 (SR1)
DEV PLATFORM: Visual Basic 6.0 (SP5) - COM+ component

Greetings everyone,

I'm automating Excel from VB code. I'm just loading a .csv file and performing some formatting and saving the correpsonding .xls file (xlWorkbook Normal Save As) onto the filesystem.

Everything works fine but when my component is complete .. the Excel process stays hanging around and doesn't properly exit. Any subsequent calls to the component returns a "Method ~ of ~ failed" error message.

Why isn't the Excel process cleaning up (exiting). I supplied a small snippet to show my instantiation of Excel and my cleanup. Thanks in advance.

<begin snippet>
Dim oExcel As Excel.Application
Dim oWorkbook As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim oFormatRange As Excel.Range

Dim strOutputFilename As String
Dim strOutputFileExt As String
Dim strOutputDirectory As String
Dim strOutputPath As String

On Error Resume Next

'Invoke Excel Application
Set oExcel = GetObject(, "Excel.Application")

On Error GoTo ErrorHandler
If Err.Number &LT;&GT; 0 Or (Not oExcel Is Nothing = False) Then
Set oExcel = CreateObject("Excel.Application")
Err.Clear
End If

oExcel.Visible = False
oExcel.DisplayAlerts = False

Set oWorkbook = oExcel.Workbooks.Open(...)

.........................
........................

'Cleanup, cleanup, cleanup
If Not oFormatRange Is Nothing Then Set oFormatRange = Nothing
If Not oSheet Is Nothing Then Set oSheet = Nothing
If Not oWorkbook Is Nothing Then Call oWorkbook.Close(False)
If Not oExcel Is Nothing Then Call oExcel.Quit
If Not oWorkbook Is Nothing Then Set oWorkbook = Nothing
If Not oExcel Is Nothing Then Set oExcel = Nothing

.....
....
&LT;end snippet>

Posted by Bert Nieves on October 12, 2001 11:23 AM

I found my problem. I was using unqualified Cells references when setting a range object. After explicity qualifying the Cells with their corresponding Worksheet reference .. everything worked fine.

Hope somebody finds this useful.

&LT;UNQUALIFIED EXAMPLE - SNIPPET - Wrong>
Set oFormatRange = oSheet.Range(Cells(1, 1), .Cells(lngMaxRow, lngMaxColumn))

&LT;FULLY QUALIFIED EXAMPLE - SNIPPET - Correct>
Set oFormatRange = oSheet.Range(oSheet.Cells(1, 1), oSheet.Cells(p_lngNumOfRowsToFormat, p_lngNumOfColsToFormat))

Bert Nieves
bert89@yahoo.com Dim strOutputFilename As String Dim strOutputFileExt As String Dim strOutputDirectory As String Dim strOutputPath As String On Error Resume Next