vba 查询关闭的工作簿而不打开它
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18069445/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Query a closed workbook without opening it
提问by Mark Kennedy
I'm searching for a string within a column of a closed Excel workbook.
我正在关闭的 Excel 工作簿的列中搜索字符串。
The following code gives a type mismatch error on MsgBox.
下面的代码给出了 MsgBox 上的类型不匹配错误。
If I replace that line with ret = "'" & wbPath & "[" & wbName & "]" & wsName & "'!" & Range("C3015").Address(True, True, -4150)then the macro gives me a hard-coded value (in this case, the value at cell C3015).
如果我替换该行，ret = "'" & wbPath & "[" & wbName & "]" & wsName & "'!" & Range("C3015").Address(True, True, -4150)则宏会为我提供一个硬编码值（在本例中为单元格 C3015 中的值）。
How can I search for other values within columns of closed workbooks, without opening them?
如何在不打开的情况下搜索已关闭工作簿列中的其他值？
Dim wbName As String, wbPath As String, wsName As String
wbPath = "Path\To\Workbook\"
wbName = "NameOfWorkbook.xlsb"
wsName = "NameOfWorkSheet"
Dim ret As String
ret = "'" & wbPath & "[" & wbName & "]" & wsName & "'!" & Range("D:D").Find(What:="SearchColumnDForThisString")
MsgBox ExecuteExcel4Macro(ret) // <--------- TYPE MISMATCH ERROR
回答by NickSlash
If your using the book in more than one macro you may want to leave the workbook open, you can do something like the following to open and hide it. You could also set the workbook to a public variable so that you can close it when your done.
如果您在多个宏中使用这本书，您可能希望让工作簿保持打开状态，您可以执行以下操作来打开和隐藏它。您还可以将工作簿设置为公共变量，以便在完成后关闭它。
Dim Wn as Window
Dim Wb as Workbook
Application.ScreenUpdating = False
Set Wb = Application.Workbooks.Open("your book")
For Each Wn in Wb
Wn.Visible = False
Next Wn
Application.ScreenUpdating = True
