PDA

View Full Version : Heading Row Returns NULL



gmaxey
06-20-2019, 08:55 AM
Hi,

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


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.

24447


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.

Bob Phillips
06-20-2019, 03:39 PM
I don't get the headers Greg, even with HDR=YES. I have Office 2016 ProPlus, what version of Office are you using?

gmaxey
06-20-2019, 06:23 PM
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.

Bob Phillips
06-21-2019, 03:46 AM
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.

Bob Phillips
06-21-2019, 04:11 AM
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.

Bob Phillips
06-21-2019, 04:16 AM
I say all that, and then I try again and this seems to work



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

gmaxey
06-21-2019, 04:56 AM
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.

p45cal
06-21-2019, 05:06 AM
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:
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.

gmaxey
06-21-2019, 05:14 AM
p45cal,

I see where that could come in handy. Thanks.

Bob Phillips
06-21-2019, 08:12 AM
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.

Bob Phillips
06-21-2019, 08:18 AM
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

p45cal
06-21-2019, 10:13 AM
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


24464