macro search in folder and subfolders

[max_max]

Hello to all,
you can change the macro attached because you can find your files in folder and subfolders ?:

Please Login or Register  to view this content.

max_max

[Bernie Deitrick]

You need to add a little bit of recursion, like this - which means some of your code that should only be run once needs to be moved to a separate macro, some of your variables need to be made public, and your original macro needs to be passed the path as an argument. You may also want to store the path as well as the filename: if you do, change

.Cells(lRow, 1) = wbk.Name

to

.Cells(lRow, 1) = wbk.FullName

Please Login or Register  to view this content.

[max_max]

Error here:

Sub SearchFolders(strPath As String)
Dim fso As Scripting.FileSystemObject


compilation error
defined type not defined

max_max

[Bernie Deitrick]

Sorry - You need to set a reference to MS Scripting Runtime.

Capture.JPG

[max_max]

O.k. thanks bernie.
Sorry for my English I translate with google translator
you can change this?:

1) display folders and subfolders in sheet2
2) does not display the msgbox processing ....
but only the final result. > done
3) for new search reset all sheet2

I hope I explained.
max_max

[Bernie Deitrick]

Try this - not sure why you would reset all sheet2 since the code creates a new sheet every time the code is run.

Please Login or Register  to view this content.

[max_max]

O.k. thanks bernie.
Sorry for my English I translate with google translator
you can change this?:

1) display folders and subfolders in sheet2 <<< O.k. done
2) does not display the msgbox processing ....but only the final result. > done <<< not done modified
3) for new search reset all sheet2 <<< not done modified

I hope I explained.
max_max

[Bernie Deitrick]

Maybe this for #3 - see changes in bold. Not sure what you mean by #2 or what you want instead.

Please Login or Register  to view this content.

[max_max]

Hello bernie,
I did not see your last post and in the meantime I changed the macro by adding the msgbox but now does not work.
max_max

[max_max]

Hello bernie,
Now works almost everything, vba does not work in the sheet name "ricerca"
max_max

[max_max]

I was able to correct the name of Sheet2, remain one corrections:
msgbox "done/fatto" must appear only one time at the end of the research
I hope I explained.
max_max

[Bernie Deitrick]

Move your message box from SearchFolders to startSearch:

Please Login or Register  to view this content.

[max_max]

Hello bernie is perfect!
Thanks so much!
Another thing is possible That the workbook found become a link to view the file immediately?
Many thanks again.
max_max

[Bernie Deitrick]

Please Login or Register  to view this content.

[max_max]

Hello bernie,
I tried the new macro here at home and work.
Tomorrow morning I try the macro in my office.
Thank you so much.
A greeting and thanks again.
max_max

[max_max]

Hello bernie the macro works well!
If it is not too complicated, you can change this ?:
Now to find a folder I have to write in the folder D1
it is possible in addition to this method with a double click in D1
that you open all the subfolders that are in the path to B1?
I hope I explained.
A greeting and thanks again.
max_max

[Bernie Deitrick]

You could need to use a macro to open all the folders in the tree (which is probably a bad idea anyway). Once you open the root folder, then you can navigate to any subfolder of interest.

If you wanted, you could open the folder named in the cell in column D by using the before double-clcik event code:

1) Copy this code.
2) Right-Click the sheet tab of interest.
3) Select "View Code"
4) Paste the code into the window that appears.
5) Save the file as a macro-enabled .xlsm file.
6) Make changes as needed

Please Login or Register  to view this content.

[max_max]

Hello bernie,
great.
it is possible instead for the whole column D only one of the D column cell for example D2?
Thank you,
max_max

[Bernie Deitrick]

It will open the folder in the cell in D that you double-click on - so, if you want D2, double-click D2.

[max_max]

Thanks Bernie.
max_max

[max_max]

Hello,
is a change to the macro possible?
Everything works well, just a small problem.
The macro must also work on merged cells.
If the search is on workbook with merged cells this error.

Runtime Error -2147352565 (8002000b)
impossible to find the searched data

Thanks in advance.
max_max

[Bernie Deitrick]

Which line in which macro produces the error?

[max_max]

gest_err:

path2 = Worksheets("search").Range("B2").Text '<<< path

If Err.Number = 76 Then

MsgBox "The path """ & path2 & """ " & Chr(13) & "is missing or the name has been changed.", vbCritical, "PATH ERROR"
'MsgBox "Il path """ & Path2 & """ " & Chr(13) & "non esiste o è stato cambiato.", vbCritical, "PATH ERRORE"

Exit Sub
End If


End Sub


VBA > F8 = image1

workbook > macro = image2

[Bernie Deitrick]

There is nothing wrong with that line of code - just make sure that the workbook that is active at the time has the sheet "Search" since that defaults to the activeworkbook. If you are opening some other workbook in other code, you may need to change it to include the workbook object, like

Please Login or Register  to view this content.

[max_max]

Hi Bernie,
everything works fine if there are no greasy cells
The only problem is only if the word / number to be searched is done on a workbook with merged cells.

Another information.
Here in:
Sub SearchFolders (strPath As String)
the search is only with xls

With wOut
strFile = Dir(strPath & "\*.xls*")
Do While strFile <> ""

the search is on xls/ xlsx / xlsm or only xls?
max

[max_max]

Hi,
I partially corrected the macro.
Now it works even with merged cells
Hi added in:

-----------------------------------------------------------------
Sub SearchFolders(strPath As String)
Dim fso As Scripting.FileSystemObject
Dim fld As Scripting.Folder
Dim sFolder As Scripting.Folder
Dim strFile As String
Dim rFound As Range
Dim strFirstAddress As String
'Dim valori As String

On Error Resume Next
"
"
"
"
"
On Error GoTo 0

End Sub
-----------------------------------------------------------------


Now this does not work:


-----------------------------------------------------------------
Sub startSearch()
"
"
"
"

gest_err:

path2 = Worksheets("search").Range("B2").Text '<<< path

If Err.Number = 76 Then

'MsgBox "The path """ & path2 & """ " & Chr(13) & "is missing or the name has been changed.", vbCritical, "PATH ERROR"
MsgBox "Il path """ & path2 & """ " & Chr(13) & "non esiste o è stato cambiato.", vbCritical, "PATH ERROR/PATH ERRORE"

Exit Sub
End If

End Sub
-----------------------------------------------------------------

the complete macros

Please Login or Register  to view this content.

max

[jeffreybrown]

max,

Please update your posts to include code tags.

[max_max]

Hello sorry for my english but my last post seems to me okay

[max_max]

hello to all, maybe I explained myself badly?
max_max

[max_max]

Hello, now the macro searches a text in B2.
you can change to search file extensions for sample: xls - xlsx - pdf - xlsm etc ... non text

Please Login or Register  to view this content.

max_max