System.NullReferenceException was unhandled

[Bear S.]

My problem is that on the .add of the with querytables line, I get the
unhandled exception with the error that "Object reference not set to an
instance of an object." However, when i set QueryTables as NEW MS..... it
won't even compile. It is of note that Microsoft.Office.Interop.Excel lists
QueryTables (with an "s") as a public interface and QueryTable (without an
"s") as a public class


public class program
public shared Excel as new Microsoft.Office.Interop.Excel.Application
end class

public class AccessDB
Dim QueryTables As Microsoft.Office.Interop.Excel.QueryTables
Public Sub Get_Data(ByVal family As String, ByVal Table As String)
'
' To be used to get data from the database
'
Dim str_DataBase As String = DBPath & family & ".mdb\"
Dim connection_string As String = _
"OLEDB;" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Password=" & Chr(34) & Chr(34) & ";" & _
"User ID=Admin;" & _
"Data Source=" & Chr(34) & str_DataBase & Chr(34) & ";" & _
"Mode=Share Deny Write;" & _
"Extended Properties=" & Chr(34) & Chr(34) & ";" & _
"Jet OLEDB:System database=" & Chr(34) & Chr(34) & ";" & _
"Jet OLEDB:Regis" & _
"try Path=" & Chr(34) & Chr(34) & ";" & _
"Jet OLEDBatabase Password=" & Chr(34) & Chr(34) & ";" & _
"Jet OLEDB:Engine Type=4;" & _
"Jet OLEDBatabase Locking Mode=0;" & _
"Jet OLEDB:Global Partial Bulk Ops=2;" & _
"Jet OLEDB:Global Bulk Transactions=1;" & _
"Jet OLEDB:New Database Password=" & Chr(34) & Chr(34) & ";"
& _
"Jet OLEDB:Create System Database=False;" & _
"Jet OLEDB:Encrypt Database=False;" & _
"Jet OLEDBon't Copy Locale on Compact=False;" & _
"Jet OLEDB:Compact Without Replica Repair=False;" & _
"Jet OLEDB:SFP=False"

With QueryTables
.Add(Connection:=connection_string,
Destination:=Program.Excel.Range("A1"))
.CommandType = XlCmdType.xlCmdTable
.CommandText = Table
.Name = family
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = Program.Excel.xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = str_DataBase
.Refresh(BackgroundQuery:=False)
End With
End Sub
end class

 

[Bear S.]

