Thread: [Solved] Pulling Data From Excel
View Single Post
 
Old 12-06-2014, 08:33 AM
gmayor's Avatar
gmayor gmayor is offline Windows 7 64bit Office 2010 32bit
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

You don't need to open Excel to read a named range. The following function will put the named range in an array, and you can loop through the array to find your value

Code:
Option Explicit
Const strWorkbook As String = "C:\Path\WorkbookName.xlsx"        'The path of the workbook
Const strRange As String = "RangeName"

Private Function xlFillArray(strWorkbook As String, _
                             strRangeName As String) As Variant
Dim RS As Object
Dim CN As Object
Dim iRows As Long
    Set CN = CreateObject("ADODB.Connection")
    CN.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
                              "Data Source=" & strWorkbook & ";" & _
                              "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"

    Set RS = CreateObject("ADODB.Recordset")
    RS.Open "SELECT * FROM [" & strRangeName & "]", CN, 2, 1

    With RS
        .MoveLast
        iRows = .RecordCount
        .MoveFirst
    End With
    xlFillArray = RS.GetRows(iRows)
    If RS.State = 1 Then RS.Close
    Set RS = Nothing
    If CN.State = 1 Then CN.Close
    Set CN = Nothing
lbl_Exit:
    Exit Function
End Function
For example, using the data file from my Chart Merge add-in, this has a range called "MergeData"



You could then look for a name in the first column of the array, and report (say) the value associated with the name in the second column e.g. as follows. You would of course have to set the values of the workbook and the range name in the declarations above

Code:
Sub Test()
'Look for the value 'Al Gore' in the first column of the array
'And report the value in the second column
'Which here should report - 14132
Dim arr() As Variant
    arr = xlFillArray(strWorkbook, strRange)
    On Error GoTo lbl_Exit
    For iRows = 0 To UBound(arr, 1)
        sResult = arr(0, iRows)
        If sResult = "Al Gore" Then
            MsgBox arr(1, iRows)
        End If
    Next iRows
lbl_Exit:
    Exit Sub
End Sub
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote