Query a closed workbook without opening it

Question

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

The following code gives a type mismatch error on 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).

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

Range.Find() returns a Range (not a string), Try Range.Find().Address. — NickSlash
Thanks, now I don't get an error but the computation is incorrect. It keeps finding the wrong values — Mark Kennedy
You can't use that approach to search for a string in a closed workbook. Why not open the workbook and use Find() directly? — Tim Williams
Turn off ScreenUpdating, open the workbook, do the search, close the workbook, turn on ScreenUpdating. — Tim Williams

NickSlash NickSlash · Accepted Answer · 2013-08-06T08:34:34

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

Query a closed workbook without opening it

1 Answers