invalid cast exception

[Bear S.]

It's important to note that oQueryTable implements Excel.QueryTable

Imports Excel = Microsoft.Office.Interop.Excel
Public Class GetData
Dim ExcelApp As Excel.Application
Dim XlCmdType As Excel.XlCmdType
Dim XlCellInsertionMode As Excel.XlCellInsertionMode
Dim XL As New Use_Excel
Dim count As Short = 0

Public Sub Get_Data(ByVal family As String, ByVal Table As String)
count += 1
'
'To be used to get data from the data base
'
Dim Workbook As Excel.Workbook
Workbook = XL.NewWorkbook(Excel.XlWBATemplate.xlWBATWorksheet)
Dim Worksheet As Excel.Worksheet
Worksheet = XL.NewWorksheet()
Worksheet = CType(Workbook.Worksheets(count), Excel.Worksheet)
Dim DBPath As String = Environment.CurrentDirectory & "\"
Dim query_cmd As New oQueryTable
Dim destination As Excel.Range
destination = CType(Worksheet, Excel.Worksheet).Range("A1")
Dim str_DataBase As String = DBPath & family & ".mdb\"
Dim connection As Object = "<some relevant string>"
query_cmd.CommandType = XlCmdType.xlCmdTable

query_cmd = Worksheet.QueryTables.Add(connection, destination)

End Sub
End Class

My problem is specifically with the line:
query_cmd = Worksheet.QueryTables.Add(connection, destination)

it looks like i followed Cindy M.'s suggestions such that:
QueryTable=...QueryTables.Add(connection,destination) with the exception
that QueryTables only shows up by intellisense when Worksheet is used but not
when Excel.Activeworkbook is... I don't have any errors (according to
intellisense) but it throws an invalid cast exception: Unable to cast COM
object of type 'System._ComObject' to class type '_Program.oQueryTable'.
Thanks in advance for your help.

 

[Andrey Dzizenko]

And what does Worksheet.QueryTables contain? Is it Worksheet.QueryTables
object, or is it null, or something else?
Try to access it in Watch or Immediate windows.

 

[Bear S.]

Without expanding all of the tree

- Worksheet.QueryTables {System.__ComObject} Microsoft.Office.Interop.Excel.QueryTables
System.__ComObject {System.__ComObject} System.__ComObject
_Default In order to evaluate an indexed property, the property must be
qualified and the arguments must be explicitly supplied by the
user. Microsoft.Office.Interop.Excel.QueryTable
+ Application {Microsoft.Office.Interop.Excel.ApplicationClass} Microsoft.Office.Interop.Excel.Application
Count 0 Integer
Creator xlCreatorCode {1480803660} Microsoft.Office.Interop.Excel.XlCreator
+ Parent {System.__ComObject} Object

 

[Cindy M.]

Hi =?Utf-8?B?QmVhciBTLg==?=,

QueryTable=...QueryTables.Add(connection,destination) with the exception
that QueryTables only shows up by intellisense when Worksheet is used but not
when Excel.Activeworkbook is... I don't have any errors (according to
intellisense) but it throws an invalid cast exception: Unable to cast COM
object of type 'System._ComObject' to class type '_Program.oQueryTable'.
Thanks in advance for your help.

That's because an Excel workbook doesn't have a QueryTables property, nor can
you add a QueryTable to an Excel workbook. A QueryTable always "belongs" to a
worksheet.

Why do you feel you need to add a QueryTable to a workbook?

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail

 

[Bear S.]

I'm needing to add a database's table to an Excel sheet such that I can
automatically run statistical calculations on the data within the database. I
was using the Workbooks.OpenDatabase method but the problem was that it
prompted the user each time a database's table was added, and when this
process needs to be completed hundreds of times per database, prompting the
user for input each time is tedious. In the past, i had successfully imported
tables into Excel without having to prompt the user using Excel's VBA
scripting.

With Activesheet.QueryTables
.Add( _
Connection:="OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;", _
Destination:= Range("A1"))
.CommandType = xlCmdTable
.CommandText = Table
.Refresh(BackgroundQuery:= False)
End With

However, I'm attempting to make a program that offers additional
functionality beyond what is capable with Excel's VBA scripting.

 

[Cindy M.]

Hi Bear,

Hmmm, I meant my question - "Why do you feel you need to add a QueryTable to a
workbook?" - in a different way. Let me try re-phrasing...

Why do you feel you need to use the Workbook object? Why not add a Worksheet to
the workbook, then add the QueryTable to that (the way it's designed to work)?
You said adding a QueryTable to a worksheet does work for you, now...

I'm needing to add a database's table to an Excel sheet such that I can
automatically run statistical calculations on the data within the database. I
was using the Workbooks.OpenDatabase method but the problem was that it
prompted the user each time a database's table was added, and when this
process needs to be completed hundreds of times per database, prompting the
user for input each time is tedious. In the past, i had successfully imported
tables into Excel without having to prompt the user using Excel's VBA
scripting.

With Activesheet.QueryTables
.Add( _
Connection:="OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;", _
Destination:= Range("A1"))
.CommandType = xlCmdTable
.CommandText = Table
.Refresh(BackgroundQuery:= False)
End With

However, I'm attempting to make a program that offers additional
functionality beyond what is capable with Excel's VBA scripting.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail