LinkBack URL
About LinkBacks
Hello to all,
you can change the macro attached because you can find your files in folder and subfolders ?:
max_max
Please Login or Register to view this content.
Hello to all,
you can change the macro attached because you can find your files in folder and subfolders ?:
max_maxPlease Login or Register to view this content.
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.
Bernie Deitrick
Excel MVP 2000-2010
Error here:
Sub SearchFolders(strPath As String)
Dim fso As Scripting.FileSystemObject
compilation error
defined type not defined
max_max
Sorry - You need to set a reference to MS Scripting Runtime.
Capture.JPG
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
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.
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
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.
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
Hello bernie,
Now works almost everything, vba does not work in the sheet name "ricerca"
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
Move your message box from SearchFolders to startSearch:
Please Login or Register to view this content.
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
Please Login or Register to view this content.
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
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
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.
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
It will open the folder in the cell in D that you double-click on - so, if you want D2, double-click D2.
Thanks Bernie.
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
Which line in which macro produces the error?
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
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.
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
Last edited by max_max; 04-08-2018 at 07:33 AM.
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
maxPlease Login or Register to view this content.
max,
Please update your posts to include code tags.
HTH
Regards, Jeff
Hello sorry for my english but my last post seems to me okay
hello to all, maybe I explained myself badly?
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
max_maxPlease Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks