Start Free Trial

asked on

How to automate adding a query to Excel 2007 spreadsheet?

I built a query generator to produce a complex query that needs to return results to an Excel 2007 spreadsheet.
I can run the query and dump the results into a spreadsheet, but I want to embed the query in the spreadsheet the same way as if MSQuery built it.
I want the user to save the spreadsheet and be able to update the data by right-clicking and choosing 'refresh data'.

The code below gives a 'parameter is invalid' error.
I tried many different variations for the 'connection string' parameter, from pure text, to the sqlconn object that I use later in the code that works.

Many hours searching the Internet yielded nothing than other people not able to get this to work.

excel.ActiveWorkbook.Connections.Add("Env Report", "New Report", ConfigurationManager.AppSettings("dev"), strQuery, CommandType.Text)
excel.ActiveWorkbook.RefreshAll()

Open in new window

Hi,

you can simply solve such problems by using the macro recorder of Excel. It shows you the commands and the right syntax. You only need to delete all unnecessary things and adapt it to your needs.

Here's what my macro recorder has done by using MS Query and SQL Server with AdventureWorks database. I only deleted the connection string, the rest is original macro recorder. The query opens the address table with ordering by city.

I used a similar solution to change the query with an Excel form to let the user choose some filter before querying.

Cheers,

Christian

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "Enter your connection string here" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandText = Array( _
        "SELECT Address.AddressID, Address.AddressLine1, Address.AddressLine2, Address.City, Address.StateProvinceID, Address.PostalCode, Address.rowguid, Address.ModifiedDate" & Chr(13) & "" & Chr(10) & "FROM AdventureWorks.Person.Addre" _
        , "ss Address" & Chr(13) & "" & Chr(10) & "ORDER BY Address.City")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Tabelle_Abfrage_von_AdventureWorks"
        .Refresh BackgroundQuery:=False
    End With

Open in new window

ASKER

The code below fails with:

System.Runtime.InteropServices.COMException (0x800A03EC): Exception from HRESULT: 0x800A03EC

Server stack trace:

Exception rethrown at [0]:
at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
at System.MarshalByRefObject.InvokeMember(String name, BindingFlags invokeAttr, Binder binder, Object[] args, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParameters)
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
at Microsoft.VisualBasic.CompilerServices.VBBinder.InvokeMember(String name, BindingFlags invokeAttr, Type objType, IReflect objIReflect, Object target, Object[] args, String[] namedParameters)
at Microsoft.VisualBasic.CompilerServices.LateBinding.LateGet(Object o, Type objType, String name, Object[] args, String[] paramnames, Boolean[] CopyBack)
at Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateGet(Object Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack)
at MatTracEnvQuery.wfrmQuery.RunQuery(String strQuery) in C:\Users\Public\Documents\Visual Studio Projects\MatTracEnvQuery\MatTracEnvQuery\wfrmQuery.vb:line 835

 With excel.ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
                    "Data Source=hrothnir;Initial Catalog=HalMatTrac;Integrated Security=True" _
                    , Destination:=excel.Range("$A$1")).QueryTable
                .CommandText = strQuery
                .RowNumbers = False
                .FillAdjacentFormulas = False
                .PreserveFormatting = True
                .RefreshOnFileOpen = False
                .BackgroundQuery = True
                .RefreshStyle = excel.xlInsertDeleteCells
                .SavePassword = False
                .SaveData = True
                .AdjustColumnWidth = True
                .RefreshPeriod = 0
                .PreserveColumnInfo = True
                .ListObject.DisplayName = "MatTrac Report"
                .Refresh(BackgroundQuery:=False)
            End With

Open in new window

ASKER

Possibly its the .CommandText line.
Visual Studio didn't like 'Array', so I just used the strquery variable by itself.

ASKER

So I added an arraylist.
Still fails.

Dim myArray As New ArrayList
            With excel.ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
                    "Data Source=hrothnir;Initial Catalog=HalMatTrac;Integrated Security=True" _
                    , Destination:=excel.Range("$A$1")).QueryTable
                .CommandText = myArray(strQuery)
                .RowNumbers = False
                .FillAdjacentFormulas = False
                .PreserveFormatting = True
                .RefreshOnFileOpen = False
                .BackgroundQuery = True
                .RefreshStyle = excel.xlInsertDeleteCells
                .SavePassword = False
                .SaveData = True
                .AdjustColumnWidth = True
                .RefreshPeriod = 0
                .PreserveColumnInfo = True
                .ListObject.DisplayName = "MatTrac Report"
                .Refresh(BackgroundQuery:=False)
            End With

