Originally Posted by
gmaxey
For a particular project I need to get the Column Headings and all of the associated column values. I've noticed that when I have a situation where the heading is text but the column contents is currency, the function returns NULL as the column heading.
<snip>
just wondering why it returns "Null" and if there is a way to have mixed format in the columns and still completely fill array (no Nulls).
OK, we can get you a mixed format in a single array (dates will be dates, currency types will remain currency types, headers will be strings etc.) cumbersome because it involves iteration but it seems quick nonetheless:
Sub test()
varExcelDataArray = modUtilities.fcnExcelDataToArray(ThisDocument.Path & "\Array Data.xlsx", "Data", , True)
End Sub
Function fcnExcelDataToArray(strWorkbook As String, _
Optional strRange As String = "Sheet1", _
Optional bIsSheet As Boolean = True, _
Optional bHeaderRow As Boolean = True) As Variant
Dim oRS As Object, oConn As Object
Dim lngRows As Long
Dim strHeaderYES_NO As String
Dim y()
strHeaderYES_NO = "YES"
If Not bHeaderRow Then strHeaderYES_NO = "NO"
If bIsSheet Then strRange = strRange & "$]" Else strRange = strRange & "]"
Set oConn = CreateObject("ADODB.Connection")
oConn.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & strWorkbook & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=" & strHeaderYES_NO & """;"
Set oRS = CreateObject("ADODB.Recordset")
oRS.Open "SELECT * FROM [" & strRange, oConn, 2, 1
With oRS
.MoveLast
lngRows = .RecordCount
.MoveFirst
x = .GetRows(lngRows)
ReDim y(0 To UBound(x), 0 To UBound(x, 2) + 1)
For i = 0 To .Fields.Count - 1
y(i, 0) = .Fields(i).Name
Next i
For i = 0 To UBound(y)
For j = 1 To UBound(y, 2)
y(i, j) = x(i, j - 1)
Next j
Next i
End With
fcnExcelDataToArray = y
lbl_Exit:
If oRS.State = 1 Then oRS.Close
Set oRS = Nothing
If oConn.State = 1 Then oConn.Close
Set oConn = Nothing
Exit Function
End Function
2019-06-21_182329.jpg