Search code examples
sqlvbaoracleadodboraoledb

'Application.Transpose(rs.GetRows)' type mismatch error Nº 13 in SQL/VBA code

I'm trying to export data from an Oracle Database through VBA, and I'm getting an error Nº 13 Type Mismatch at line:

mtxData = Application.Transpose(rs.GetRows)

below is my entire code

Sub start()

Dim cn              As ADODB.Connection
Dim rs              As ADODB.Recordset
Dim mtxData         As Variant
Dim strSQL          As String

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

    cn.Open ( _
    "user ID =user" & _
    ";Password=password" & _
    ";data source=source" & _
    ";Provider=OraOLEDB.oracle")
    
    rs.CursorType = adOpenForwardOnly
    strSQL = ("SELECT * FROM table")
    rs.Open strSQL, cn
    mtxData = Application.Transpose(rs.GetRows)
        
    ActiveSheet.Range("A1:K22") = mtxData

below is the result I was expecting... enter image description here

Solution

  • You will get a type mismatch error from Transpose if the data you received via GetRows contains any null values.

    There is, however, a better way to dump the data you have in a RecordSet into Excel: Simply use the method Range.CopyFromRecordSet. Advantage is you don't need the transpose, and you need to specify only the start cell.

    Const connStr = "(Your connection String)"
Const sql = "(Your SQL)"

Dim cn              As ADODB.Connection
Dim rs              As ADODB.Recordset

Set cn = New ADODB.Connection
cn.Open connStr
Set rs = cn.Execute(sql)

With ActiveSheet
    .UsedRange.Clear
    .Range("A1").CopyFromRecordset rs
End With

    If you need also the column names, try this:

    With ActiveSheet
    .UsedRange.Clear
    Dim destRange As Range, colIndex As Long
    Set destRange = .Range("A1")
    ' Write column names
    For colIndex = 0 To rs.Fields.Count - 1
        destRange.Offset(0, colIndex) = rs(colIndex).Name
    Next colIndex
    ' Dump the data
    destRange.Offset(1, 0).CopyFromRecordset rs
End With