Force Array to be String Type

[Veritan]

Hello, I am trying to get an array to treat all results from a SQL query into a closed Excel workbook to be of String type. The code below works just fine as long as I declare the array as a variant (I've explicitly set a reference to the Microsoft ActiveX Data Objects 6.1 Library). The reason I want all the data in it to be strings is that while there are numbers in the workbook I am querying, I am not doing any math with them and have no problem with them being treated as strings. One of the columns has data that consists of small integer numbers (< 30) as well as some text-based notes, all in the same column. I know this is not great table design, and I was able to get everything to work perfectly when I moved the text data to a column that had only string-type data. However, my boss doesn't really want to change the design of the table, so I'm trying to find some sort of work-around.

When I run the code below, the array that ends up being generated is a 2 dimensional array with dimensions of (0 To 9, 0 To 38). However, the dimensions are generated dynamically by the GetRows method so the values are not fixed in place. The element (3,0) is a number and so the array sets that dimension to be type Double. Which is fine for the numbers, but at (3,25) the data goes from numeric to text, and instead of getting the text stored in the array, it simply returns a Null value. Is there a way to force the array to assign String instead of Double to that dimension? I tried declaring the array As String, but this ended up failing with a Type Mismatch error.

Option Explicit

Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1


Sub GetArray()
    Dim arrMyArray() As Variant, oRS As Object
    Dim sFileName As String, sConnect As String, sSQL As String
    
    sFileName = "C:\Users\my.name\Desktop\Test.xlsm"
    sConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=" & sFileName & ";" & _
        "Extended Properties=""Excel 12.0 Macro;HDR=YES"";"
    sSQL = "SELECT * FROM [Sheet1$]"
    
    Set oRS = CreateObject("ADODB.Recordset")
    oRS.Open sSQL, sConnect, adOpenForwardOnly, adLockReadOnly, adCmdText
    
    arrMyArray = oRS.GetRows
    
    oRS.Close
    Set oRS = Nothing
End Sub

 

[Norie]

Why not just open the workbook, populate the array and then close it?

Workbooks.Open C:\Users\my.name\Desktop\Test.xlsm

arrMyArray = ActiveWorkbook.Sheets("Sheet1").Range("A1").CurrentRegion.Value

ActiveWorkbook.Close False

 

[Veritan]

Thought I was being clever and saving time by using SQL and connecting to it with an ADODB model Thanks for the input, my concern was that opening the actual file would take longer than I wanted it to and so I didn't want to open it if I could. But I ran your code and it populated in less than a second without any obvious screen flashing (I turned off screen updating). Thanks for the solution!

 

[Norie]

No problem.

There's nothing wrong with using ADO for this sort of thing but when you have columns/fields that have a 'mixed' data type then it can cause problems.

With such a column/field ADO will try to 'guess' the data type based on the first few rows, if it guesses incorrectly it can lead to data loss - I think that's what your problem was.

 

[Veritan]

Norie, just an FYI on something I found while trolling the internet. Apparently, it is possible to force a particular dimension to be a certain data type. What I did was in my closed workbook (the one with the data I was trying to get into the array), I went into the column that was causing the problems and changed them all from General to Text. Then I ran my original code to see what would happen. This ended up actually forcing the dimension into a String data type, which then happily recognized all elements in that column as Strings with no Null values. I'll still likely use your code since it's shorter and works just as well, but I thought I'd let you know about this particular little gimmick. Thanks again for your help!