Avatar of PeterFrb
PeterFrb
Flag for United States of America asked on

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:

Shell "Explorer C:\FindFiles\

Open in new window


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.txt

Open in new window

).  (I realize that the previous example goes out on a limb, ss windows has become so dominant a part of the landscape, redirection is hardly in place any more.

Anything that will support VBA to see the results of a search is what I seek.  Thank you.
Windows OSVBAMicrosoft Word

Avatar of undefined
Last Comment
Bill Prew

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Fabrice Lambert

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Fabrice Lambert

redirection is hardly in place any more.

Think again, command line is still system admins's favorit tool.

PeterFrb

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
Fabrice Lambert

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.

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
SOLUTION
Bill Prew

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
PeterFrb

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
PeterFrb

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.
Bill Prew

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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Fabrice Lambert

Bill Prew

@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