Consulting

Results 1 to 12 of 12

Thread: Heading Row Returns NULL

  1. #1
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location

    Heading Row Returns NULL

    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.

    Excel.jpg


    Array.jpg

    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.
    Greg

    Visit my website: http://gregmaxey.com

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    I don't get the headers Greg, even with HDR=YES. I have Office 2016 ProPlus, what version of Office are you using?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location
    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.
    Greg

    Visit my website: http://gregmaxey.com

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #7
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location
    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.
    Greg

    Visit my website: http://gregmaxey.com

  8. #8
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  9. #9
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location
    p45cal,

    I see where that could come in handy. Thanks.
    Greg

    Visit my website: http://gregmaxey.com

  10. #10
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Quote Originally Posted by p45cal View Post
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  11. #11
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Quote Originally Posted by gmaxey View Post
    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
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  12. #12
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by gmaxey View Post
    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
    Last edited by p45cal; 06-21-2019 at 10:26 AM.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •