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/
