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

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:

  1. Populate a VBA array variant with the data using
  2. Transpose the array, because GetRows is the wrong way ’round for
  3. 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.


solved SQL Query output in VBA is different than in SQL Oracle