Use VBA to Reproduce Explorer "Copy As Path" option
If I am doing a search through a number of files for a particular word ("Motor") or file configuration (".pdf"), I can, once the procedure is complete, press <Shift><Right Click>, and then left click on "Copy as Path". That takes all the found elements and stores them to the clipboard, where I can paste them any where I want, i.e., a text file or Excel.
I would like to replicate this functionality using VBA code. I would like VBA to capture a whole series of folder/file paths, based on the search criteria given it. I do know how to use vba to invoke a command prompt, and have the prompt run Explorer from a set folder:
What I don't know how to do is choose a search criteria when running Explorer, and I also don't know how to give VBA visibility to the files it has found. What might also work is redirection (">") which produces sends command-line output elsewhere. (i.e.,
Anything that will support VBA to see the results of a search is what I seek. Thank you.
I would like to replicate this functionality using VBA code. I would like VBA to capture a whole series of folder/file paths, based on the search criteria given it. I do know how to use vba to invoke a command prompt, and have the prompt run Explorer from a set folder:
Shell "Explorer C:\FindFiles\What I don't know how to do is choose a search criteria when running Explorer, and I also don't know how to give VBA visibility to the files it has found. What might also work is redirection (">") which produces sends command-line output elsewhere. (i.e.,
Shell "Explorer C:\FindFiles\ > CopyAsPath.txtAnything that will support VBA to see the results of a search is what I seek. Thank you.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Well, Fabrice, thank you! This is lovely, finished code, and I'm pleased to know that the lowly command line still hassome life to it.
Although this is a great start, it does fall short of my vision. When you perform a key word search in File Explorer, as in the word "Motor", the list it produces covers not only the word "Motor" in the file name, but also the word "Motor" embedded in file contents. File Explorer does a pretty good job of ferreting out these contents from multiple sources: Word, Excel, PowerPoint, and myriad programs well beyond the Microsoft family. When it comes to searching for key words, the file name is just the tip of the iceberg.
Taking "Motor" as an example, my current procedure is to put "Motor" in the search box, get the file list, "Copy as Path", and then paste the results into columb B of a spreadsheet. Into cell A1, I type the word "Motor" and copy and paste that down for all the files listed. That labor completes a single cycle for one found word; and I may have to "rinse and repeat" for the number of words for which I'm looking.
The command prompt, and by extension, VBA, does a fine job of opening Explorer to whatever folder I choose in the command line. What I don't know how to do is use the command line to place a desired word into the search box; and I don't believe that VBA has an object model for File Explorer, as it does for any of the Microsoft Office products. I am also not aware of any way of using VBA to capture the files listed in a search. If those results were redirectable to a command line, then I would choose to send those results to a file. The closest equivalent I can name for doing this is the "Dir" command, as follows:
C:\>Dir *.pdf > PDFFiles.txt
This is essentially what you did in your code block. Doing the equivalent for embedded words would fulfill my requirement.
PS: I am a big fan of recursion and accustomed to writing subroutines like your "FindFiles". Recursion is a lifesaver in this situation. Sincerely, ~ Peter Ferber
Although this is a great start, it does fall short of my vision. When you perform a key word search in File Explorer, as in the word "Motor", the list it produces covers not only the word "Motor" in the file name, but also the word "Motor" embedded in file contents. File Explorer does a pretty good job of ferreting out these contents from multiple sources: Word, Excel, PowerPoint, and myriad programs well beyond the Microsoft family. When it comes to searching for key words, the file name is just the tip of the iceberg.
Taking "Motor" as an example, my current procedure is to put "Motor" in the search box, get the file list, "Copy as Path", and then paste the results into columb B of a spreadsheet. Into cell A1, I type the word "Motor" and copy and paste that down for all the files listed. That labor completes a single cycle for one found word; and I may have to "rinse and repeat" for the number of words for which I'm looking.
The command prompt, and by extension, VBA, does a fine job of opening Explorer to whatever folder I choose in the command line. What I don't know how to do is use the command line to place a desired word into the search box; and I don't believe that VBA has an object model for File Explorer, as it does for any of the Microsoft Office products. I am also not aware of any way of using VBA to capture the files listed in a search. If those results were redirectable to a command line, then I would choose to send those results to a file. The closest equivalent I can name for doing this is the "Dir" command, as follows:
C:\>Dir *.pdf > PDFFiles.txt
This is essentially what you did in your code block. Doing the equivalent for embedded words would fulfill my requirement.
PS: I am a big fan of recursion and accustomed to writing subroutines like your "FindFiles". Recursion is a lifesaver in this situation. Sincerely, ~ Peter Ferber
If looking for file names is easy, looking into file's content is a completly different matter.
Reproducing Explorer's find functionality behavior is probably doable with the help of API, but this is nowhere near trivial and way beyond my knowledge.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
No problem, Fabrice. I appreciate your thorough contribution and frankness: re, the limits of your knowledge. If anyone else out there has any clues to this problem, I would love to hear their thoughts on the matter.
One more item, as food for thought: If there were an online equivalent program capable of performing a word search on my internal drive, if the output were presented as a standard HTML format, I imagine a scenario in which I web scrape the contents. Does Google have such a feature?
Sincerely, ~ Peter Ferber
One more item, as food for thought: If there were an online equivalent program capable of performing a word search on my internal drive, if the output were presented as a standard HTML format, I imagine a scenario in which I web scrape the contents. Does Google have such a feature?
Sincerely, ~ Peter Ferber
ASKER
Hey, Bill! Thanks so much. I will be looking at this code intently today, and seeing if I can make it run.
Looking at the code, these are my first observations, prior to having attempted a run of the process, :
* I do note that I have CScript.exe on my windows machine: I've never used it before, so that's new for me. May I presume that the code above is good to go.
* regarding "Putting MACHINENAME.SystemIndex in the from clause", where do I find my machine's name?
* My biggest question: does this dataset constitute just file names, or is there any window into the file's contents? That's the piece that File Explorer provides that I would need to be seeing in the SystemIndex table. If it's not there, then does this technique provide more value than Fabrice's stated solution?
Thank you both for the input. I appreciate your freely sharing your knowledge.
Looking at the code, these are my first observations, prior to having attempted a run of the process, :
* I do note that I have CScript.exe on my windows machine: I've never used it before, so that's new for me. May I presume that the code above is good to go.
* regarding "Putting MACHINENAME.SystemIndex in the from clause", where do I find my machine's name?
* My biggest question: does this dataset constitute just file names, or is there any window into the file's contents? That's the piece that File Explorer provides that I would need to be seeing in the SystemIndex table. If it's not there, then does this technique provide more value than Fabrice's stated solution?
Thank you both for the input. I appreciate your freely sharing your knowledge.
I do note that I have CScript.exe on my windows machine: I've never used it before, so that's new for me. May I presume that the code above is good to go.Yes, save it as a VBS file and from a command prompt pass that file name to CSCRIPT. You will want to press CTRL-C after a second or two since as it stands (unless you restrict to a folder with just a few files in it as mention in a comment) it will list all Windows Search files.
regarding "Putting MACHINENAME.SystemIndex in the from clause", where do I find my machine's name?You shouldn’t have to do anything there, it will default to the current machine automatically.
My biggest question: does this dataset constitute just file names, or is there any window into the file's contents? That's the piece that File Explorer provides that I would need to be seeing in the SystemIndex table. If it's not there, then does this technique provide more value than Fabrice's stated solution?That’s actually why I passed this along. If I am interpreting this right, it’s not just showing hits from the file system, but rather hits from the Windows Search Index. So this will already have indexed the content of files, as long as you have configured Windows Search to do that on your computer. So the results displayed should include content matches. You will probably have to understand the syntax of the query statement to see how to get the same results as just typing in some text to the Explorer search box.
»bp
Maybe this article can help:
https://docs.microsoft.com/fr-fr/windows/win32/search/using-sql-and-aqs-to-query-the-index
@Peter,
Are you all set with this now, or do you need more help? If all set, could you please close it out now. If you need help with the question close process take a look at:
»bp
Are you all set with this now, or do you need more help? If all set, could you please close it out now. If you need help with the question close process take a look at:
»bp
Think again, command line is still system admins's favorit tool.