Open in new window

Hi,

you didn't say that you tried that from within VB, the code above is of course VBA which supports this. I don't think that the array is necessary as this is the command text, try it with a simple SQL string instead.
That's what I meant: The macro recorder often implements unnecessary code, normally it should be enough with the shown way. Another solution could be to implement the code in VBA inside the sheet and call it from VB which should be possible (but I have not enough experience with VB to help you with this).

Cheers,

Christian

ASKER

The array isn't the problem, apparently. Crashed with or without.
Using the macro recorder is a great idea, but it's sometimes difficult to translate the code into usable VB.NET code.
The crash occurs for other reasons. that I can't figure out.
As this gets deployed with high security in a domain environment, no VBA is used.
We use integrated security and SQL Client, not oledb.
Really appreciate the help. especially during holidays.
I'm alternating coding, cleaning, and cooking for a party tonight..

Maybe you should be a little more specific, what's the displayed error and where is it?
And a little code fragment, how did you built the connection to the Excel file within VB so maybe I could try to test this here in VB Express.
Hope you had a great party and you didn't mix the tasks...:-)

Happy new year,

Christian

ASKER

Error is posted above for the below code, which I posted a more complete segment.

Imports System.Collections
Imports System.Data.SqlClient
Imports System.Configuration
  
  Private Sub RunQuery(ByVal strQuery As String)
        Dim SqlConn As SqlConnection
        '  Dim sqlCmd As New SqlCommand
        Dim ds As New DataSet("Report")
        Dim col As DataColumn
        Dim row As DataRow
        Dim colIndex As Integer = 0
        Dim rowIndex As Integer = 1
        Dim excel As Excel.Application = Globals.ThisWorkbook.ThisApplication  'New Excel.Application
        '   excel.Application.Workbooks.Add(True)

        ' MsgBox(ConfigurationManager.ConnectionStrings("MatTracEnvQuery.MySettings.HalMatTracConnectionString").ConnectionString)
        ' SqlConn = New SqlConnection("Data Source=halmattrac;Initial Catalog=HalMatTrac;Integrated Security=True")
        '  SqlConn = New SqlConnection("Data Source=hrothnir;Initial Catalog=HalMatTrac;Integrated Security=True")
        Try
            ' SqlConn = New SqlConnection(ConfigurationManager.AppSettings("dev"))
            SqlConn = New SqlConnection(ConfigurationManager.ConnectionStrings("MatTracEnvQuery.MySettings.HalMatTracConnectionString").ToString)
            Dim da As New SqlDataAdapter(strQuery, SqlConn)
            Dim dasql As New System.Data.SqlClient.SqlDataAdapter(strQuery, SqlConn)


            dasql.Fill(ds, "Report") 'fill dataset

            'Add connection to Excel workbook         ' excel.ActiveWorkbook.Connections.Add("name", "desc", SqlConn, "command text", "cmdtype")
            '  excel.ActiveWorkbook.Connections.Add("Env Report", "MatTrac report", "Data Source=hrothnir;Initial Catalog=HalMatTrac;Integrated Security=True", strQuery, CommandType.Text)
            'excel.ActiveWorkbook.Connections.Add("EnvReport", "Report", " providerName=System.Data.SqlClient; Data Source=hrothnir;Initial Catalog=HalMatTrac;Integrated Security=True", "select * from Country")
            'excel.ActiveWorkbook.RefreshAll()
            'Exit Sub
            Dim myArray As New ArrayList
            With excel.ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
                    "providerName=System.Data.SqlClient;Data Source=hrothnir;Initial Catalog=HalMatTrac;Integrated Security=True" _
                    , Destination:=excel.Range("$A$1")).QueryTable
                .CommandText = myArray(strQuery)
                .RowNumbers = False
                .FillAdjacentFormulas = False
                .PreserveFormatting = True
                .RefreshOnFileOpen = False
                .BackgroundQuery = True
                .RefreshStyle = excel.xlInsertDeleteCells
                .SavePassword = False
                .SaveData = True
                .AdjustColumnWidth = True
                .RefreshPeriod = 0
                .PreserveColumnInfo = True
                .ListObject.DisplayName = "MatTrac Report"
                .Refresh(BackgroundQuery:=False)
            End With

            Exit Sub
        Catch ex As Exception
            MsgBox(ex.ToString)
            tbStatus.Text = ex.ToString
            Exit Sub
        End Try

