Heading Row Returns NULL

Hi,

I use the following function in Word to return a variant array from Excel:

Code:

---

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
  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
  End With
  fcnExcelDataToArray = oRS.GetRows(lngRows)
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

---

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.

Attachment 24447


Attachment 24448

I know that I can just format by column D as General or Text and then "Price" will appear in the array, 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). Thanks.

I don't get the headers Greg, even with HDR=YES. I have Office 2016 ProPlus, what version of Office are you using?

xld,

When I call that function and want to include the header row with the returned results I do it something like this:

varExcelDataArray = modUtilities.fcnExcelDataToArray(ThisDocument.Path & "\Array Data.xlsx", "Data", , False)

So the optional bHeaderRow parameter is "False"

It works here with Office 2010 and Office 2016.

Thanks.

Okay, I see it now, I was mis-interpeting what the HDR argument did.

I seem to recall problems with the currency type in VBA in the past, but I am afraid I cannot recall what the problem was. Reading the fields separately doesn't work, it also shows Null.

As you may know, when reading an Excel file, the OLEDB/Jet engine looks at the first 8 row to determine the datatype. Any data that is not of this datatype is pulled in as a null. The usual way to get around this is to add IMEX=1 in the Extended Properties of the connection string. Unfortunately, when you do this, HDR seems to be ignored, and so you just get the data range.

I say all that, and then I try again and this seems to work

Code:

---

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 conn As String
Dim lngRows As Long
Dim strHeaderYES_NO As String
Dim i As Long

    strHeaderYES_NO = "YES"
    If Not bHeaderRow Then strHeaderYES_NO = "NO"
    If bIsSheet Then strRange = strRange & "$]" Else strRange = strRange & "]"
    Set oConn = CreateObject("ADODB.Connection")
    conn = "Provider=Microsoft.ACE.OLEDB.16.0;" & _
          "Data Source=" & strWorkbook & ";" & _
          "Extended Properties=""Excel 12.0 Xml;HDR=" & strHeaderYES_NO & ";IMEX=1"";"
    oConn.Open ConnectionString:=conn
    Set oRS = CreateObject("ADODB.Recordset")
    oRS.Open "SELECT * FROM [" & strRange, oConn, 2, 1, &H1
    With oRS
   
        .MoveLast
        lngRows = .RecordCount
        .MoveFirst
   
        fcnExcelDataToArray = .GetRows(lngRows)
    End With
   
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

---

xld,

Yes, that does seem to work (after I changed Microsoft.ACE.OLEDB.16.0 back to 12) :-( Seems alll of the data is now brought is as a string which is fine for my purposes. Thanks!
When I loaded Windows 10 and all my Office Applications on my new PC in January,
none of my previous projects using OLEDB.16.0 worked (Provider could not be found error)

I tried downloading and installing the provider then nothing worked and I had to restore the PC from an image.

You have any tips on you to download, install and register the 16.0 provider?

Thanks again.

xld beat me to it.
Note first that I don't know what I'm talking about. The headers seem to be stored in the oRS.Fields.
If you call the function with:
varExcelDataArray = modUtilities.fcnExcelDataToArray(ThisDocument.Path & "\Array Data.xlsx", "Data")
or
varExcelDataArray = modUtilities.fcnExcelDataToArray(ThisDocument.Path & "\Array Data.xlsx", "Data", , True)
having tweaked the function along the lines of:

Code:

---

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 Headers() As String
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
  ReDim Headers(.Fields.Count - 1)
  For i = 0 To .Fields.Count - 1
    Headers(i) = .Fields(i).Name
  Next i
  fcnExcelDataToArray = Array(Headers, .GetRows(lngRows))
End With

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

---

You get an array of 2 arrays, the first being the header strings, the second being the data, with the 4th column type being retained as Currency.

p45cal,

I see where that could come in handy. Thanks.

Quote:

---

Originally Posted by p45cal

Note first that I don't know what I'm talking about. The headers seem to be stored in the oRS.Fields.

---

Pascal,
It is true that Fields contain the headers, but as I understand the HDR=No property means that the connection assumes that the dataset does not have headers, so the headers are treated as part of the data range. There is no need to iterate Fields as you already have them. I looked at Fields to see if that was of any help, but before I added IMEX, they had the same values as the array.

Quote:

---

Originally Posted by gmaxey

xld,

Yes, that does seem to work (after I changed Microsoft.ACE.OLEDB.16.0 back to 12)

---

Glad we got there in the end Greg.

BTW, xld is the moniker I used when I first started contributing to newsgroups and forums, it seemed then that anonymity was the way to go, now I am happy to be in the open. In real life I am Bob Phillips, you may remember me from a few years ago when myself and Ron de Bruin helped you with getting to grips with the ribbon when you first started with it, indeed I do believe that I get a credit on your web pages on the ribbon :yes

Quote:

---

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:

Code:

---

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

---

Attachment 24464