本文介绍了从封闭的Excel文件获取范围的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我一直在尝试从关闭的excel文件中获取数据,到目前为止,没有打开源工作簿的任何解决方案都无法做到这一点.我尝试使用ExecuteExcel4Macro命令,但是由于我需要VLOOKUP的搜索功能,因此无法使用它.但是,使用VLOOKUP有其自身的问题.我不能简单地做类似的事情:

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.

我设法获得了一个MWE,它可以在其中打开源工作簿并运行

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

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

可以成功获取数据,但是我不能在后台安静地打开工作簿.其中一个工作簿包含链接,然后出现一个弹出对话框,询问我是否要更新链接.我试图用//p>取消显示对话框.

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需要一个范围作为第二个参数,该范围必须是一个范围.我一直在打开源文件来获取这些范围,但是我正在寻找一种无需打开文件即可获取那些范围对象的方法,因为看来我无法安静地打开它们.

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.

推荐答案

当您需要从封闭的Excel文件中获取多个值时,请使用ADO.这是一个示例,其中我使用ADO从关闭的Excel文件中获取特定范围. I:\stackoverflow\Employees.xlsx[Sheet1$A1:F21].
有关更详细的示例,请在此处查看我的答案:

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

这篇关于从封闭的Excel文件获取范围的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

05-28 22:50
查看更多