Open in new window

ASKER

It dies on line 34 of the above code segment. I use the same SQL connection that I use to run the query and dump to the spreadsheet. It works, but leaves me with a static spreadsheet that can't be refreshed.

Hi,

after experimenting a little bit I got it working. The real big problem with this is indeed the connection string and nothing else. I tested it on a machine with Excel 2003 and tried to implement the SQLOLEDB driver but nothing worked. Only if I used ODBC everything works fine. Maybe someone has a better idea so ODBC could be avoided (because this will need a local installation of the right ODBC connection), here's the working code.
The only reference needed was the Excel reference which automatically added VBA reference. After reading a bit about it I think that by using the COM object of Excel to access it's methods you use VBA so maybe you will need additional security considerations to get this working in a high security environment.
Here's a link about the macro security settings to get access from a VB project without security warnings:
http://support.microsoft.com/?scid=kb%3Ben-us%3B282830&x=12&y=10

In my tests this setting was not needed.

The code based on a simple Windows Form with one button "Button1" to execute it. The Array is not needed, it's a strange method of the macro recorder to assemble long strings together.

Cheers,

Christian

Public Class Form1

    Private Sub RunQuery(ByVal strQuery As String)
        Dim excel As New Excel.Application
        excel.Application.Workbooks.Add(True)
        excel.Visible = True
        Dim objSheet As New Excel.Worksheet
        objSheet = excel.Sheets(1)
        Dim strConnection As String = "ODBC;DSN=AdventureWorksDSN;UID=WindowsUsername;APP=Microsoft Office 2003;WSID=Computername;DATABASE=AdventureWorks;Trusted_Connection=Yes"
        Try
            With objSheet.QueryTables.Add(strConnection, objSheet.Range("$A$1"), strQuery)
                .RowNumbers = False
                .FillAdjacentFormulas = False
                .PreserveFormatting = True
                .RefreshOnFileOpen = False
                .BackgroundQuery = True
                .RefreshStyle = Global.Excel.XlCellInsertionMode.xlInsertDeleteCells
                .SavePassword = False
                .SaveData = True
                .AdjustColumnWidth = True
                .RefreshPeriod = 0
                .PreserveColumnInfo = True
                .Refresh(BackgroundQuery:=False)
            End With
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim strSQL As String
        strSQL = "SELECT * FROM AdventureWorks.Person.Address"
        RunQuery(strSQL)
    End Sub
End Class

Open in new window

...by the way: The first macro recorder code above was recorded with Excel 2007 which gives a "ListObjects" object in between but as you can see from the Excel 2003 example it is not needed.

The ODBC issue maybe can be solved by installing the right OLEDB or SQL Native Client driver on the target machine(s) but I didn't test that.

ASKER

I use SQL Native Client for all the other parts of the code. I use it to fill all the controls and the dataset in the form. Visual studio 2008 and the latest VSTO uses SQLNCli for their auto generated code.
Some of the newer stuff requires SQLNCLI.
OLEDB is a possibility but ODBC can't be used, but mixing connection types in the code would be bad.

My dev machine certainly has SQLNCL installed and I can't get it to work with this code.
I wonder if its just not supported or bugged? It's certainly not documented, and most likely never tested.

You're not mixing the connection types because as you add a connection to an Excel file it is completely independent of your VB code - the connection and the query is executed by Excel and only there. So normally it should not make any difference which connection type you use.
In Excel 2007 it should be possible to select SQLNCLI, but former versions seem to not support other than ODBC connections. In Excel (2003) itself there is no other way than to choose an ODBC connection - or I couldn't find any other. Excel 2007 supports direct SQL Server connections and editable connection strings so here it should be no problem with VB and Excel but I didn't test this.
The only disadvantage with ODBC is that you need to install an ODBC connection on the target system. But using Excel 2007 this should no longer be needed. Just test it, there are examples on www.connectionstrings.com.

