Using VB.Net 2005 to drive an Excel 2007 QueryTable problem

The below VB.Net 2005 code errors out on the line

        qryTable = oSheet.QueryTables.Add(conn, oSheet.Range("A1"), "Select * from tblActiveIngrediantAnalysis")

with the below error message.  Please help to resolve.  Thanks.

System.ArgumentException was unhandled
  Message="The parameter is incorrect. (Exception from HRESULT: 0x80070057 (E_INVALIDARG))"

Dim oExcel As New Excel.Application
        Dim oSheet As Excel.Worksheet
        oSheet = oExcel.ActiveSheet
        Dim conn As New SqlConnection(My.Settings.BRDWorklsates)
        Dim qryTable As Excel.QueryTable
        qryTable = oSheet.QueryTables.Add(conn, oSheet.Range("A1"), "Select * from tblActiveIngrediantAnalysis")
        With qryTable
            .RefreshStyle = XlCellInsertionMode.xlInsertEntireRows
        End With

Open in new window

Who is Participating?
Rory ArchibaldConnect With a Mentor Commented:
Your connection string would need to be:
"OLEDB;Provider=SQLOLEDB.1;Data Source=USGRRD3;Integrated Security=SSPI;Initial Catalog=BRDWorkslates"

Rory ArchibaldCommented:
The first parameter should be a connection string, not a Connection object.
KentDBerryAuthor Commented:
Thanks Rory.  Now I have a subsequent error:

"Exception from HRESULT: 0x800A03EC"

When I try:
qryTable = oSheet.QueryTables.Add(conn.ConnectionString, oExcel.ActiveSheet.Range("A1"))


qryTable = oSheet.QueryTables.Add(conn.ConnectionString, oExcel.ActiveSheet.Range("A1"), "Select * from tblActiveIngrediantAnalysis")

The new generation of project management tools

With’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Rory ArchibaldCommented:
What does the returned connection string look like? It should start with "ODBC;" or "OLEDB;"
KentDBerryAuthor Commented:
Well, it was this:

conn.ConnectionString = "Data Source=usgrrd3;Initial Catalog=BRDWorkslates;Integrated Security=True"

So I changed it to

Dim conn As New OleDb.OleDbConnection(My.Settings.OLEDB_Connection)
and conn.ConnectionString now equals
"Provider=SQLOLEDB.1;Data Source=USGRRD3;Integrated Security=SSPI;Initial Catalog=BRDWorkslates"

I still get the same error.  I'm trying to directly access a SQL Server.  Am I using the wrong approach.  Basically I'm wanting to quickly dump the results of 15,000 records from a SQL Server 2005 into Excel 2007 using VB.Net 2005

KentDBerryAuthor Commented:
That now works, Thanks.  Is there a way to add that OLEDB via the Project Settings form, so that I would not have had to append it as shown below?

qryTable = oSheet.QueryTables.Add("OLEDB; " & conn.ConnectionString, oExcel.ActiveSheet.Range("A1", "A1"), "Select * from tblActiveIngrediantAnalysis")
Rory ArchibaldCommented:
Not that I know of, no. The prefix depends entirely on the type of connection - whether it's OLEDB, ODBC, web etc.
KentDBerryAuthor Commented:
Thanks again for your help!!!
All Courses

From novice to tech pro — start learning today.