Unable to transpose array

[JackDanIce]

Hi,

I have the following code which I'm testing to read data into an array via ADO:

Sub ExistingCode()

Dim filepath as string: filepath = "D:\"
Dim filename as string: filename = "Test.csv"

'ADO test
Dim arr()   As Variant
Dim arr2    As Variant

arr = f_ADO_Out(filepath, filename)
ReDim arr2(LBound(arr, 2) To UBound(arr, 2), LBound(arr, 1) To UBound(arr, 1))
arr2 = Application.Transpose(arr)

End Sub

Line in bold has error "Run-time error '13': Type mismatch"

f_ADO_Out is a public function that reads data into an array

Public Function f_ADO_Out(ByRef strPath As String, ByRef strName As String) As Variant
    
    Dim objConnection       As Object
    Dim objRecordset        As Object
    
    Dim sql                 As String
       
    Const adOpenStatic      As Long = 3
    Const adLockOptimistic  As Long = 3
    
    Const adCmdText         As Long = &H1
       
    Set objConnection = CreateObject("ADODB.Connection")
    Set objRecordset = CreateObject("ADODB.Recordset")
        
    'Create connection to source file
    objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & strPath & ";" & "Extended Properties=""text;HDR=No;FMT=Delimited;"""
    
    'SQL query to set up a "table of data" to read into Excel
    sql = "Select * FROM [@1]"  'sql = "Select * FROM [" & strName & "] "
    
    objRecordset.Open Replace(sql, "@1", strName), objConnection, adOpenStatic, adLockOptimistic, adCmdText
    f_ADO_Out = objRecordset.GetRows
    
    Set objConnection = Nothing
    Set objRecordset = Nothing
    
End Function

Realised the output from the function is transposed and because this is something I'm bolting into existing code, I prefer to transpose before using the data.

I'm passing valid arguments into the function and do not believe the inputs are the problem. I also tried to transpose within the function but I get the same error; thoughts on how to fix please?

TIA,
Jack

 

[shg]

Don't redim arr2 before the assignment. Or don't use it at all:

  Const filepath    As String = "D:\"
  Const filename    As String = "Test.csv"
  Dim arr           As Variant

  arr = WorksheetFunction.Transpose(f_ADO_Out(filepath, filename))

The max dimension of the array is limited to 64K, BTW.

 

[Rick Rothstein]

Hi,

I have the following code which I'm testing to read data into an array via ADO:

Sub ExistingCode()

Dim filepath as string: filepath = "D:\"
Dim filename as string: filename = "Test.csv"

'ADO test
Dim arr()   As Variant
Dim arr2    As Variant

arr = f_ADO_Out(filepath, filename)
MsgBox UBound(arr, 1)
MsgBox UBound(arr, 2)
ReDim arr2(LBound(arr, 2) To UBound(arr, 2), LBound(arr, 1) To UBound(arr, 1))
arr2 = Application.Transpose(arr)

End Sub

Line in bold has error "Run-time error '13': Type mismatch"

f_ADO_Out is a public function that reads data into an array

My guess is that error is saying the f_ADO_Out is not a two-dimensional array. I don't know ADO or SQL, so the only suggestion I can give is to put the two lines of code I show in red above and see if, in fact, you are getting the two-dimensional array you think you are. If my guess is correct, the first MessageBox will output the number of rows of data but the second MessageBox will generate an error.

 

[JackDanIce]

Hi shg and Rick

@shg usually wouldn't use a second variant arr2, but trying to decode this issue is reason why - tried to set 1 variable = transpose of another.
This works:

arr = f_ADO_Out(filepath, filename)

except it returns the data not transposed in the way I would like.
The number of rows being returned (from a csv file, generated externally) is around 5k.

@Rick the locals window shows the "function" before it passes to the array is of size 0 to 1 and 0 to 4579 so it does return 2 dimensions and the suggested msgboxes confirm same dimensions. I am now wondering if I need to explicitly state Base 1 to resolve this.. about to try and will reply back.

Using Option Base 1 doesn't resolve issue, still can't seem to transpose the data.

 