Cheers,

Christian

ASKER

I've tried every variation of the connection strings for .NET Framework dataprovider several times now.
Same error message as posted above. Tried all that before even asking this question.

As for ODBC, I'd need to get permission to install an ODBC connection on the company's workstations and an automated way to install it that would not require the end user to do more than click on it that would support XP and Win 7.

I tried the a modified connection string that I used in Excel 2003:
"DRIVER=SQL Server;SERVER=hrothnir;UID=;;APP=Microsoft Office 2007;WSID=;DATABASE=HalMatTrac;Trusted_Connection"

and got:
System.Runtime.InteropServices.COMException (0x800A03EC): Exception from HRESULT: 0x800A03EC

Server stack trace:

Exception rethrown at [0]:
at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
at System.MarshalByRefObject.InvokeMember(String name, BindingFlags invokeAttr, Binder binder, Object[] args, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParameters)
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
at Microsoft.VisualBasic.CompilerServices.VBBinder.InvokeMember(String name, BindingFlags invokeAttr, Type objType, IReflect objIReflect, Object target, Object[] args, String[] namedParameters)
at Microsoft.VisualBasic.CompilerServices.LateBinding.LateGet(Object o, Type objType, String name, Object[] args, String[] paramnames, Boolean[] CopyBack)
at Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateGet(Object Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack)
at MatTracEnvQuery.wfrmQuery.RunQuery(String strQuery) in C:\Users\Public\Documents\Visual Studio Projects\MatTracEnvQuery\MatTracEnvQuery\wfrmQuery.vb:line 836

That's exactly the error I got all the time trying to use any other connection method than ODBC. That's why I said that I'm afraid Excel 2003 can only use ODBC which works like shown above in the VB.NET code (made it with VB 2008).

You don't need to install ODBC on the workstations because you can use a file DSN instead of system or user DSN so the only permission needed is to copy the created DSN file to the user's workstation. The best place is of course in common files in the programs folder but maybe there are restrictions to write there. So simply use your VB.NET solution to copy the DSN file to the VB application folder where you should have full access to from the VB solution. Then use this folder and file inside the connection string.
Here is a page with examples for FILEDSN:
http://www.codemaker.co.uk/it/tips/ado_conn.htm

Cheers,

Christian

ASKER

Ahh, this is an Excel 2007 solution.
I created a system DSN file, and connected it to my database, tested OK.
Then I added it to my code as the connection string.
Crash!.
Maybe I got the syntax wrong in my code?
What did your test code look like?

System.Runtime.InteropServices.COMException (0x800A03EC): Exception from HRESULT: 0x800A03EC

Server stack trace:

Exception rethrown at [0]:
at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
at System.MarshalByRefObject.InvokeMember(String name, BindingFlags invokeAttr, Binder binder, Object[] args, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParameters)
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
at Microsoft.VisualBasic.CompilerServices.VBBinder.InvokeMember(String name, BindingFlags invokeAttr, Type objType, IReflect objIReflect, Object target, Object[] args, String[] namedParameters)
at Microsoft.VisualBasic.CompilerServices.LateBinding.LateGet(Object o, Type objType, String name, Object[] args, String[] paramnames, Boolean[] CopyBack)
at Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateGet(Object Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack)
at MatTracEnvQuery.wfrmQuery.RunQuery(String strQuery) in C:\Users\Public\Documents\Visual Studio Projects\MatTracEnvQuery\MatTracEnvQuery\wfrmQuery.vb:line 836

With excel.ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
                    "FILEDSN=C:\Users\Public\Documents\Visual Studio Projects\MatTracEnvQuery\MatTracEnvQuery\halmattrac.dsn", Destination:=excel.Range("$A$1")).QueryTable
                .CommandText = (strQuery)
                .RowNumbers = False
                .FillAdjacentFormulas = False
                .PreserveFormatting = True
                .RefreshOnFileOpen = False
                .BackgroundQuery = True
                .RefreshStyle = excel.xlInsertDeleteCells
                .SavePassword = False
                .SaveData = True
                .AdjustColumnWidth = True
                .RefreshPeriod = 0
                .PreserveColumnInfo = True
                .ListObject.DisplayName = "MatTrac Report"
                .Refresh(BackgroundQuery:=False)
            End With

