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
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

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

ADVERTISEMENT

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!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,950
Messages
5,599,028
Members
414,275
Latest member
Pungie

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top