[GTO]

Hi There,

I am not exactly sure what you are wanting, as shg's would seem to be what you wanted?

You will not need Option Base 1 if you Transpose the data, as Transpose returns a 1-based array.

Maybe this example would help?

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br>  <br><SPAN style="color:#00007F">Sub</SPAN> example()<br><SPAN style="color:#00007F">Dim</SPAN> arr<br>  <br>  arr = MyFunction<br>  Stop<br>  <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>  <br>Function MyFunction() <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> ary(0 <SPAN style="color:#00007F">To</SPAN> 1, 0 <SPAN style="color:#00007F">To</SPAN> 5000)<br><SPAN style="color:#00007F">Dim</SPAN> n <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>  <br>  <SPAN style="color:#00007F">For</SPAN> n = 0 <SPAN style="color:#00007F">To</SPAN> 5000<br>    ary(0, n) = n + 1<br>    ary(1, n) = (n + 1) * 20<br>  <SPAN style="color:#00007F">Next</SPAN><br>  <br>  My<SPAN style="color:#00007F">Function</SPAN> = Application.Transpose(ary)<br>  <br><SPAN style="color:#00007F">End</SPAN> Function</FONT>

Have your Locals window showing and you can see the returned arr().

If by chance you want to Transpose inside the function, not tested, but I think f_ADO_Out = Application.Transpose(objRecordset.GetRows) should work. I am not sure (VERY limited tries with ADO), but I think you want to close the recordset after that line.

Hope that helps,

Mark

 

[xenou]

Your original code works for me (Post #1), although curiously it takes my (0 to 1, 0 to 4) array and makes it (1 to 5, 1 to 2).

Edit: not using Option Base 1 in my testing - with a simple text file for input having product codes in Column1 and Quantities in Column 2. ADO is pretty good about cleaning up after itself, so although I do usually close things I open and set them to nothing, that's not strictly necessary.

Also, BTW, if you are reading from text files from Excel, you can also just open the text files with Excel and not use ADO.

 

[JackDanIce]

Thank you GTO and xenou

To both:

It's some legacy code that I'm trying to update at work. The existing code generates a Unix script (which I manually run in separate system) which in turn generates a .csv file and data is imported back into the Main workboook.

The existing code opens the file, reads the data contents into a variant, closes csv file and then prints to the Main Workbook before further processing.

The contents of the csv file, aside from the header, are alphanumeric characters in column A (security ISINs for specifics) and mixed number values (doubles) in column B.

In the past, I've opened a file, had a function create an array using it's contents and output that back to a variable that called the function. The only difference I can tell is I'm trying to use ADO here instead of Workbooks.Open, partially for speed gains and partially to learn a new thing with VBA (SQL is also on my to learn list)

(I found same xenou, Option Base 1 didn't seem to affect it). Need to release some intra-day prices right now, will come back to this.

The code posted above is a summary of what I actually have but the parts that I've changed - I have been testing in a separate module, using local variables only and that's the error message generated. Which is also odd it works ok for xenou.

 

[xenou]

You could roll your own transpose function which would only take a few minutes and would possibly provide some insight (if it fails) or more confusion (if it works).

I'm always paranoid when it comes to alphanumeric data with ADO. ADO is by nature geared to work with typed data, but if it comes across data it thinks is numeric (1, 2, 3 ...) and then hits an alphanumeric entry (1, 2, 3, A, ...) it can be troublesome.

 

[JackDanIce]

My next approaches were either: use a temporary sheet and just read and print the data to there (i.e. not use ADO for this) or as you suggest, write my own Transpose function.

Going to investigate further and revert.

 

[JackDanIce]

@GTO Have the following inside the function and now receive a run-time error '13' type mismatch on bold line:

With objRecordset
    .Open sql, objConnection, adOpenStatic, adLockOptimistic, adCmdText
    ' f_ADO_Out = .GetRows
     arr = Application.Transpose(.GetRows)
End With

The commented out line above the bold works error free but then I need to transpose the data. Going to try writing my own transpose function now