Open in new window

ASKER

I also tried this string, which is supposed to be ODBC.

With excel.ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"Driver={SQL Server};Server=hrothnir; Database=halmattrac; Trusted_Connection=yes", Destination:=excel.Range("$A$1")).QueryTable
.CommandText = (strQuery)

Same crash.

Hi,

so, now I tried it again using Excel 2007. I made an example for both, DSN with file and ODC with file. The last one would be preferable because it is faster.
I created the DSN solution with a DSN file and the interesting result was that the macro recorder used the DSN only to access the server and read the table structure, then added the connection string to the module and in the connection string the DSN file doesn't appear. I tested it by renaming the original DSN file and the solution works without any problem.
The same thing with the ODC file: It CAN be used but it is not necessary. Both solutions are tested and working.
I attached the source code below and adjusted it to your setting, you only need to change computername (if you want to use this) and your SELECT command.

For Excel 2007 there were some changes needed to get the right result, especially the Import at the beginning.

Cheers,

Christian

Imports Microsoft.Office.Interop

Public Class Form1

    Private Sub RunQueryODC(ByVal strQuery As String)
        Dim excel As New Excel.Application
        excel.Application.Workbooks.Add(True)
        excel.Visible = True
        Dim objSheet As New Excel.Worksheet
        objSheet = excel.Sheets(1)
        Try
			' You can adjust computername if necessary for your application or just delete it
            With objSheet.ListObjects.Add(SourceType:=0, _
               Source:="OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
                       "Persist Security Info=True;Data Source=hrothnir;" & _
                       "Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;" & _
                       "Workstation ID=YourComputerName;Use Encryption for Data=False;" & _
                       "Tag with column collation when possible=False;Initial Catalog=HalMatTrac", _
                       Destination:=objSheet.Range("$A$1")).QueryTable
                .CommandType = Microsoft.Office.Interop.Excel.XlCmdType.xlCmdTable
				.CommandText = strQuery
                .RowNumbers = False
                .FillAdjacentFormulas = False
                .PreserveFormatting = True
                .RefreshOnFileOpen = False
                .BackgroundQuery = True
                .RefreshStyle = Microsoft.Office.Interop.Excel.XlCellInsertionMode.xlInsertDeleteCells
                .SavePassword = False
                .SaveData = True
                .AdjustColumnWidth = True
                .RefreshPeriod = 0
                .PreserveColumnInfo = True
				' You can use this but it is not necessary
                ' .SourceConnectionFile = "D:\VBProjects\NameOfYourODCFile.odc"
                .ListObject.DisplayName = "MatTrac Report"
                .Refresh(BackgroundQuery:=False)
            End With
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub RunQueryDSN(ByVal strQuery As String)
        Dim excel As New Excel.Application
        excel.Application.Workbooks.Add(True)
        excel.Visible = True
        Dim objSheet As New Excel.Worksheet
        objSheet = excel.Sheets(1)

        Try
			'  Here you must only change the computername, like above
            With objSheet.ListObjects.Add(SourceType:=0, _
                                  Source:="ODBC;DRIVER=SQL Server Native Client 10.0;SERVER=hrothnir;" & _
                                          "UID=root;Trusted_Connection=Yes;APP=Microsoft® Windows® Operating System;" & _
                                          "WSID=YourComputerName;DATABASE=HalMatTrac;", _
                                  Destination:=objSheet.Range("$A$1")).QueryTable
				.CommandText = strQuery
                .RowNumbers = False
                .FillAdjacentFormulas = False
                .PreserveFormatting = True
                .RefreshOnFileOpen = False
                .BackgroundQuery = True
                .RefreshStyle = Microsoft.Office.Interop.Excel.XlCellInsertionMode.xlInsertDeleteCells
                .SavePassword = False
                .SaveData = True
                .AdjustColumnWidth = True
                .RefreshPeriod = 0
                .PreserveColumnInfo = True
                .ListObject.DisplayName = "MatTrac Report"
                .Refresh(BackgroundQuery:=False)
            End With
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub


    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim strSQL As String
        ' Here you must change the command to your need
        strSQL = "SELECT * FROM HalMatTrac.Schema.TableName TableName"
        RunQueryDSN(strSQL)
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim strSQL As String
        Dim strSQL As String
        ' Here you must change the command to your need
        strSQL = "SELECT * FROM HalMatTrac.Schema.TableName TableName"
        RunQueryODC(strSQL)
    End Sub
