This project has moved and is read-only. For the latest updates, please go here.

What type of .Connection should I use?

Apr 11, 2012 at 6:33 PM

        Dim vItem(4) As Object, oConn As NetOffice.ExcelApi.ODBCConnection

        'Error 159 Value of type 'NetOffice.ExcelApi.WorkbookConnection' cannot be converted to 'NetOffice.ExcelApi.ODBCConnection'. 
        For Each oConn In wb.Connections
            vItem(1) = TypeName(oConn)
            vItem(2) = oConn.Connection
            vItem(3) = oConn.CommandText
            vItem(4) = oConn.CommandType

Apr 11, 2012 at 6:48 PM

hello patrick,

it is possible the workbook contains different kind of connections?
do you use ODBC connetction in VBA and works fine?
gimme a day to checkout what happen.


Sebastian

Apr 11, 2012 at 6:58 PM

Here's the VBA code:

         Dim vItem(4) As Variant, oConn As ODBCConnection
For Each oConn In wb.Connections
vItem(1) = TypeName(oConn)
vItem(2) = oConn.Connection

Apr 11, 2012 at 8:35 PM

this is not helpfull. same code as before. most of the time i see your notations ws means Workbook? aaah okay i have to research about the workbook type.
but anyway, your code works fine if you re-define oConn to ODBC Connection?

*Sebastian

Apr 11, 2012 at 8:57 PM

"same code as before." The code before was what I am trying in VB.NET, the second post is the original VBA from which it came.

" ws means Workbook", yes, and wb means workbook, always Dim wb as Workbook

"your code works fine if you re-define oConn to ODBC Connection?
The code does not even work yet, I'm still trying to get it to compile!

The line "For Each oConn In wb.Connections" is giving a compile error

 'Error 159 Value of type 'NetOffice.ExcelApi.WorkbookConnection' cannot be converted to 'NetOffice.ExcelApi.ODBCConnection'. 

I can see from the Object Browser

Public Class Connections
          Inherits LateBindingApi.Core.COMObject
and

Public ReadOnly Property Connections As NetOffice.ExcelApi.Connections
     Member of NetOffice.ExcelApi._Workbook
 

If I Dim oConn as WorkbookConnection I can then iterate,

and I can type oConn.ODBCConnection   

I can see

 Public Property CommandText As Object
     Member of NetOffice.ExcelApi.ODBCConnection
 

So that tells me I can do this:

        For Each oConn In wb.Connections
            vItem(1) = TypeName(oConn)
            vItem(2) = oConn.ODBCConnection 
            vItem(3) = oConn.ODBCConnection.CommandText
            vItem(4) = oConn.ODBCConnection.CommandType
    

 So that tells me I have to write separate code for OLEDBConnection, although VBA seems to allow a mixture.

OK, I can do that.

 

 

Apr 11, 2012 at 9:14 PM

i am confused. the Connections Property in Excel is defined as WorkbookConnection.
the exception occurs at runtime in which line?(generaly helpfull) oConn.ODBCConnection.CommandText?
gimme a chance to reproduce the situation on my system when! its possible.
thanks patrick ;)
Sebastian