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
        oExcel.Workbooks.Add()
        oSheet = oExcel.ActiveSheet
 
        Dim conn As New SqlConnection(My.Settings.BRDWorklsates)
        conn.Open()
        Dim qryTable As Excel.QueryTable
        qryTable = oSheet.QueryTables.Add(conn, oSheet.Range("A1"), "Select * from tblActiveIngrediantAnalysis")
        With qryTable
            .RefreshStyle = XlCellInsertionMode.xlInsertEntireRows
            .Refresh(False)
        End With

Open in new window

KentDBerryAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
Rory ArchibaldCommented:
The first parameter should be a connection string, not a Connection object.
Regards,
Rory
0
 
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"))

or

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


Thoughts?
0
 
Rory ArchibaldCommented:
What does the returned connection string look like? It should start with "ODBC;" or "OLEDB;"
 
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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


0
 
Rory ArchibaldCommented:
Your connection string would need to be:
"OLEDB;Provider=SQLOLEDB.1;Data Source=USGRRD3;Integrated Security=SSPI;Initial Catalog=BRDWorkslates"

0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
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")
0
 
Rory ArchibaldCommented:
Not that I know of, no. The prefix depends entirely on the type of connection - whether it's OLEDB, ODBC, web etc.
0
 
KentDBerryAuthor Commented:
Thanks again for your help!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.