Naturally, the "&" should be replaced with an ampersand and the """ replaced
with a double quote...it doesn't seem to post correctly.

 

[Cindy M.]

Hi Bear,

My problem is that on the .add of the with querytables line, I get the
unhandled exception with the error that "Object reference not set to an
instance of an object." However, when i set QueryTables as NEW MS..... it
won't even compile. It is of note that Microsoft.Office.Interop.Excel lists
QueryTables (with an "s") as a public interface and QueryTable (without an
"s") as a public class

As I mentioned in the VSTO forum, you're not using the With keyword correctly.
For an Office object, you have to use the full hierarchy. QueryTables alone
won't suffice. It doesn't matter that you can see things listed in Visual
Studio as classes or interfaces. When you use With when automating an Office
application you must specify the complete hierarchy (or instantiate an object
variable for the hierarchy and use that). The Office application has to know
WHICH QueryTables you want to "talk to". Here's an example, but since I don't
know how you're declaring your Excel application variable (or even if you are
declaring one - you must!), it may not be exact for your circumstances.

Dim wkb as Excel.Workbook = xlApp.ActiveWorkbook
With wkb.QueryTables

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.]

Thanks, I've reposted some changes here so that it makes it clear that Excel
is an instantiated object with the entire hierarchy of
"Microsoft.Office.Interop.Excel.Application" within this snippet instead of
within the public class program as shown in my previous example.
It might also be of note that QueryTables too, is an instantiated object
with the entire hierarchy of "Microsoft.Office.Interop.Excel.QueryTables"
this way this sub knows which class to talk to without having to write out
the entire hierarchy

Public Class GetData
Dim Excel As New Microsoft.Office.Interop.Excel.Application
Dim DBPath As String = "N:\Database"
Dim XlCmdType As New Microsoft.Office.Interop.Excel.XlCmdType
Dim QueryTables As Microsoft.Office.Interop.Excel.QueryTables
Public Sub Get_Data(ByVal family As String, ByVal Table As String)
'
' To be used to get data from the data base
'
Dim str_DataBase As String = DBPath & family & ".mdb\"
Dim connection_string As String = _
"OLEDB;" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Password=" & Chr(34) & Chr(34) & ";" & _
"User ID=Admin;" & _
"Data Source=" & Chr(34) & str_DataBase & Chr(34) & ";" & _
"Mode=Share Deny Write;" & _
"Extended Properties=" & Chr(34) & Chr(34) & ";" & _
"Jet OLEDB:System database=" & Chr(34) & Chr(34) & ";" & _
"Jet OLEDB:Regis" & _
"try Path=" & Chr(34) & Chr(34) & ";" & _
"Jet OLEDBatabase Password=" & Chr(34) & Chr(34) & ";" & _
"Jet OLEDB:Engine Type=4;" & _
"Jet OLEDBatabase Locking Mode=0;" & _
"Jet OLEDB:Global Partial Bulk Ops=2;" & _
"Jet OLEDB:Global Bulk Transactions=1;" & _
"Jet OLEDB:New Database Password=" & Chr(34) & Chr(34) & ";" & _
"Jet OLEDB:Create System Database=False;" & _
"Jet OLEDB:Encrypt Database=False;" & _
"Jet OLEDBon't Copy Locale on Compact=False;" & _
"Jet OLEDB:Compact Without Replica Repair=False;" & _
"Jet OLEDB:SFP=False"
Dim query_cmd As Microsoft.Office.Interop.Excel.QueryTable
With query_cmd
.CommandType = XlCmdType.xlCmdTable
.CommandText = Table
.Name = family
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = Excel.xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = str_DataBase
.Refresh(BackgroundQuery:=False)
End With
QueryTables.Add( _
Connection:=connection_string, _
Destination:=Excel.Range("A1"), _
Sql:=query_cmd)
End Sub
End Class

 

[Cindy M.]

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

Thanks, I've reposted some changes here so that it makes it clear that Excel
is an instantiated object with the entire hierarchy of
"Microsoft.Office.Interop.Excel.Application" within this snippet instead of
within the public class program as shown in my previous example.
It might also be of note that QueryTables too, is an instantiated object
with the entire hierarchy of "Microsoft.Office.Interop.Excel.QueryTables"
this way this sub knows which class to talk to without having to write out
the entire hierarchy

And if I understand correctly, you're still running into the problem? If yes,
it's because the Office object models, generally, won't let you work with
objects (class instances) without actually creating them, first. I know it
seems a bit odd if you're accustomed to working with, say, ADO objects or
Windows Forms controls, where you set all the properties first and THEN
instantiate the actual object. But it always helps to remember that the core
Office apps are twenty year old relics from the days of C (with no +)

I'd approach your code more in this manner (off the top of my head and
untested, so there might be small mistakes, but this is the basic approach):

'Declare the object variable
Dim QueryTable as Microsoft.Office.Interop.Excel.QueryTable

'Instantiate it by setting it to an existing object
'or creating the object
Set QueryTable = Excel.ActiveWorkbook.QueryTables.Add( _
Connection:=connection_string, _
Destination:=Excel.Range("A1"), _
Sql:=query_cmd)
'Set the properties
With QueryTable
.CommandType = XlCmdType.xlCmdTable
.CommandText = Table
.Name = family
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = Excel.xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = str_DataBase
.Refresh(BackgroundQuery:=False)
End With

Public Class GetData
Dim Excel As New Microsoft.Office.Interop.Excel.Application
Dim DBPath As String = "N:\Database"
Dim XlCmdType As New Microsoft.Office.Interop.Excel.XlCmdType
Dim QueryTables As Microsoft.Office.Interop.Excel.QueryTables
Public Sub Get_Data(ByVal family As String, ByVal Table As String)
'
' To be used to get data from the data base
'
Dim str_DataBase As String = DBPath & family & ".mdb\"
Dim connection_string As String = _
"OLEDB;" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Password=" & Chr(34) & Chr(34) & ";" & _
"User ID=Admin;" & _
"Data Source=" & Chr(34) & str_DataBase & Chr(34) & ";" & _
"Mode=Share Deny Write;" & _
"Extended Properties=" & Chr(34) & Chr(34) & ";" & _
"Jet OLEDB:System database=" & Chr(34) & Chr(34) & ";" & _
"Jet OLEDB:Regis" & _
"try Path=" & Chr(34) & Chr(34) & ";" & _
"Jet OLEDBatabase Password=" & Chr(34) & Chr(34) & ";" & _
"Jet OLEDB:Engine Type=4;" & _
"Jet OLEDBatabase Locking Mode=0;" & _
"Jet OLEDB:Global Partial Bulk Ops=2;" & _
"Jet OLEDB:Global Bulk Transactions=1;" & _
"Jet OLEDB:New Database Password=" & Chr(34) & Chr(34) & ";" & _
"Jet OLEDB:Create System Database=False;" & _
"Jet OLEDB:Encrypt Database=False;" & _
"Jet OLEDBon't Copy Locale on Compact=False;" & _
"Jet OLEDB:Compact Without Replica Repair=False;" & _
"Jet OLEDB:SFP=False"
Dim query_cmd As Microsoft.Office.Interop.Excel.QueryTable
With query_cmd
.CommandType = XlCmdType.xlCmdTable
.CommandText = Table
.Name = family
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = Excel.xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = str_DataBase
.Refresh(BackgroundQuery:=False)
End With
QueryTables.Add( _
Connection:=connection_string, _
Destination:=Excel.Range("A1"), _
Sql:=query_cmd)
End Sub
End Class

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.]

Thanks again, I really appreciate your patience. However, I think I may have
thrown too much on the table at once. I took out the "with..end with"
statement to clarify my problem.

I'm trying to get my program to open, then import data from a database.
However, I keep running into the error depending upon how I word it. The
problem revolves around the
"query_cmd" declaration. If I declare it as:
Dim query_cmd As New Microsoft.Office.Interop.Excel.QueryTable
I get the error: Retrieving the COM class factory for component with CLSID
{00020819-0000-0000-C000-000000000046} failed due to the following error:
80040154
However, if I declare it as:
Dim query_cmd As Microsoft.Office.Interop.Excel.QueryTable
it throws a NullReferenceException when I try to use it as:
query_cmd.CommandType = XlCmdType.xlCmdTable
n other words...it never makes it to the QueryTables.Add(...)
Thanks in advance for your help



Public Class GetData
Dim Excel As New Microsoft.Office.Interop.Excel.Application
Dim DBPath As String = "C:\Database"
Dim XlCmdType As New Microsoft.Office.Interop.Excel.XlCmdType
Dim QueryTables As Microsoft.Office.Interop.Excel.QueryTables

Public Sub Get_Data(ByVal family As String, ByVal Table As String)
'
' To be used to get data from the data base
'
Dim str_DataBase As String = DBPath & family & ".mdb\"
Dim query_cmd As New Microsoft.Office.Interop.Excel.QueryTable
Dim connection_string As String = _
"OLEDB;" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Password=" & Chr(34) & Chr(34) & ";" & _
"User ID=Admin;" & _
"Data Source=" & Chr(34) & str_DataBase & Chr(34) & ";" & _
"Mode=Share Deny Write;" & _
"Extended Properties=" & Chr(34) & Chr(34) & ";" & _
"Jet OLEDB:System database=" & Chr(34) & Chr(34) & ";" & _
"Jet OLEDB:Regis" & _
"try Path=" & Chr(34) & Chr(34) & ";" & _
"Jet OLEDBatabase Password=" & Chr(34) & Chr(34) & ";" & _
"Jet OLEDB:Engine Type=4;" & _
"Jet OLEDBatabase Locking Mode=0;" & _
"Jet OLEDB:Global Partial Bulk Ops=2;" & _
"Jet OLEDB:Global Bulk Transactions=1;" & _
"Jet OLEDB:New Database Password=" & Chr(34) & Chr(34) & ";" & _
"Jet OLEDB:Create System Database=False;" & _
"Jet OLEDB:Encrypt Database=False;" & _
"Jet OLEDBon't Copy Locale on Compact=False;" & _
"Jet OLEDB:Compact Without Replica Repair=False;" & _
"Jet OLEDB:SFP=False"

query_cmd.CommandType = XlCmdType.xlCmdTable

QueryTables.Add( _
Connection:=connection_string, _
Destination:=Excel.Range("A1"), _
Sql:=query_cmd)

End Sub
End Class

 

[Cindy M.]

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

Thanks again, I really appreciate your patience. However, I think I may have
thrown too much on the table at once. I took out the "with..end with"
statement to clarify my problem.

I'm trying to get my program to open, then import data from a database.
However, I keep running into the error depending upon how I word it. The
problem revolves around the
"query_cmd" declaration. If I declare it as:
Dim query_cmd As New Microsoft.Office.Interop.Excel.QueryTable
I get the error: Retrieving the COM class factory for component with CLSID
{00020819-0000-0000-C000-000000000046} failed due to the following error:
80040154
However, if I declare it as:
Dim query_cmd As Microsoft.Office.Interop.Excel.QueryTable
it throws a NullReferenceException when I try to use it as:
query_cmd.CommandType = XlCmdType.xlCmdTable
n other words...it never makes it to the QueryTables.Add(...)

Yes, that's what I've been trying to tell you

With an Office object model you MUST first create the object IN the Office
document. Only then can you assign properties to it. Take a close look again at
the sample I gave you in my last reply:

Dim QueryTable as Microsoft.Office.Interop.Excel.QueryTable

'Instantiate it by setting it to an existing object
'or creating the object
Set QueryTable = Excel.ActiveWorkbook.QueryTables.Add( _
Connection:=connection_string, _
Destination:=Excel.Range("A1"), _
Sql:=***)

***The object model Help says about the Sql parameter:
"Sql Optional Variant. The SQL query string to be run on the ODBC data
source. This argument is optional when you're using an ODBC data source (if you
don't specify it here, you should set it by using the Sql property of the query
table before the table is refreshed). You cannot use this argument when a
QueryTable object, text file, or ADO or DAO Recordset object is specified as
the data source."

Since it appears your data source is ADO (OLE DB), you should probably leave
this out or, if you have Option Explicit On you need to pass Type.Missing.

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.]

Thanks again, I've posted my simplified code to avoid confusion. It's
important to note that all previous posts of my code are irrelevant.
I'm still having problems with the query_cmd, I tried setting it to:
query_cmd=Excel.ActiveWorkbook.QueryTables.Add(...)
as suggested; however, neither QueryTables nor QueryTable are members of
ActiveWorkbook, only members of Microsoft.Office.Interop.Excel.Workbook are
members of ActiveWorkbook.



Imports Excel = Microsoft.Office.Interop.Excel
Public Class GetData
Dim ExcelApp As Excel.Application
Dim XlCmdType As Excel.XlCmdType
Public Sub Get_Data(ByVal family As String, ByVal Table As String)
Dim query_cmd As Excel.QueryTable
query_cmd = ExcelApp.ActiveWorkbook
query_cmd.CommandType = XlCmdType.xlCmdTable
End Sub
End Class

 

[Bear S.]

The fix was simple, only had to add

Implements Excel.QueryTable

after the class statement. Now, to figure out the use of the QueryTables.Add
*outside* of the "with...end with" statement.