[Solved] SQL Query output in VBA is different than in SQL Oracle

Question

The problem is CopyFromRecordset – it’s truncating at 255 chars, and it’s not the only Excel.Range method that does that.

The question is: do I have a method that doesn’t? And do you have an an OLEDB driver that’s doing it to your Recordset before you even get to the stage of writing to the range?

You should loop through your recordset, in VBA, and check the offending field in VBA for a value exceeding 255 chars in length. If the fields are truncated already, try using the native Oracle Client drivers in your connection string, instead of the Microsoft Oracle OLEDB provider – Connections.com will have the information.

Once you know that the recordset actually contains your data, without truncation, try CopyFromRecordset again. I don’t actually expect it to write a field exceeding 255 chars in length, but it’s been a while since I encountered the error, it might have been fixed, and it’s always nice to give a pessimist a pleasant surprise.

Next up:

A VBA Substitute for CopyFromRecordset

There are three tasks here:

Populate a VBA array variant with the data using
Recordset.GetRows();
Transpose the array, because GetRows is the wrong way ’round for
Excel;
Size up a target range and write the array as
Range.Value = Array, a repetitive task which
should be automated in an ArrayToRange() routine.

…And maybe some ancillary work with writing the field names, but I’m ignoring that in a short answer.

The end result is that you run this code:


    ArrayToRange rngTarget, ArrayTranspose(rst.GetRows)

Transposing the array is trivial, but here it is anyway:


Public Function ArrayTranspose(InputArray As Variant) As Variant
Application.Volatile False

Dim arrOutput As Variant

Dim i As Long
Dim j As Long
Dim iMin As Long
Dim iMax As Long
Dim jMin As Long
Dim jMax As Long

iMin = LBound(InputArray, 1)
iMax = UBound(InputArray, 1)
jMin = LBound(InputArray, 2)
jMax = UBound(InputArray, 2)

ReDim arrOutput(jMin To jMax, iMin To iMax)

For i = iMin To iMax
    For j = jMin To jMax
        arrOutput(j, i) = InputArray(i, j)
    Next j
Next i

ArrayTranspose = arrOutput

End Function

…And ArrayToRange is trivial if you don’t add checks for array dimensions and preserving formulas in the target cells: the essential point is that you can write your data in a single ‘hit’ if the dimensions of the range exactly match the dimensions of the array:

Public Sub ArrayToRange(rngTarget As Excel.Range, InputArray As Variant)
' Write an array to an Excel range in a single 'hit' to the sheet
' InputArray should be a 2-Dimensional structure of the form Variant(Rows, Columns)

' The target range is resized automatically to the dimensions of the array, with
' the top left cell used as the start point.

' This subroutine saves repetitive coding for a common VBA and Excel task.

' Author: Nigel Heffernan  http://Excellerando.blogspot.com


On Error Resume Next

Dim rngOutput As Excel.Range

Dim iRowCount   As Long
Dim iColCount   As Long

iRowCount = UBound(InputArray, 1) - LBound(InputArray, 1)
iColCount = UBound(InputArray, 2) - LBound(InputArray, 2)

With rngTarget.Worksheet

    Set rngOutput = .Range(rngTarget.Cells(1, 1), _
                           rngTarget.Cells(iRowCount + 1, iColCount + 1))

    Application.EnableEvents = False


    rngOutput.Value2 = InputArray

    Application.EnableEvents = True

    Set rngTarget = rngOutput   ' resizes the range This is useful, most of the time

End With  '  rngTarget.Worksheet

End Sub

A note of caution: in older versions of Excel (Office 2000, if I recall) the array ‘write’ still truncated to 255 chars. This is no longer a problem; and if you’re still using XL2000, cells containing a string exceeding 255 chars are enough of a problem that you might be glad of the truncation.

Accepted Answer