Forgot to say that this needs the Firebird ODBC driver to be set up. RBS
On Wed, May 2, 2012 at 12:03 AM, Bart Smissaert <[email protected]>wrote: > This is some simple VBA code that demonstrates how this can be done. > In Excel you need to set a reference (in the VBE: Tools, References) to > Microsoft ActiveX Data Objects 2.x Library. > > Option Explicit > Public oADOConn As ADODB.Connection > > Sub OpenDBConnection(strConnString As String, _ > Optional strUserName As String, _ > Optional strPassWord As String) > > If oADOConn Is Nothing Then > Set oADOConn = New ADODB.Connection > End If > If oADOConn.State = 0 Then > oADOConn.Open strConnString, strUserName, strPassWord > End If > > End Sub > > Sub testing() > > Dim strConn As String > Dim strSQL As String > Dim oRs As ADODB.Recordset > Dim arr() As Variant > Dim arr2() As Variant > > strConn = "Driver={Firebird/InterBase(r) driver};" & _ > "Dbname=localhost:C:\folder\xxx.fdb;" & _ > "PWD=aaaa;" & _ > "UID=bbbb;" > > OpenDBConnection strConn > > Set oRs = New ADODB.Recordset > > strSQL = "SELECT * FROM TABLE1" > > oRs.Open strSQL, oADOConn, adOpenForwardOnly, adLockReadOnly > > arr = oRs.GetRows > > arr2 = Transpose_Array(arr) > > Range(Cells(1), Cells(UBound(arr2) + 1, UBound(arr2, 2) + 1)) = arr2 > > End Sub > > Function Transpose_Array(vArr() As Variant) As Variant() > > If Not IsArray(vArr) Then > Transpose_Array = vArr > Exit Function > End If > > Dim r As Long > Dim c As Long > Dim LB As Long > Dim UB1 As Long > Dim UB2 As Long > Dim arrT() As Variant > > LB = LBound(vArr) > UB1 = UBound(vArr) > UB2 = UBound(vArr, 2) > > ReDim arrT(LB To UB2, LB To UB1) > > For r = LB To UB1 > For c = LB To UB2 > arrT(c, r) = vArr(r, c) > Next c > Next r > > Transpose_Array = arrT > > End Function > > > RBS > > > > On Tue, May 1, 2012 at 2:33 PM, Maury Markowitz <[email protected] > > wrote: > >> ** >> >> >> In a former life I was a SQLServer+Excel guru, but today I am merely the >> noob. Does anyone have some sample VBA of calling Firebird via ADODB from >> Excel, or Office in general? I'm sure I can get up to speed quickly, but >> that first step... >> >> > > [Non-text portions of this message have been removed] ------------------------------------ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: [email protected] [email protected] <*> To unsubscribe from this group, send an email to: [email protected] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
