CODEWITHSUNDEEP
vbaexcel
Hari
Hari

Reputation: 155

Get Range from Closed Excel FIle

I have been trying to get data from a closed excel file, and so far, no solution posted has worked to do so without opening the source workbooks. I have tried using the ExecuteExcel4Macro command, but cannot use that since I need the search functionality of VLOOKUP. Using VLOOKUP, however, has its own problems. I can't simply do something like:

cell.Value = "=VLOOKUP(<search item>, '<filepath>[<sourcename.xlsx>]<worksheet>'!<range>, <col>, FALSE)"

because I need to do some processing of the source data before it is entered into the cell.

I've managed to get an MWE where it opens the source workbooks and runs 

Application.VLookup(<search item>, <source range object>, <col>, FALSE)

which gets the data successfully, but I can't open the workbooks quietly in the background. One of the workbooks contains links, and a pop-up dialog appears, asking if I would like to update the links. I have attempted to suppress the dialog box with

Application.ScreenUpdate = FALSE
Application.EnableEvents = FALSE
Application.DisplayAlerts = FALSE
Workbooks.Open <source>
Set sourceRange = Workbooks(<sourcename>).Worksheets(<sheetname>).Range(<range>)

but I was not successful in suppressing the dialog.

VLOOKUP requires a range as the second argument, which must be a range. I have been opening the source files to get those ranges, but I am looking for a way to get those range objects without having to open the files, since it seems I can't open them quietly.

Upvotes: 2

Views: 3404

Answers (1)

user6432984
user6432984

Reputation:

Use the ADO when you need to get multiple values from a closed Excel file. Here is an example where I use the ADO to get a specific range from a closed Excel file. I:\stackoverflow\Employees.xlsx[Sheet1$A1:F21].
For a more detailed example check out my answer here: Error 91 (Object Not Set) When Finding Data in Closed Workbook

Sub ADOGetRange()

    Dim lastRow As Long, x As Long

    Const adOpenKeyset = 1
    Const adLockOptimistic = 3
    Dim conn
    Dim EmployeeData

    Set conn = CreateObject("ADODB.Connection")
    conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=I:\stackoverflow\Employees.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
    conn.Open

   ' On Error GoTo CloseConnection

    Set EmployeeData = CreateObject("ADODB.Recordset")

    With EmployeeData
        .ActiveConnection = conn
        .CursorType = adOpenKeyset
        .LockType = adLockOptimistic
        .Source = "Select * FROM [Sheet1$A1:F21]"
        .Open
   '     On Error GoTo CloseRecordset

    End With

    With Worksheets("Sheet1")
        lastRow = .range("A" & Rows.Count).End(xlUp).Row
        For x = 2 To lastRow

            EmployeeData.Filter = "id=" & Cells(x, 1)
            If Not (EmployeeData.BOF And EmployeeData.EOF) Then
                .Cells(x, 2) = EmployeeData.Fields("first_name")
                .Cells(x, 3) = EmployeeData.Fields("last_name")
                .Cells(x, 4) = EmployeeData.Fields("email")
                .Cells(x, 5) = EmployeeData.Fields("gender")
                .Cells(x, 6) = EmployeeData.Fields("ip_address")
            End If

        Next
    End With
CloseRecordset:

    EmployeeData.Close
    Set EmployeeData = Nothing

CloseConnection:
    conn.Close
    Set conn = Nothing
End Sub

Upvotes: 4

Related Questions