End Class

Open in new window

... I forgot: you must change the username, too...:-)

ASKER

I created a new project and tried to run your code, modifying it slightly as needed.
It complained about ' .ListObject.DisplayName = "MatTrac Report", so I commented it out.
I do get query objects embedded in the spreadsheet, but they crash when running.
This is much further than I have gotten before.

Line 72 General ODBC error with RunQueryDSN

Line 38 COM exception: The query did not run, or the database table could not be opened. with RunQueryODC

Imports Microsoft.Office.Interop

Public Class Form1

    Private Sub RunQueryODC(ByVal strQuery As String)
        Dim excel As New Excel.Application
        excel.Application.Workbooks.Add(True)
        excel.Visible = True
        Dim objSheet As New Excel.Worksheet
        objSheet = excel.Sheets(1)
        Try
            ' You can adjust computername if necessary for your application or just delete it
            With objSheet.ListObjects.Add(SourceType:=0, _
               Source:="OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
                       "Persist Security Info=True;Data Source=hrothnir;" & _
                       "Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;" & _
                       "Workstation ID=;Use Encryption for Data=False;" & _
                       "Tag with column collation when possible=False;Initial Catalog=HalMatTrac", _
                       Destination:=objSheet.Range("$A$1")).QueryTable
                .CommandType = Microsoft.Office.Interop.Excel.XlCmdType.xlCmdTable
                .CommandText = strQuery
                .RowNumbers = False
                .FillAdjacentFormulas = False
                .PreserveFormatting = True
                .RefreshOnFileOpen = False
                .BackgroundQuery = True
                .RefreshStyle = Microsoft.Office.Interop.Excel.XlCellInsertionMode.xlInsertDeleteCells
                .SavePassword = False
                .SaveData = True
                .AdjustColumnWidth = True
                .RefreshPeriod = 0
                .PreserveColumnInfo = True
                ' You can use this but it is not necessary
                ' .SourceConnectionFile = "D:\VBProjects\NameOfYourODCFile.odc"
                '      .ListObject.DisplayName = "MatTrac Report"
                .Refresh(BackgroundQuery:=False)
            End With
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub RunQueryDSN(ByVal strQuery As String)
        Dim excel As New Excel.Application
        excel.Application.Workbooks.Add(True)
        excel.Visible = True
        Dim objSheet As New Excel.Worksheet
        objSheet = excel.Sheets(1)

        Try
            '  Here you must only change the computername, like above
            With objSheet.ListObjects.Add(SourceType:=0, _
                                  Source:="ODBC;DRIVER=SQL Server Native Client 10.0;SERVER=hrothnir;" & _
                                          "UID=root;Trusted_Connection=Yes;APP=Microsoft® Windows® Operating System;" & _
                                          "WSID=;DATABASE=HalMatTrac;", _
                                  Destination:=objSheet.Range("$A$1")).QueryTable
                .CommandText = strQuery
                .RowNumbers = False
                .FillAdjacentFormulas = False
                .PreserveFormatting = True
                .RefreshOnFileOpen = False
                .BackgroundQuery = True
                .RefreshStyle = Microsoft.Office.Interop.Excel.XlCellInsertionMode.xlInsertDeleteCells
                .SavePassword = False
                .SaveData = True
                .AdjustColumnWidth = True
                .RefreshPeriod = 0
                .PreserveColumnInfo = True
                '    .ListObject.DisplayName = "MatTrac Report"
                .Refresh(BackgroundQuery:=False)
            End With
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub


   
    Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim strSQL As String
        ' Here you must change the command to your need
        strSQL = "SELECT * FROM Country"
        RunQueryDSN(strSQL)
    End Sub

    Private Sub Button2_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim strSQL As String
        ' Here you must change the command to your need
        strSQL = "SELECT * FROM Country"
        RunQueryODC(strSQL)
    End Sub
End Class

Open in new window

ASKER

