Finding from a closed workbook

[meatcan]

Hi all,

I am writing a VBA code to find a string from a workbook and return the number from the cell below it.

The problem is that my workbook, worksheet names and the string to find are all variables and i can't open all the workbooks while running the code since I have too many workbooks here.

How do I define my search range? Lets assume all my workbooks are in C:\Folder\

Dim rngSearch As Range, rngFound As Range
Set rngSearch = Workbooks(bookname).Worksheets(sheetname).Range("B:M")
Set rngFound = rngSearch.Find(What:=mystring, LookIn:=xlValues, LookAt:=xlPart)
If rngFound Is Nothing Then
MsgBox "Not found"
Else
n = Workbooks(bookname).Worksheets(sheetname).Cells(rngFound.Row + 1, rngFound.Column)
End If

 

[Mohammad Basem]

I don't think you can define range in a closed workbook. bookname should be an opened workbook.

 

[meatcan]

Would it be possible to change the Workbooks(bookname) part into something with a directory, eg, C:\Folder\"bookname"?

 

[Mohammad Basem]

I'm afraid no. It generates error.

Are you looking here for a workbook that contains certain text but you have no idea which one it is?

 

[meatcan]

I know which workbook and which worksheet I need and I would define the name at each loop. Its just that I need to find the string in with the workbook closed, since there are too many different workbooks to look into.

 

[Mohammad Basem]

This make things easier. How many files are you talking about?

Why don't you give a try and measure the time required to finish the job?

 

[meatcan]

I have about 65 workbooks with 30+ worksheets in each of them. Opening all the workbooks before running the macro would kill my system. I think I may try Workbooks.Open and close. Thanks anyway

 

[Mohammad Basem]

I thought you were going to tell me they are hundreds

You can start a loop, open one workbook at a time, process it then close it, until you process all of them.

Good luck!