Excel

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Leo - 17 Sep 2003 16:59 GMT

Hi,

I'm having trouble porting ASP-script to VB.NET with respect to Excel
COM-interop:

The following statement generates an Error:

Dim qt As New Excel.QueryTable

ERROR:
An unhandled exception of type 'System.Runtime.InteropServices.COMException'
occurred in ExcelTest.exe

Additional information: COM object with CLSID
{59191DA1-EA47-11CE-A51F-00AA0061507F} is either not valid or not
registered.

In the watch window I see a lot of these 'values': <error: an exception of
type: {System.InvalidCastException} occurred>

The following two statements seem to be OK:
Dim objExcelApp As New Excel.Application
Dim objExcelSheet As New Excel.Worksheet

I'm using Excel97, but I have the same problem with Excel 2003 (beta 2).
What could be the problem?

Leo.

Reply to this Message

Xin Huang - 18 Sep 2003 07:15 GMT

From the type library, the CLSID of QueryTable is
{59191DA1-EA47-11CE-A51F-00AA0061507F}. But this CLSID is not registered on
my machine, either. (I'm running Excel 2003.)

Maybe you can use Worksheet.QueryTables.Add method instead?

Regards,
Xin

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure? Visit http://windowsupdate.microsoft.com to get the latest
critical updates and service packs available for your computer's Windows
operating system.

Reply to this Message

Leo R - 18 Sep 2003 08:38 GMT

Hi Xin,
Thanks for your reply. In the mean time I did some further resarch. It seems
that I have to use the following piece of code:

*******************
Const strConnect = "ODBC;DSN=Qapp;SERVER=SERVER1;UID=USER1;PWD="
Dim oExcel As New Excel.Application
Dim oBook As Excel.Workbook
Dim oBooks As Excel.Workbooks
Dim oSheet As Excel.Worksheet
Dim qt As Excel.QueryTable
Try
'oExcel.Visible = False
oBooks = oExcel.Workbooks
oBook = oBooks.Open("c:\test.xls")
oSheet = oBook.ActiveSheet
qt = oSheet.QueryTables.Add(strConnect, oExcel.Cells(6, 1))
qt.Sql = sSQL
qt.FieldNames = True
qt.RefreshStyle = 1 'xlInsertDeleteCells
qt.RowNumbers = False
qt.FillAdjacentFormulas = False
qt.RefreshOnFileOpen = False
qt.HasAutoFormat = True
qt.BackgroundQuery = False
qt.TablesOnlyFromHTML = True
qt.Refresh(False)
qt.SavePassword = True
qt.SaveData = True
oSheet.SaveAs("c:\test_" & Format(Now, "HH_mm_ss") & ".xls")
oBook.Close(True)
System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook)
oBook = Nothing
System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks)
oBooks = Nothing
oExcel.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel)
oExcel = Nothing
Catch ex As Exception
Finally
GC.Collect()
End Try
*******************

This code works fine.
The problem was that Excel2003 (11.0 library) and Excel97 (8.0 library) both
didn't support 'oBooks.Open("c:\test.xls")' and Excel2002 (10.0 library)
does! So now I use Excel 2002 and everything is OK.
Leo.

> From the type library, the CLSID of QueryTable is
> {59191DA1-EA47-11CE-A51F-00AA0061507F}. But this CLSID is not registered on
[quoted text clipped - 10 lines]
> critical updates and service packs available for your computer's Windows
> operating system.

Reply to this Message

Excel

Free Magazines