问题描述
我一直在尝试从关闭的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文件获取范围的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!