Finding from a closed workbook

meatcan

New Member
Joined
Jun 19, 2012
Messages
14
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\

Code:
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

Well-known Member
Joined
Dec 24, 2011
Messages
1,218
I don't think you can define range in a closed workbook. bookname should be an opened workbook.
 

meatcan

New Member
Joined
Jun 19, 2012
Messages
14
Would it be possible to change the Workbooks(bookname) part into something with a directory, eg, C:\Folder\"bookname"?
 

Mohammad Basem

Well-known Member
Joined
Dec 24, 2011
Messages
1,218
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

New Member
Joined
Jun 19, 2012
Messages
14
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

Well-known Member
Joined
Dec 24, 2011
Messages
1,218
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

New Member
Joined
Jun 19, 2012
Messages
14
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

Well-known Member
Joined
Dec 24, 2011
Messages
1,218
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!
 

Forum statistics

Threads
1,085,850
Messages
5,386,342
Members
401,996
Latest member
mg07p929

Some videos you may like

This Week's Hot Topics

Top