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 :hi:

Here is an ADO method that you can modify.
https://www.exceltip.com/st/Fill_a_L...Excel/410.html

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 :banghead:

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]

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.

: pray2:

Raj