System.Runtime.InteropServices.COMException (0x800A03EC): General ODBC Error
at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)
at Microsoft.Office.Interop.Excel._QueryTable.Refresh(Object BackgroundQuery)
at WindowsApplication2.Form1.RunQueryDSN(String strQuery) in C:\Users\Public\Documents\Visual Studio Projects\WindowsApplication2\WindowsApplication2\Form1.vb:line 73

*******************************************************************************************************************

System.Runtime.InteropServices.COMException (0x800A03EC): The query did not run, or the database table could not be opened.

Check the database server or contact your database administrator. Make sure the external database is available and hasn't been moved or reorganized, then try the operation again.
at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)
at Microsoft.Office.Interop.Excel._QueryTable.Refresh(Object BackgroundQuery)
at WindowsApplication2.Form1.RunQueryODC(String strQuery) in C:\Users\Public\Documents\Visual Studio Projects\WindowsApplication2\WindowsApplication2\Form1.vb:line 38

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Same crash.
At least the query is getting embedded.
I think I made all the needed changes. --see code attached.

Imports Microsoft.Office.Interop

Public Class Form1

    Private Sub RunQueryODC(ByVal strQuery As String)
        Dim excel As New Excel.Application
        excel.Application.Workbooks.Add(True)
        excel.Visible = True
        Dim objSheet As New Excel.Worksheet
        objSheet = excel.Sheets(1)
        Try
            ' You can adjust computername if necessary for your application or just delete it
            With objSheet.ListObjects.Add(SourceType:=0, _
               Source:="OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
                       "Persist Security Info=True;Data Source=hrothnir;" & _
                       "Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;" & _
                       "Use Encryption for Data=False;" & _
                       "Tag with column collation when possible=False;Initial Catalog=HalMatTrac", _
                       Destination:=objSheet.Range("$A$1")).QueryTable
                .CommandType = Microsoft.Office.Interop.Excel.XlCmdType.xlCmdTable
                .CommandText = strQuery
                .RowNumbers = False
                .FillAdjacentFormulas = False
                .PreserveFormatting = True
                .RefreshOnFileOpen = False
                .BackgroundQuery = True
                .RefreshStyle = Microsoft.Office.Interop.Excel.XlCellInsertionMode.xlInsertDeleteCells
                .SavePassword = False
                .SaveData = True
                .AdjustColumnWidth = True
                .RefreshPeriod = 0
                .PreserveColumnInfo = True
                ' You can use this but it is not necessary
                ' .SourceConnectionFile = "D:\VBProjects\NameOfYourODCFile.odc"
                .ListObject.DisplayName = "MatTracReport"
                .Refresh(BackgroundQuery:=False)
            End With
        Catch ex As Exception
            MsgBox(ex.ToString)
            tbStatus.Text = ex.ToString
        End Try
    End Sub

    Private Sub RunQueryDSN(ByVal strQuery As String)
        Dim excel As New Excel.Application
        excel.Application.Workbooks.Add(True)
        excel.Visible = True
        Dim objSheet As New Excel.Worksheet
        objSheet = excel.Sheets(1)

        Try
            '  Here you must only change the computername, like above
            With objSheet.ListObjects.Add(SourceType:=0, _
                                  Source:="ODBC;DRIVER=SQL Server Native Client 10.0;SERVER=hrothnir;" & _
                                          ";Trusted_Connection=Yes;APP=Microsoft® Windows® Operating System;" & _
                                          "DATABASE=HalMatTrac;", _
                                  Destination:=objSheet.Range("$A$1")).QueryTable
                .CommandText = strQuery
                .RowNumbers = False
                .FillAdjacentFormulas = False
                .PreserveFormatting = True
                .RefreshOnFileOpen = False
                .BackgroundQuery = True
                .RefreshStyle = Microsoft.Office.Interop.Excel.XlCellInsertionMode.xlInsertDeleteCells
                .SavePassword = False
                .SaveData = True
                .AdjustColumnWidth = True
                .RefreshPeriod = 0
                .PreserveColumnInfo = True
                .ListObject.DisplayName = "MatTracReport"
                .Refresh(BackgroundQuery:=False)
            End With
        Catch ex As Exception
            MsgBox(ex.ToString)
            tbStatus.Text = ex.ToString

        End Try
    End Sub


   
    Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim strSQL As String
        ' Here you must change the command to your need
        strSQL = "SELECT * FROM Country"
        RunQueryDSN(strSQL)
    End Sub

    Private Sub Button2_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim strSQL As String
        ' Here you must change the command to your need
        strSQL = "SELECT * FROM Country"
        RunQueryODC(strSQL)
    End Sub
