Consulting

Results 1 to 5 of 5

Thread: Populate Array from a range in a closed workbook in vba

  1. #1

    Populate Array from a range in a closed workbook in vba

    Hi,

    i need help on populating an array in one of my subroutine from a range from a closed work book.

    the workbook is in c:\drive under a folder.

    is it possible to get the desired range say A1:A30 to an array?

    Thanks for any help in advance
    Raj

  2. #2
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    Here is an ADO method that you can modify.
    https://www.exceltip.com/st/Fill_a_L...Excel/410.html

  3. #3

    Populate Array from closed workbook

    Dear Hob,
    The link is not working. My code is here, pls help

    [VBA]
    Sub HideUnwantedEngrs(ByVal fName As String)
    Dim pvtItem As PivotItem
    Dim pvtField_ENGR As PivotField
    ' ArrayEngr = Workbooks("c:\data_analysis\Reference Table.xls").Sheets("Engr MAP").Range("A1:A30")
    Set pvtField_ENGR = Worksheets("Pivot Table").PivotTables("INVENTORY-BLR").PivotFields("ENGR NO")

    For Each pvtItem In pvtField_ENGR.PivotItems
    Select Case pvtItem.Caption

    Case "1783", "1790", "1801", "1807", "1856", "1857", "1874", "1888", _
    "1897", "1898", "KA82", "KB75", "KB76", "KB84", "KB81", "KA81", "KA82", "KB83", "XB09"
    pvtItem.Visible = True
    Case Else
    pvtItem.Visible = False
    End Select
    Next

    Call savinv(fName)
    End Sub[/VBA]

    Raj

  4. #4
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    This is similar to what that link contained.
    [VBA]Function ReadDataFromWorkbook(SourceFile As String, _
    SourceRange As String) As Variant
    ' requires a reference to the Microsoft ActiveX Data Objects library
    ' (menu Tools, References in the VBE)
    ' if SourceRange is a range reference:
    ' this function can only return data from the first worksheet in SourceFile
    ' if SourceRange is a defined name reference:
    ' this function can return data from any worksheet in SourceFile
    ' SourceRange must include the range headers
    ' examples:
    ' varRecordSetData = _
    ReadDataFromWorkbook("C:\FolderName\SourceWbName.xls", "A1:A21")
    ' varRecordSetData = _
    ReadDataFromWorkbook("C:\FolderName\SourceWbName.xls", "A1:B21")
    ' varRecordSetData = _
    ReadDataFromWorkbook("C:\FolderName\SourceWbName.xls", "DefinedRangeName")
    Dim dbConnection As ADODB.Connection, rs As ADODB.Recordset
    Dim dbConnectionString As String
    dbConnectionString = _
    "DRIVER={Microsoft Excel Driver (*.xls)};ReadOnly=1;DBQ=" & SourceFile
    Set dbConnection = New ADODB.Connection
    On Error GoTo InvalidInput
    dbConnection.Open dbConnectionString ' open the database connection
    Set rs = dbConnection.Execute("[" & SourceRange & "]")
    On Error GoTo 0
    ReadDataFromWorkbook = rs.GetRows
    ' returns a two dim array with all records in rs
    rs.Close
    dbConnection.Close ' close the database connection

    Set rs = Nothing
    Set dbConnection = Nothing
    On Error GoTo 0
    Exit Function
    InvalidInput:
    MsgBox "The source file or source range is invalid!", _
    vbExclamation, "Get data from closed workbook"
    Set rs = Nothing
    Set dbConnection = Nothing
    End Function
    [/VBA]

  5. #5

    Populate Array from closed workbook

    Sorry, i'm not a programmer. could you pls give me how to call this function
    in my code.

    I want to get engr numbers which are in A2:A30(may be expanding when new engr added) in Sheet Engr MAP in C:\data_analysis\reference table.xls to an array.



    Raj

Posting Permissions

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