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

Question

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...

Is your SQL-statement returning anything? Split the command into two lines: `mtxData = rs.GetRows : mtxData = Application.Transpose(mtxData)` and check the content of `mtxData` when the error occurs — FunThomas, Nov 03 '22 at 13:05
Hi @FunThomas, just checked and my sql statement does return something, I used the line "MsgBox "max records found: " & rs.RecordCount" and it returned the number 21, which is exactly the number of rows inside the table. I also tried the splitting suggestion into two lines, but the same problem occur when the code reachs the Application.transpose, could this error be occurring due to somekind of data? — Savio, Nov 03 '22 at 14:36

score 0 · Accepted Answer · answered Nov 03 '22 at 15:23

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

hi, the second code that extract the column names works very well... but I think I'm missing something here, with the recordset I can get the data that I need, but it seems like I still need the transpose method, what am I missing here? Thank you btw — Savio, Nov 03 '22 at 18:44
The `CopyFromRecordset`-method writes the data directly to the sheet, no transpose needed. — FunThomas, Nov 04 '22 at 06:59

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

1 Answers1