End Class

Open in new window

ASKER

The ODC one works now, thanks..

The DSN one still crashes with:
System.Runtime.InteropServices.COMException (0x800A03EC): General ODBC Error
at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)
at Microsoft.Office.Interop.Excel._QueryTable.Refresh(Object BackgroundQuery)
at WindowsApplication2.Form1.RunQueryDSN(String strQuery) in C:\Users\Public\Documents\Visual Studio Projects\WindowsApplication2\WindowsApplication2\Form1.vb:line 73

Private Sub RunQueryDSN(ByVal strQuery As String)
        Dim excel As New Excel.Application
        excel.Application.Workbooks.Add(True)
        excel.Visible = True
        Dim objSheet As New Excel.Worksheet
        objSheet = excel.Sheets(1)

        Try
            '  Here you must only change the computername, like above
            With objSheet.ListObjects.Add(SourceType:=0, _
                                  Source:="ODBC;DRIVER=SQL Server Native Client 10.0;SERVER=hrothnir;" & _
                                          ";Trusted_Connection=Yes;" & _
                                          "DATABASE=HalMatTrac;", _
                                  Destination:=objSheet.Range("$A$1")).QueryTable
                .CommandType = Microsoft.Office.Interop.Excel.XlCmdType.xlCmdSql
                .CommandText = strQuery
                .RowNumbers = False
                .FillAdjacentFormulas = False
                .PreserveFormatting = True
                .RefreshOnFileOpen = False
                .BackgroundQuery = True
                .RefreshStyle = Microsoft.Office.Interop.Excel.XlCellInsertionMode.xlInsertDeleteCells
                .SavePassword = False
                .SaveData = True
                .AdjustColumnWidth = True
                .RefreshPeriod = 0
                .PreserveColumnInfo = True
                .ListObject.DisplayName = "MatTrac_Report"
                .Refresh(BackgroundQuery:=False)
            End With
        Catch ex As Exception
            MsgBox(ex.ToString)
            tbStatus.Text = ex.ToString

        End Try
    End Sub

Open in new window

ASKER

The crash in line 73 is at .Refresh(BackgroundQuery:=False)

I'm going to get this in good enough shape to test on my clients network tomorrow.
The oledb version may or may not work on that network even though it does on mine ( we had to switch to SQLNCli for some other code to get all the trusts and Active Directory stuff to work).

You have one semicolon too much in your connection string before "Trusted Connection..." in the DSN variant. Maybe that's the reason?

ASKER

No, fixed that. Still same crash. Something still wrong with that connection string...

You can only go to VBA in Excel, copy the same code (with little modifications for the objects like shown in the first example as VBA uses other constant links as VB.NET) to a module and test it there until it works here. The error says "General ODBC error" so I think it will be a problem with the connection to your database itself. The same will happen in VBA but here it's easier to debug.

If you are unsure, like said at the beginning, use the macro recorder in your environment to create a DSN connection with a DSN file then you have the exact right connection string for your environment - I did the same before adjusting it to VB.NET.
My tests here has shown that it works in both cases so the only problem can be at your environment with the special kind of connection to your server. Maybe a firewall or not sufficient rights to the server with the used login or anything like that. Sometimes you must use the complete domain name for the server if it cannot be found with the short name - whatever. I'm afraid, I cannot help you further with this as the rest can only be tested in your environment. So again: Use the macro recorder, create a successful connection, stop the macro recorder and look at the modules to see what's in there. Maybe it's not enough to only use the connection string you see, check ALL the additional properties inside the WITH command.

Cheers,

Christian

ASKER

The DSN connectiondoesn't work--I don't know if it's because I'm running Win 7 64bit --with all the extra security or not. Doesn't matter if the oledb one works.

The oledb connection works, and I'll try that on my client's network.

This was a two part question:
1. Adding the query to the spreadsheet programatically
2. Getting the connection string to work

Maybe the Windows 7/64Bit and additional security here can produce further problems - I only tested it with simple Windows XP/Vista 32 Bit...:-)
Thanks for the points, hope I could help you.