Search all workbooks in a folder
Author: Oscar Cronquist Article last updated on February 22, 2023
Today I'll show you how to search all Excel workbooks with file extensions xls, xlsx and xlsm in a given folder for a specific string.
What's on this page
You can search workbooks with other file extensions as well, I will demonstrate what to change in the macro, later in this post.
The macro creates a new worksheet containing the search result. Each result contains a link to a cell where the search string was found.
You can then press with left mouse button on the link and Excel opens the corresponding workbook and locates the cell containing the search string.
There are also macros demonstrated here that allow you to search for multiple values and return a corresponding on the same row.
The following macro asks for a folder to search in and the search string.
Search workbooks in folder for a single string
'Name macro
Sub SearchWKBooks()
'Dimension variables and declare data types
Dim WS As Worksheet
Dim myfolder As String
Dim Str As String
Dim a As Single
Dim sht As Worksheet
'Create a new worksheet and svae it to object named WS
Set WS = Sheets.Add
'Show dialog box and ask for a folder
With Application.FileDialog(msoFileDialogFolderPicker)
.Show
'Save selected folder path to variable myfolder
myfolder = .SelectedItems(1) & "\"
End With
'Show inputbox and ask for search string to use
Str = Application.InputBox(prompt:="Search string:", Title:="Search all workbooks in a folder", Type:=2)
'Stop macro if string is nothing
If Str = "" Then Exit Sub
'Save text "Search string:" to cell A1 on worksheet WS
WS.Range("A1") = "Search string:"
'Save text in variable Str to cell B1 on worksheet WS
WS.Range("B1") = Str
'Save text "Path:" to cell A2 on worksheet WS
WS.Range("A2") = "Path:"
'Save text in variable myfolder to cell B2 on worksheet WS
WS.Range("B2") = myfolder
'Save text "Workbook" to cell A3 on worksheet WS
WS.Range("A3") = "Workbook"
'Save text "Worksheet" to cell B3 on worksheet WS
WS.Range("B3") = "Worksheet"
'Save text "Cell Address" to cell C3 on worksheet WS
WS.Range("C3") = "Cell Address"
'Save text "Link" to cell D3 on worksheet WS
WS.Range("D3") = "Link"
'Save 0 (zero) to variable a
a = 0
' The DIR function returns the first filename that matches the pathname specified in variable myfolder and save it to variable Value
Value = Dir(myfolder)
'Keep iterating until variable Value is nothing
Do Until Value = ""
'Check if Value is . or ..
If Value = "." Or Value = ".." Then
'If Value is not . or .. run these lines of code
Else
'Check If file names last three characters match xls or four last characters match xlsx or xlsm
If Right(Value, 3) = "xls" Or Right(Value, 4) = "xlsx" Or Right(Value, 4) = "xlsm" Then
'Enable error handling
On Error Resume Next
'Open workbook based on path in variable folder and file name in variable Value using password zzzzzzzzzzzz
Workbooks.Open Filename:=myfolder & Value, Password:="zzzzzzzzzzzz"
'Check if an error has occured
If Err.Number > 0 Then
'Save path to column A based on counter variable a
WS.Range("A4").Offset(a, 0).Value = Value
'Save text "Password protected" to column B based on counter variable a
WS.Range("B4").Offset(a, 0).Value = "Password protected"
'Add 1 to the number stored in variable a
a = a + 1
'Continue here if no error is returned
Else
'Disable error handling
On Error GoTo 0
'Go through all worksheets in active workbook
For Each sht In ActiveWorkbook.Worksheets
'Seach worksheet based on value in variable Str and save result to object c
Set c = sht.Cells.Find(Str, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)
'Check if object c is not nothing
If Not c Is Nothing Then
'Save address based on range object c to variable firstAddress
firstAddress = c.Address
'Repet following lines between Do and Loop
Do
'Save file name to column A and row number based on variable a
WS.Range("A4").Offset(a, 0).Value = Value
'Save worksheet name to column B and row number based on variable a
WS.Range("B4").Offset(a, 0).Value = sht.Name
'Save cell address to column C and row number based on variable a
WS.Range("C4").Offset(a, 0).Value = c.Address
'Create hyperlink pointing to file name , worksheet name and cell address, show hyperlink text "Link"
WS.Hyperlinks.Add Anchor:=WS.Range("D4").Offset(a, 0), Address:=myfolder & Value, SubAddress:= _
sht.Name & "!" & c.Address, TextToDisplay:="Link"
'Increase count variable a with 1
a = a + 1
'Find next cell that matches and save to object c
Set c = sht.Cells.FindNext(c)
'Keep iterating until c is nothing and c is not equal to the first found cell in the same worksheet
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
'Continue with next worksheet
Next sht
End If
'Close workbook and do not save any changes made
Workbooks(Value).Close False
'Diable error handling
On Error GoTo 0
End If
End If
'Find next file
Value = Dir
'Keep iterating
Loop
'Change cell widths to fit text
Cells.EntireColumn.AutoFit
End Sub
Explaining the macro
The macro loops through all files in the given folder and if the file extension matches xls, xlsx or xlsm the workbook is opened.
If the workbook is password protected the macro outputs "Password protected" for that workbook.
If it successfully opens a workbook another loop is started that searches each worksheet in the workbook for the given search string.
Where to put the code?
- Copy macro (CTRL + c)
- Go to the VB Editor (Alt + F11)
- Press with left mouse button on "Insert" on the top menu.
- Press with left mouse button on "Module" to insert a code module to your workbook.
- Paste code to the module. (CTRL + v)
- Return to Excel.
- Save your workbook as a macro-enabled workbook (*.xlsm file).
If you don't the macro will be gone the next time you open the same workbook.
Example
Example, I have 5 workbooks in folder c:\temp\ containing many random numbers. The macro asks for the path and the search string.
It then creates this sheet.
Press with left mouse button on a link and excel loads the corresponding workbook and navigates to the specific cell.
Search other file extensions
Change the following line to whatever file extensions you want to look for.
If Right(Value, 3) = "xls" Or Right(Value, 4) = "xlsx" Or Right(Value, 4) = "xlsm" Then
Search multiple strings in all workbooks in a folder
The following macro allows you to search all workbooks in a folder for multiple search strings.
'Name macro
Sub SearchWKBooks()
'Dimension variables and declare their data types
Dim WS As Worksheet
Dim myfolder As String
Dim Str As String
Dim a As Single
Dim sht As Worksheet
Dim RNG As Range
'Enable error handling
On Error Resume Next
'Show dialog box and ask for a cell range
Set RNG = Application.InputBox(Prompt:="Select a cell range containing search strings" _
, Title:="Select a range", Default:=ActiveCell.Address, Type:=8)
'Disable error handling
On Error GoTo 0
'Show dialog box and ask for a folder
With Application.FileDialog(msoFileDialogFolderPicker)
.Show
myfolder = .SelectedItems(1) & "\"
End With
'Add worksheet
Set WS = Sheets.Add
'Write "Path:" to cell A2
WS.Range("A2") = "Path:"
'Write contents of variable myfolder to cell B2
WS.Range("B2") = myfolder
'Write "Workbook" to cell A3
WS.Range("A3") = "Workbook"
'Write "Worksheet" to cell B3
WS.Range("B3") = "Worksheet"
'Write "Cell Address" to cell C3
WS.Range("C3") = "Cell Address"
'Write "Link" to cell D3
WS.Range("D3") = "Link"
'Write "Search string" to cell E3
WS.Range("E3") = "Search string"
'Save 0 (zero) to variable a
a = 0
'The DIR function returns the first filename that matches the pathname specified in variable myfolder and saves it to variable Value
Value = Dir(myfolder)
'Keep iterating through all files and folders until variable Value is empty
Do Until Value = ""
'Check if Value is . meaning current directory or .. meaning parent directory
If Value = "." Or Value = ".." Then
'Go here if Value is not . or ..
Else
'Check if last three characters match xls or last four characters match xlsx or xlsm
If Right(Value, 3) = "xls" Or Right(Value, 4) = "xlsx" Or Right(Value, 4) = "xlsm" Then
'Enable error handling
On Error Resume Next
Open workbook using password "zzzzzzzzzzzz"
Workbooks.Open Filename:=myfolder & Value, Password:="zzzzzzzzzzzz"
'Check if an error has occurred
If Err.Number > 0 Then
'Write contents of variable Value to cell A4 offset by number in variable a
WS.Range("A4").Offset(a, 0).Value = Value
'Write "Password protected" of variable Value to cell A4 offset by number in variable a
WS.Range("B4").Offset(a, 0).Value = "Password protected"
'Add 1 to number stored in variable a
a = a + 1
Else
'Disbale error handling
On Error GoTo 0
'Iterate through each worksheet in active workbook
For Each sht In ActiveWorkbook.Worksheets
'Iterate through all cells in variable RNG
For Each d In RNG
'Find cells in worksheet sht equal to value in cell d and save to range object variable c
Set c = sht.Cells.Find(d, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)
'Check if range object variable c is populated
If Not c Is Nothing Then
'Save address of range object variable c to variable firstAddress
firstAddress = c.Address
'Do ... Loop through all cells in range object variable c
Do
'Save contents of variable Value to cell A4 offset by variable a
WS.Range("A4").Offset(a, 0).Value = Value
'Save worksheet name to cell B4 offset by variable a
WS.Range("B4").Offset(a, 0).Value = sht.Name
'Save cell address to cell C4 offset by variable a
WS.Range("C4").Offset(a, 0).Value = c.Address
'Create a hyperlink in cell D4
WS.Hyperlinks.Add Anchor:=WS.Range("D4").Offset(a, 0), Address:=myfolder & Value, SubAddress:= _
sht.Name & "!" & c.Address, TextToDisplay:="Link"
'Save cell value in variable d to column E and row based on count variable a
WS.Range("E4").Offset(a, 0).Value = d
'Add 1 to variable a
a = a + 1
'Go to next cell in range object variable c
Set c = sht.Cells.FindNext(c)
'Continue to loop as long as c is populated AND c is not equal to the first address stored in variable
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
Next d
Next sht
End If
Workbooks(Value).Close False
On Error GoTo 0
End If
End If
Value = Dir
Loop
Cells.EntireColumn.AutoFit
End Sub
How to use the macro
The macro asks for a cell range containing the search strings you want to look for. The next step is which folder to look in?
A new sheet is inserted and populated with data if values are found.
Search multiple strings and return corresponding value(s) on the same row
The macro demonstrated above looks for multiple search strings in all Excel workbooks in a folder and also returns a value on the same row.
Sub SearchWKBooks()
Dim WS As Worksheet
Dim myfolder As String
Dim Str As String
Dim a As Single
Dim sht As Worksheet
Dim RNG As Range
On Error Resume Next
'Show inputbox and ask for cell range containing search values
Set RNG = Application.InputBox(prompt:="Select a cell range containing search strings" _
, Title:="Select a range", Default:=ActiveCell.Address, Type:=8)
On Error GoTo 0
'Show inputbox and ask for cell offset
Str = Application.InputBox(prompt:="Cell Offset:", Title:="Offset", Type:=2)
With Application.FileDialog(msoFileDialogFolderPicker)
.Show
myfolder = .SelectedItems(1) & "\"
End With
Set WS = Sheets.Add
WS.Range("A2") = "Path:"
WS.Range("B2") = myfolder
WS.Range("A3") = "Workbook"
WS.Range("B3") = "Worksheet"
WS.Range("C3") = "Cell Address"
WS.Range("D3") = "Link"
WS.Range("E3") = "Search string"
WS.Range("F3") = "Returned value"
a = 0
Value = Dir(myfolder)
Do Until Value = ""
If Value = "." Or Value = ".." Then
Else
If Right(Value, 3) = "xls" Or Right(Value, 4) = "xlsx" Or Right(Value, 4) = "xlsm" Then
On Error Resume Next
Workbooks.Open Filename:=myfolder & Value, Password:="zzzzzzzzzzzz"
If Err.Number > 0 Then
WS.Range("A4").Offset(a, 0).Value = Value
WS.Range("B4").Offset(a, 0).Value = "Password protected"
a = a + 1
Else
On Error GoTo 0
For Each sht In ActiveWorkbook.Worksheets
For Each d In RNG
'MsgBox d
Set c = sht.Cells.Find(d, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)
If Not c Is Nothing Then
firstAddress = c.Address
Do
WS.Range("A4").Offset(a, 0).Value = Value
WS.Range("B4").Offset(a, 0).Value = sht.Name
WS.Range("C4").Offset(a, 0).Value = c.Address
WS.Hyperlinks.Add Anchor:=WS.Range("D4").Offset(a, 0), Address:=myfolder & Value, SubAddress:= _
sht.Name & "!" & c.Address, TextToDisplay:="Link"
WS.Range("E4").Offset(a, 0).Value = d
'Save value from cell based on offset variable Str
WS.Range("F4").Offset(a, 0).Value = c.Offset(0, Str).Value
a = a + 1
Set c = sht.Cells.FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
Next d
Next sht
End If
Workbooks(Value).Close False
On Error GoTo 0
End If
End If
Value = Dir
Loop
Cells.EntireColumn.AutoFit
End Sub
How to use
- Start macro (Alt+F8)
- Select the cell range containing search strings
- Press with left mouse button on OK button.
- Select how many cells to the right (positive numbers) or to the left (negative numbers) from the found value.
Example, 1 will return the adjacent value to the right.
Example, -2 will return the value from a cell two columns to the left of the found value. - Select the folder you want to use.
- Press with left mouse button on OK
The macro will now add a worksheet to your workbook and populate it with data if values are found.
The following article explains how to search a folder and subfolders:
Recommended articles
Search all workbooks in a folder and sub folders – VBA
This article demonstrates macros that allow you to search for a text string(s) in multiple worksheets and workbooks located in […]
This article demonstrates macros that allow you to search for a text string(s) in multiple worksheets and workbooks located in […]
Recommended reading:
Recommended articles
Open Excel files in a folder [VBA]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]
Files and folders category
Compare file names in two different folder locations and their sub folders
Today I would like to share a macro that compares the content in two different folders and their subfolders. It […]
Today I would like to share a macro that compares the content in two different folders and their subfolders. It […]
Copy data from workbooks in folder and subfolders
I will in this article demonstrate a macro that automatically opens all workbooks in a folder and subfolders, one by […]
I will in this article demonstrate a macro that automatically opens all workbooks in a folder and subfolders, one by […]
Filter duplicate files in a folder and subfolders
This is a follow up to my last post Compare file names in two different folder locations and their subfolders, the obvious […]
This is a follow up to my last post Compare file names in two different folder locations and their subfolders, the obvious […]
Excel categories
105 Responses to “Search all workbooks in a folder”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Excellent. Excellent job. The BEST of the BEST of the BEST.
Matt,
Thank you, I am happy you like it.
Is it possible to search a folder of workbooks, based on a cell within those workbooks, and collect a value from a different cell?
Kyle,
Yes, I believe it is possible. Send me an example workbook.
Tried using this and get a run-time error '91': object variable or With black variable not set. And it's this line of code: "Loop While Not c Is Nothing And c.Address firstAddress"
Any ideas why? thanks!
Cathy,
I made this macro in excel 2010, what is your version?
also using Excel 2010.
Cathy,
mac?
nope, just regular PC. thanks.
Cathy,
I found this: https://msdn.microsoft.com/en-us/library/aa264506(v=vs.60).aspx
but I have no clue.
No worries - thanks for looking!
Your Post has been very helpful, but I'm stuck on something. I've modified your code to fit my needs, but the problem is that my files I am pulling from have 31 pages, and I need the Macro to only pull from the first page. Is there code to do this? All 31 pages have the same "search" words.
Sub SearchWKBooks() Dim WS As Worksheet Dim myfolder As String Dim Str As String Dim a As Single Dim sht As Worksheet Set WS = Sheets.Add With Application.FileDialog(msoFileDialogFolderPicker) .Show myfolder = .SelectedItems(1) & "\" End With Str = Application.InputBox(prompt:="Search string:", Title:="Search all workbooks in a folder", Type:=2) If Str = "" Then Exit Sub WS.Range("A2") = "Path:" WS.Range("B2") = myfolder WS.Range("A3") = "Client" WS.Range("B3") = "Job #" WS.Range("C3") = "Labor" WS.Range("D3") = "Hours" WS.Range("E3") = "Materials" WS.Range("F3") = "ShopMtls" WS.Range("G3") = "Subs" WS.Range("H3") = "Other/Trash" WS.Range("I3") = "Job Cost" WS.Range("J3") = "Sell Price" WS.Range("K3") = "Profit $" WS.Range("L3") = "Profit %" WS.Range("M3") = "Link" a = 0 Value = Dir(myfolder) Do Until Value = "" If Value = "." Or Value = ".." Then Else If Right(Value, 3) = "xls" Or Right(Value, 4) = "xlsx" Or Right(Value, 4) = "xlsm" Then On Error Resume Next Workbooks.Open Filename:=myfolder & Value, Password:="zzzzzzzzzzzz" If Err.Number > 0 Then WS.Range("A4").Offset(a, 0).Value = Value WS.Range("B4").Offset(a, 0).Value = "Password protected" a = a + 1 Else On Error GoTo 0 For Each sht In ActiveWorkbook.Worksheets Set c = sht.Cells.Find(Str, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext) If Not c Is Nothing Then firstAddress = c.Address Do WS.Range("A4").Offset(a, 0).Value = Value WS.Range("B4").Offset(a, 0).Value = Range("K1") WS.Range("C4").Offset(a, 0).Value = Range("B36") WS.Range("D4").Offset(a, 0).Value = Range("c36") WS.Range("E4").Offset(a, 0).Value = Range("d36") WS.Range("F4").Offset(a, 0).Value = Range("e36") WS.Range("G4").Offset(a, 0).Value = Range("f36") WS.Range("H4").Offset(a, 0).Value = Range("g36") WS.Range("I4").Offset(a, 0).Value = Range("h36") WS.Range("J4").Offset(a, 0).Value = Range("M36") WS.Range("K4").Offset(a, 0).Value = Range("M43") WS.Range("L4").Offset(a, 0).Value = Range("J46") WS.Hyperlinks.Add Anchor:=WS.Range("M4").Offset(a, 0), Address:=myfolder & Value, SubAddress:= _ sht.Name & "!" & c.Address, TextToDisplay:="Link" a = a + 1 Set c = sht.Cells.FindNext(c) Loop While Not c Is Nothing And c.Address <> firstAddress End If Next sht End If Workbooks(Value).Close False On Error GoTo 0 End If End If Value = Dir Loop Cells.EntireColumn.AutoFit End SubInstead of pages I mean "worksheets"
Jonathan J,
Make these changes to my macro.
Change this line:
to this:
Set sht = Worksheets("Sheet1")Change Sheet1 to the sheet name you want to use.
Remove this line:
Next sht
Hai
Your code is very helpful for my requirement
if possible . kindly change the code to find 100 numbers from the folder (ie instead of giving each number input 100 numbers)
I have a folder of excel files that are named by no type of order and I was hoping to find a way to search through all of them and point out the ones that have a value greater than one I specific only in a specific column. So search through all files and tell me the ones that have a value greater than 6 in column C. Is this possible with minimal changes to this macro ? Cheers
[…] they accomplish the majority of the task that I want my macro to accomplish. Here's the first one Search all workbooks in a folder | Get Digital Help - Microsoft Excel resource, this macro creates a new worksheet that houses info on which cells contain the string that you […]
This code looks like it might be fantastically helpful to me and I appreciate your sharing it. I have got a syntax error at line 44:
WS.Hyperlinks.Add Anchor:=WS.Range("D4").Offset(a, 0), Address:=myfolder & Value, SubAddress:= _
I am using Excel 2013 and wonder if this might be the problem? (I am not experienced in VB). Many thanks
[…] a VBA module that will perform a text search in all workbooks in a particular folder. Blog post: Search all workbooks in a folder | Get Digital Help - Microsoft Excel resource Example workbook: […]
hi thanks for the great macro really makes a hard job much easier, can this be made to search subfolders as well?
thanks
rusl
rusl cato,
read this post:
Search all workbooks in a folder and sub folders
[…] (adsbygoogle = window.adsbygoogle || []).push({}); I am looking for a way to search the entire worksheet of multiple spreadsheets within a single folder for values greater than 100. If cells values of greater than 100 are found, then these will be listed in a worksheet/tab with file directory, worksheet name, cell address, cell value, and hyperlink to that cell. I have been able to find the code below which was very helpful for searching for a particular text strings. However, it is values (I think they are integers!?) that I need to search for (that are greater than 100). My VBA skills are basic and therefore not sure where to start. Any hints/tips would be great or a modification of the code below would be fantastic. Thanks for your help! https://www.get-digital-help.com/2014/01/08/search-all-workbooks-in-a-folder/ […]
Thank you SO much for this code! It is a lifesaver for a monthly process that occasionally results in my having to look for an error in 80+ Excel files!
Elaine Weigle,
I am happy you like it!
[…] You might want to give a look at this. Search all workbooks in a folder | Get Digital Help - Microsoft Excel resource […]
[…] there you might want to give a look at that.Search all workbooks in a folder | Get Digital Help - Microsoft Excel resource […]
Is there a way to make the search look in subdirectories as well
Matt Durbin,
read this post:
Search all workbooks in a folder and sub folders
[…] for some text but i don't want to open all these files one by one. I chanced across this website Search all workbooks in a folder | Get Digital Help - Microsoft Excel resource which has got the code to supposedly fulfill my needs. However, it does not work. The line […]
[…] Search all workbooks in a folder is a popular post, I am happy so many find it useful. […]
Hi! The file seems to be offline. Cant get it!
Any help? Other places where it is hosted?
Olaf,
The file works here. I emailed you the file.
Sir, This is very helpful topic. Here is one question or I want solution that I have one excel file how has the values in Column B. I Want it search one by one name in the folder worksheets. Can it be possible this.
Is there any way to adapt this code to repeat itself and find a list of data rather than just one specific "Str" and return all results on the same worksheet?
Bravo. Super macro - thank you for publishing it. Windows Explorer ("WE") with indexing option could not handle contents of my set of Excel workbook files. "WE" search was listing only some files but not all of them.
This looks great for what I am looking for. Can this code search workbooks with folders for duplicate reference numbers and then highlight those duplicates on a new worksheet perhaps
John,
No, the macro can´t do that, as it is. Can you describe in greater detail what you want to do?
Hi, is there any way to change it to reference a cell for the path and another for the search string?
Subscribed. This code cut my workload for the week by 90%.
I can't even begin to express my gratitude for this.
stefan
I am really happy to hear that, thank you for commenting.
Is there a way to adapt this code if all workBOOKS I am searching are password protected and all have the same password? I'd like to assure that the workBOOKS are re-protected by the same password after the search. Many thanks!
Sali
Change "zzzzzzzzzzzz" to your "password".
Excellent and The Best. Save me from hours of manual checking of raw data
helmi
Thank you.
Hey
Nice job, but for me it doesnt work.
I set the folder and the search key, it opens all the files but will not return any answer.
I use office 2010
Since a long time I was looking for the same type of code and
has matched to my requirement. I appreciate your work and knowledge.
I had also posted this in one of the forum but have not received any reply till date.
In case if possible in your code i.e the top first code if I
can search in all the files but in only one sheet say Sheet1.
I did see the code by Jonathan. But since there are lots of changes and I am not from the VBA background so I am unable to
understand the code.
As mentioned when I changed the line in your code it gave the error of Next.
I will be very thankful if you can help on this issue.
Thanks again
Veerat
Hi,
I am trying to search for a 'number' using this code. It shows the right sheet where that number is , but since I am trying to open sheets with the same 'number'
For example: sheet1 has 1,2,3,4,6,7,8
sheet2- Cell A1 = any of these numbers. SO, when I run the code it shows that number exists in sheet 1 but doesn't show sheet 2 which is what I am trying to reference the code to. Also, how can I replace the searched quantity to look at a specific cell in the worksheet?
I tried set sht = sheet("Sheet1") but when I do that it doesn't read the value but read its when all sheet are being checked for
hi, your marco is rly great, but is it possible to search more than two single strings at the same time? thanks.
Excellent, thank you.
would this work for Excel on a Mac?
Thanks for creating this code, which makes me hope, it can be the basis of something I'm looking for.
I would need to make a search of 5000 different values in multiple workbooks, located in a folder/subfolders. I didn't yet find any similar solution, however, the desired search result is more complex.
The point would be to find specific data, located in column A for example, which contains 5000 rows of data. In column B I would like to make the name of containing workbook written for every row of column A.
It is also possible, the column A data is not listed in any workbooks, so it should be left empty or any static text, like: "Not found".
Does anybody think, creating this code is possible?
Many thanks for any help!
Daniel
Brilliant! Thank you.
Thanks a lot!
Very new to computers and I have Excel 2003 and the MS update which does load your xlms file. The result after running your file says no files are in my folder, yet it has a great many xls files.
Can you help please.
Regards,
Kate
Any way to make this work on Mac? Love the whole idea, just need to use Mac. Thanks.
Chris,
I don´t own a mac, I can´t help you out.
Trying to help my daughter - this could be extremely useful in her job; however, it seems to only find results on the first sheet. She must search 20 to 30 Excel files, each with varying number of worksheets. The data she needs to find could be in any one of the worksheets, not just the first one. So far, this macro finds the data on the first sheet, not on any others. Can this be modified to search all the sheets in a workbook before going to the next?
This is such a terrific macro and will help save her hours of work if she can use it over multiple sheets within multiple workbooks. Thank you so much for your willingness to share your knowledge and expertise.
Chris,
Thank you.
It does search all the sheets in a workbook.
What excel version are you using?
I run the macro and it stops after open the first file in the folder and shows "This workbook contains links to other data sources" and then when I press with left mouse button on "no update links" the macro stops. Could you help me please? I'm not sure what is causing it?
Thanks!
Hey, thanks for the code, is there a way to change it so instead of prompting for the folder you can have it set to a certain location?
and also can you have it so it doesn't have to open a new worksheet each time?
Also does this work for words too, cant seem to get it to work searching for a key word in a cell
Correction, Does work for works but only if you get the whole cell right, can this be changed so it shows if any cell has the search word?
I want to get the pdf file base on the data in excel and save the pdf file in the other folder. can you help ?
This is just Perfect i was looking for. Thanks a ton sir. Much Respect :)
This macro is awesome! Thanks so much!
I see that this only works if the cell contents match exactly. Can anyone tell me how to modify the code so that it returns a result even the cell contents don't match exactly? For example, I enter search string 123.45a67 and the folder i am searching in has results such as 123:123.45a67. I want this to come up as a result also.
Thank you for this incredibly helpful macro!
I also would like to be able to search for contents "containing" only partial cell content. EX: search for 1234 and get cells that contain 123456
Thanks!
Hi Ron or Oscar, Just wondering if you had any luck with a partial match. As this is excatley what im looking for. Thank you again for an amazing code Oscar. James
Macro is great but
i'm getting is message
"search.xlsm is already open. reopening will cause any changes you made to be discarded. do you want to reopen search.xlsm"
after this workbook is closed
Verinder Singh
You probably have saved your workbook in the same folder as you are searching in.
Good day,
the code is working great!! Thanks :)
but I have another question. It is possible that in column E and F displays values from columns U and V from the workbooks. For example if am searching for a value 1234 and it is found in Book1 Cell A14 that displays also value fm cell U14 and V14?
many thanks
Hi Oscar,
This looks like this will be perfect for what I need. My only question is, instead of searching a local file can the macro search documents stored in a folder that is stored on a SharePoint?
Many thanks!
Steven Knightley
If the workbook is allowed to open excel workbooks stored on a sharepoint it should work fine.
Very Good code. Is it possible to have a list of all the key search words (search strings) and then run the macro for all the key words at once with the output in single sheet for all. I have like a list of 30 items that I want to search in a dump every hour, so can't really use this macro manually 30 times every hour. I hope you can help. Thanks in advance.
Also, it is feasible to search parts of values in a cell instead of the entire value in a cell as currently this macro gives result only if the entire search string is available in a cell.
I am looking for a VBA code which searhc excel file name from a folder based on name mentioned in sample file name and email to persons.
Example:-
I have a excel file which columns (A1 as Customer Name , B1 as email ID
VBA code to search name from cloumn A and same excel name file in a folder and email to mentioned email ID mentioned in B cloumn.
Please help to share the VBA code.
[email protected]
Hi,
I have tried and it was successful before. But now, I has problem
How to solve this problem?
"Runtime error 52
Bad file name or number". Thanks for the topic
Regards!
Truong,
I am not sure what is wrong but I found this:
https://msdn.microsoft.com/VBA/Language-Reference-VBA/articles/bad-file-name-or-number-error-52
Hi
I am grateful for sharing the macro. It works great.
For now VBA is for me unknown world. Is it possible to adapt it for search csv files?
Regards.
Hi,
This is a very awesome vba. I'm wondering if it's possible to only loop until the first match is found, then move to the next search string?
Regards.
Great bit of VBA code. Thank you VERY much. You generously providing this saved me quite a bit of time and effort.
One issue I am having is that the links open the "found" file, but give me a "invalid cell address" message. This may correlate to the Excel version in that it works fine for v. 2010, but there appears to be a hyperlink address issue with v. 2007. As far as I know, there is no different is how these 2 Excel version use hypertext links to other files & cells. Any insight would be welcome.
dw,
Which vba line is highlighted when the error occurs?
The macro fully executes properly and the issue only occurs when the hyperlink to the found cells is used.
I did a bit more looking and found that the hyperlink address did not include single quotes around the sheet name. It appears that if the sheet name contains spaces Excel need single quotes around the sheet name.
Again, thank you VERY much for your excellent work and assistance.
dw,
You are right, there seems to be a problem with worksheet names containing one or more space characters.
Here is a macro that works:
Search-multiple-workbooks-in-a-folderv3.xlsm
The problem is this line:
WS.Hyperlinks.Add Anchor:=WS.Range("D4").Offset(a, 0), Address:=myfolder & Value, SubAddress:=_ sht.Name & "!" & c.Address, TextToDisplay:="Link"change it to:
WS.Hyperlinks.Add Anchor:=WS.Range("D4").Offset(a, 0), Address:=myfolder & Value, SubAddress:=_ "'" & sht.Name & "'!" & c.Address, TextToDisplay:="Link"hi, I installed your code today, and it was running, but then i realized the Excel files i need are in binary format . . xlsb. I changed the macro to look for xlsb but not it runs, but stalls (and does nothing) after creating a target worksheet.
thoughts?
Jeffrey R Green,
Can you provide the vba line you changed?
Hi, the code is fantastic, however I was hoping you may be able to help me narrow this down. Can the folder location be preset, as in it only looks in the same folder each time ? that way you don't need to select the folder, just the string? I've tried tweaking the code but it always goes back to the original selection. Also, is there a way to pick up the string from a range, rather than a opening a inputbox?
Michael A
Can the folder location be preset, as in it only looks in the same folder each time ? that way you don't need to select the folder, just the string?
Yes, delete the following lines:
With Application.FileDialog(msoFileDialogFolderPicker)
.Show
myfolder = .SelectedItems(1) & "\"
End With
and replace with:
myfolder = "c:\yourfolder\"
Also, is there a way to pick up the string from a range, rather than a opening a inputbox?
myfolder = Worksheets("Sheet1").Range("A1").Value
Hi I have a question I have adjusted the code to my needs but i am running into a issue with some certain key numbers. it is like once it search for one of the certain key numbers/data, it does find the number but it never breaks out of the loop where it is copying the number of said category of number onto the spreadsheet.
Sub SearchWKBooks()
Dim WS As Worksheet
Dim myfolder As String
Dim Str As String
Dim a As Single
Dim Count As Single
Dim Found As Single
Dim sht As Worksheet
Dim Find As Range
Dim Val As Range
Dim test As String
'prompt the user to select the cells of what data to look for
On Error Resume Next
Set Find = Application.InputBox(Prompt:="Select a cell range containing search data(Example: $C$3:$C$20)" _
, Title:="Select the cell data range!", Default:=ActiveCell.Address, Type:=8)
If Err.Number = 424 Then
Exit Sub
End If
'prompt the user to select the cell with the value catagory to match up with
Set Val = Application.InputBox(Prompt:="Select a Catagory(Example: Paid In Full)" _
, Title:="Select a Cell Containing the Catogry to look for!", Default:=ActiveCell.Address, Type:=8)
If Err.Number = 424 Then
Exit Sub
End If
test = InputBox(Prompt:="Please enter password if the workbook is password protected, If there is no password please press ok" _
, Title:="Password", Default:="password")
If test = vbNullString Then
Exit Sub
End If
On Error GoTo 0
'prompt the user to select the file folder to run through the find the data in the list
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
If .Show = True Then
myfolder = .SelectedItems(1) & "\"
Else
Exit Sub
End If
End With
'Creating a new spread sheet to display the Data that was found along with location and link
Set WS = Sheets.Add
WS.Range("A2") = "Path:"
WS.Range("B2") = myfolder
WS.Range("A3") = "Workbook"
WS.Range("B3") = "Worksheet"
WS.Range("C3") = "Cell Address"
WS.Range("D3") = "Link"
WS.Range("E3") = "Search string"
WS.Range("F3") = "Catagory: " & Val
'set the counters to 0 or 1
a = 0
Count = 1
'Turning off the application updaters and events to speed up the process of the search without taking long.
'Application.StatusBar = False
'Application.ScreenUpdating = False
'Application.Calculation = xlCalculationManual
'Application.EnableEvents = False
'opening the file in the folder location and running the data through each spread sheet to see if the data is there and report the finding of found or not.
Value = Dir(myfolder)
Do Until Value = ""
If Value = "." Or Value = ".." Then
Else
If Right(Value, 3) = "xls" Or Right(Value, 4) = "xlsx" Or Right(Value, 4) = "xlsm" Then
On Error Resume Next
Workbooks.Open Filename:=myfolder & Value, Password:=test
If Err.Number > 0 Then
WS.Range("A4").Offset(a, 0).Value = Value
WS.Range("B4").Offset(a, 0).Value = "Password protected"
a = a + 1
Else
On Error GoTo 0
For Each d In Find
For Each sht In ActiveWorkbook.Worksheets
Set c = sht.Cells.Find(d, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)
' check the c cell to see if the object match with the data that is currently being looked for and if found check the value/catagory if it is the correct data set
If Not c Is Nothing Then
firstAddress = c.Address
Do
If UCase(Val) = UCase(c.Offset(0, 1).Value) Then
WS.Range("A4").Offset(a, 0).Value = Value
WS.Range("B4").Offset(a, 0).Value = sht.Name
WS.Range("C4").Offset(a, 0).Value = c.Address
WS.Hyperlinks.Add Anchor:=WS.Range("D4").Offset(a, 0), Address:=myfolder & Value, SubAddress:= _
"'" & sht.Name & "!" & c.Address, TextToDisplay:="Link"
WS.Range("E4").Offset(a, 0).Value = d
WS.Range("F4").Offset(a, 0).Value = c.Offset(0, 1).Value
a = a + 1
Found = 1
Set c = sht.Cells.FindNext(c)
End If
Loop While Not c Is Nothing And c.Address firstAddress
End If
' report the Data was not found after reaching the last spread sheet and checking if found was change to 1 if not then report not found.
If Count = ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count).Index Then
If Found = 1 Then
Else
WS.Range("A4").Offset(a, 0).Value = "could not find"
WS.Range("B4").Offset(a, 0).Value = "please double check the number"
WS.Range("C4").Offset(a, 0).Value = "N/A"
WS.Range("D4").Offset(a, 0).Value = "N/A"
WS.Range("E4").Offset(a, 0).Value = d
WS.Range("F4").Offset(a, 0).Value = "None"
a = a + 1
End If
End If
Count = Count + 1
Next sht
Count = 1
Found = 0
Next d
End If
Workbooks(Value).Close False
On Error GoTo 0
End If
End If
Value = Dir
Loop
'turn back on the status update screen calculation and events
'Application.StatusBar = True
'Application.ScreenUpdating = True
'Application.Calculation = xlCalculationAuto
'Application.EnableEvents = True
Cells.EntireColumn.AutoFit
End Sub
Hello I appriciate what your code does but I made some modifications to it and I am running into a issue where the code stays inside the loop and never breaks out after finding the key numbers I am looking for.
[VB 1="vbnet" Language=","]
Sub SearchWKBooks()
Dim WS As Worksheet
Dim myfolder As String
Dim Str As String
Dim a As Single
Dim Count As Single
Dim Found As Single
Dim sht As Worksheet
Dim Find As Range
Dim Val As Range
Dim test As String
'prompt the user to select the cells of what data to look for
On Error Resume Next
Set Find = Application.InputBox(Prompt:="Select a cell range containing search data(Example: $C$3:$C$20)" _
, Title:="Select the cell data range!", Default:=ActiveCell.Address, Type:=8)
If Err.Number = 424 Then
Exit Sub
End If
'prompt the user to select the cell with the value catagory to match up with
Set Val = Application.InputBox(Prompt:="Select a Catagory(Example: Paid In Full)" _
, Title:="Select a Cell Containing the Catogry to look for!", Default:=ActiveCell.Address, Type:=8)
If Err.Number = 424 Then
Exit Sub
End If
test = InputBox(Prompt:="Please enter password if the workbook is password protected, If there is no password please press ok" _
, Title:="Password", Default:="password")
If test = vbNullString Then
Exit Sub
End If
On Error GoTo 0
'prompt the user to select the file folder to run through the find the data in the list
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
If .Show = True Then
myfolder = .SelectedItems(1) & "\"
Else
Exit Sub
End If
End With
'Creating a new spread sheet to display the Data that was found along with location and link
Set WS = Sheets.Add
WS.Range("A2") = "Path:"
WS.Range("B2") = myfolder
WS.Range("A3") = "Workbook"
WS.Range("B3") = "Worksheet"
WS.Range("C3") = "Cell Address"
WS.Range("D3") = "Link"
WS.Range("E3") = "Search string"
WS.Range("F3") = "Catagory: " & Val
'set the counters to 0 or 1
a = 0
Count = 1
'Turning off the application updaters and events to speed up the process of the search without taking long.
'Application.StatusBar = False
'Application.ScreenUpdating = False
'Application.Calculation = xlCalculationManual
'Application.EnableEvents = False
'opening the file in the folder location and running the data through each spread sheet to see if the data is there and report the finding of found or not.
Value = Dir(myfolder)
Do Until Value = ""
If Value = "." Or Value = ".." Then
Else
If Right(Value, 3) = "xls" Or Right(Value, 4) = "xlsx" Or Right(Value, 4) = "xlsm" Then
On Error Resume Next
Workbooks.Open Filename:=myfolder & Value, Password:=test
If Err.Number > 0 Then
WS.Range("A4").Offset(a, 0).Value = Value
WS.Range("B4").Offset(a, 0).Value = "Password protected"
a = a + 1
Else
On Error GoTo 0
For Each d In Find
For Each sht In ActiveWorkbook.Worksheets
Set c = sht.Cells.Find(d, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)
' check the c cell to see if the object match with the data that is currently being looked for and if found check the value/catagory if it is the correct data set
If Not c Is Nothing Then
firstAddress = c.Address
Do
If UCase(Val) = UCase(c.Offset(0, 1).Value) Then
WS.Range("A4").Offset(a, 0).Value = Value
WS.Range("B4").Offset(a, 0).Value = sht.Name
WS.Range("C4").Offset(a, 0).Value = c.Address
WS.Hyperlinks.Add Anchor:=WS.Range("D4").Offset(a, 0), Address:=myfolder & Value, SubAddress:= _
"'" & sht.Name & "!" & c.Address, TextToDisplay:="Link"
WS.Range("E4").Offset(a, 0).Value = d
WS.Range("F4").Offset(a, 0).Value = c.Offset(0, 1).Value
a = a + 1
Found = 1
Set c = sht.Cells.FindNext(c)
End If
Loop While Not c Is Nothing And c.Address firstAddress
End If
' report the Data was not found after reaching the last spread sheet and checking if found was change to 1 if not then report not found.
If Count = ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count).Index Then
If Found = 1 Then
Else
WS.Range("A4").Offset(a, 0).Value = "could not find"
WS.Range("B4").Offset(a, 0).Value = "please double check number"
WS.Range("C4").Offset(a, 0).Value = "N/A"
WS.Range("D4").Offset(a, 0).Value = "N/A"
WS.Range("E4").Offset(a, 0).Value = d
WS.Range("F4").Offset(a, 0).Value = "None"
a = a + 1
End If
End If
Count = Count + 1
Next sht
Count = 1
Found = 0
Next d
End If
Workbooks(Value).Close False
On Error GoTo 0
End If
End If
Value = Dir
Loop
'turn back on the status update screen calculation and events
'Application.StatusBar = True
'Application.ScreenUpdating = True
'Application.Calculation = xlCalculationAuto
'Application.EnableEvents = True
Cells.EntireColumn.AutoFit
End Sub
[/vb]
Hello I appriciate what your code does but I made some modifications to it and I am running into a issue where the code stays inside the loop and never breaks out after finding the key numbers I am looking for.
VB 1="vbnet" Language=","
Sub SearchWKBooks()
Dim WS As Worksheet
Dim myfolder As String
Dim Str As String
Dim a As Single
Dim Count As Single
Dim Found As Single
Dim sht As Worksheet
Dim Find As Range
Dim Val As Range
Dim test As String
'prompt the user to select the cells of what data to look for
On Error Resume Next
Set Find = Application.InputBox(Prompt:="Select a cell range containing search data(Example: $C$3:$C$20)" _
, Title:="Select the cell data range!", Default:=ActiveCell.Address, Type:=8)
If Err.Number = 424 Then
Exit Sub
End If
'prompt the user to select the cell with the value catagory to match up with
Set Val = Application.InputBox(Prompt:="Select a Catagory(Example: Paid In Full)" _
, Title:="Select a Cell Containing the Catogry to look for!", Default:=ActiveCell.Address, Type:=8)
If Err.Number = 424 Then
Exit Sub
End If
test = InputBox(Prompt:="Please enter password if the workbook is password protected, If there is no password please press ok" _
, Title:="Password", Default:="password")
If test = vbNullString Then
Exit Sub
End If
On Error GoTo 0
'prompt the user to select the file folder to run through the find the data in the list
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
If .Show = True Then
myfolder = .SelectedItems(1) & "\"
Else
Exit Sub
End If
End With
'Creating a new spread sheet to display the Data that was found along with location and link
Set WS = Sheets.Add
WS.Range("A2") = "Path:"
WS.Range("B2") = myfolder
WS.Range("A3") = "Workbook"
WS.Range("B3") = "Worksheet"
WS.Range("C3") = "Cell Address"
WS.Range("D3") = "Link"
WS.Range("E3") = "Search string"
WS.Range("F3") = "Catagory: " & Val
'set the counters to 0 or 1
a = 0
Count = 1
'Turning off the application updaters and events to speed up the process of the search without taking long.
'Application.StatusBar = False
'Application.ScreenUpdating = False
'Application.Calculation = xlCalculationManual
'Application.EnableEvents = False
'opening the file in the folder location and running the data through each spread sheet to see if the data is there and report the finding of found or not.
Value = Dir(myfolder)
Do Until Value = ""
If Value = "." Or Value = ".." Then
Else
If Right(Value, 3) = "xls" Or Right(Value, 4) = "xlsx" Or Right(Value, 4) = "xlsm" Then
On Error Resume Next
Workbooks.Open Filename:=myfolder & Value, Password:=test
If Err.Number > 0 Then
WS.Range("A4").Offset(a, 0).Value = Value
WS.Range("B4").Offset(a, 0).Value = "Password protected"
a = a + 1
Else
On Error GoTo 0
For Each d In Find
For Each sht In ActiveWorkbook.Worksheets
Set c = sht.Cells.Find(d, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)
' check the c cell to see if the object match with the data that is currently being looked for and if found check the value/catagory if it is the correct data set
If Not c Is Nothing Then
firstAddress = c.Address
Do
If UCase(Val) = UCase(c.Offset(0, 1).Value) Then
WS.Range("A4").Offset(a, 0).Value = Value
WS.Range("B4").Offset(a, 0).Value = sht.Name
WS.Range("C4").Offset(a, 0).Value = c.Address
WS.Hyperlinks.Add Anchor:=WS.Range("D4").Offset(a, 0), Address:=myfolder & Value, SubAddress:= _
"'" & sht.Name & "!" & c.Address, TextToDisplay:="Link"
WS.Range("E4").Offset(a, 0).Value = d
WS.Range("F4").Offset(a, 0).Value = c.Offset(0, 1).Value
a = a + 1
Found = 1
Set c = sht.Cells.FindNext(c)
End If
Loop While Not c Is Nothing And c.Address firstAddress
End If
' report the Data was not found after reaching the last spread sheet and checking if found was change to 1 if not then report not found.
If Count = ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count).Index Then
If Found = 1 Then
Else
WS.Range("A4").Offset(a, 0).Value = "could not find"
WS.Range("B4").Offset(a, 0).Value = "please double check the account number or account"
WS.Range("C4").Offset(a, 0).Value = "N/A"
WS.Range("D4").Offset(a, 0).Value = "N/A"
WS.Range("E4").Offset(a, 0).Value = d
WS.Range("F4").Offset(a, 0).Value = "None"
a = a + 1
End If
End If
Count = Count + 1
Next sht
Count = 1
Found = 0
Next d
End If
Workbooks(Value).Close False
On Error GoTo 0
End If
End If
Value = Dir
Loop
'turn back on the status update screen calculation and events
'Application.StatusBar = True
'Application.ScreenUpdating = True
'Application.Calculation = xlCalculationAuto
'Application.EnableEvents = True
Cells.EntireColumn.AutoFit
End Sub
/vb
my apologies so it was just delayed on posting thought it was my computer... but i found a work around on the issue I just needed to include another if statement before the the matching of the value with a single counter check to force the find next c
brent
I am happy you got it working.
Brent, can you please share the modified code. As i too want to report the found or not found cases. Appreciate the help.
Oscar,
On your May 21, 2014 at 12:29 pm response to user Jonathan J, after applying the recommended changes you advised, the Worksheet (cell C4), Cell Address (D4), and Link (F4) no longer populate after the macro runs. Can you please help me out with this?
Hi
Is it possible to add a range in the workbooks? When I run it I get the result that the search word is repoted in the entire row
Cell Address
$B$1
$C$1
$D$1
$E$1
$F$1
$G$1
$H$1
$I$1
$J$1
$K$1
$L$1
$M$1
$N$1
$O$1
$P$1
$Q$1
$R$1
$S$1
$T$1
$U$1
$V$1
$W$1
$X$1
$Y$1
$Z$1
$AA$1
$AB$1
............
It's crazy. I have 75 excel files; each with multiple worksheets. This thing ROCKS on most files... totally ignores others. I get no errors. I've checked that the "search words" are in the bad files so that the macro can find it. I've renamed the bad files just in case something was wonky in the filename. I've rerun the macro multiple times; it is very consistent at missing the same files. I've created a clean folder with two files; one file that I know will work, the file other I know will not work... still ignores the same file.
I've also checked the file properties of the "bad" files. Unless I'm missing something they are the same. Got any ideas?
Ray
Thank you for "Search multiple strings and return corresponding value(s) on the same row". I use it all the time. I look for same string across different workbooks in multiple locations to sought values that are in different columns in each workbook (i.e. I am looking for QWERTY in workbook 1 to return value from column A, in workbook 2 value from column G, in workbook 3 value from column D etc.).
Would you be able to adjust the code for a N number of static searches where paths to workbooks and offset variables are fed from range in spreadsheet (similar to N numbers of search strings), i.e. in A1 path to workbook 1, B1 - where you manually input offset number, A2 path to workbook 2, B2 - manual input for offset column etc, looped until there is no path to workbook?
Oscar thanks a lot. You rock
Hey Oscar,
Great macro! Excactly what I was looking for. Very nice extra: the links in the outputsheet to directly jump to the results. This macro saves me a lot of time and effort every month. And maybe more important: the macro won't miss any match and I might when I search manually!
Thank you very much!
Naar.
Hiya, I'm really glad I've found this information. Today bloggers publish just about gossips and internet and this is actually frustrating.
A good site with exciting content, this is what I
need. Thanks for keeping this website, I'll be visiting it.
Do you do newsletters? Can not find it.
Dear Mr. Oscar:
I was looking for this solution since ages! Excellent!!!
Is it possible to show value instead of link? How?
Thank you.
Welcome, Mister
The method is very useful and works great
But I have a problem
How to return multiple offset values 1 & 2 & 3
Hi There
Thank you for the Great piece of work
can I run "Search-multiple-workbooks-in-a-folder.xlsm"
and have it leave all the files open instead of it closing them all?
Oscar, Thanks a lot for this. It is what I have been looking for BUT I was hoping that after the string is found, How can I add a line in the code to "Entire row copy" and paste it to the output after the column containing the "Link"?
Thanks,
Kasango