查询已关闭的工作簿而不打开它 [英] Query a closed workbook without opening it

问题描述

我正在关闭的Excel工作簿的一列中搜索字符串.

I'm searching for a string within a column of a closed Excel workbook.

以下代码在MsgBox上给出类型不匹配错误.

The following code gives a type mismatch error on MsgBox.

如果我用ret = "'" & wbPath & "[" & wbName & "]" & wsName & "'!" & Range("C3015").Address(True, True, -4150)替换该行，则宏会给我一个硬编码的值(在这种情况下，是单元格C3015的值).

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).

如何在不打开的工作簿的列中搜索其他值?

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

推荐答案

如果您在多个宏中使用本书，则可能希望打开工作簿，则可以执行以下操作来打开和隐藏它.您还可以将工作簿设置为公共变量，以便在完成后将其关闭.

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

这篇关于查询已关闭的工作簿而不打开它的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！