Open all files in a folder at once

[granomark]

Hello all, 

I am looking for a solution to my problem of needing to open all files in a folder with a single click from excel. I have a spreadsheet that has a bunch of information pulled from several reference files. I am needing to have a link to these reference files in case I want to access the references where all this information came from without having to dig through directories to find each one. My first and obvious choice was to just create a hyperlink inside excel, however that only lets my link one folder or file to one cell. So, my solution that I came up with was to write a short script to open all files in the folder where I have the references located, and then just hyperlink to the batch file. I for the most part achieved this as I just used this very short script to run:

for %%i in (H:\Test One\Test Two\*.*) do start %%i

and this would work but the problem is ALL of my files and folders have spaces that I want to use this on have spaces in the names and when I run it, command prompt gets stuck do to the spaces and cannot find the file specified. I would think this is a simple fix but I am not able to figure it out. When I did a test and renamed all the folders to contain no spaces, the script worked exactly as I wanted it, however I am not at authority to go changing all the names and folder names on the server, nor do I want to spend all the time. The ultimate would be to have a .bat file that I could just drop in every folder that this will need to go to, and it will automatically detect all files in the folder it is contained inside and open them up without having to designate which folder to look in. But, either or would work, just a matter a tedious time saved versus spent. I can live with either. 

also If anyone has a different solution to this problem, I would love to hear it and try it out. maybe there is a more elegant way contained inside excel itself but I am not too familiar with it. 

Thank you in advance for any help!

Edited July 21, 2015 by granomark

[JohnOne]

Something like this...

Local $array = _FileListToArray("path\to\folder\", "*.xls", 1)

For $i = 1 To $array[0]
    ShellExecute($array[$i])
Next

 

[granomark]

Something like this...

Local $array = _FileListToArray("path\to\folder\", "*.xls", 1)

For $i = 1 To $array[0]
    ShellExecute($array[$i])
Next

 

I tried this and plugged in my file path but all that pops up is a cmd window for a half second and goes away without opening anything. One thing also is I am needing to open pdfs not additional excel documents. I changed the "*.xls" to "*.pdf" which I am assuming is what I needed to do as that was fairly obvious. also my directory is on a network server. for example H:\User Name\Documents\ and I plugged that direct address into this code but I was not sure if that was exactly how I was supposed to do it or if I was supposed to write it as \\server\data\User Name\Documents as "\\server\data" is the drive that I am mapped too. also, I am assuming this is just supposed to be saved as a .bat file also which is what I saved it as. but let me know if you have any more suggestions and Thanks for your time and help!

[ViciousXUSMC]

Try adding $bReturnPath = True to _FileListToArray() so that you will be sending the full file path to the ShellExecute() rather than just the file name.

Else the script needs to be in the same directory as those files.

 

Local $array = _FileListToArray("path\to\folder\", "*.xls", 1, True)

For $i = 1 To $array[0]
    ShellExecute($array[$i])
Next

 

Based on the fact you're working with Excel you may find it very handy to look at Waters Excel UDF, also I work with Excel and incorporate currently selected text into functions via hotkeys so I can say have a computer name selected in a cell and  press Ctrl+Shift+A to instantly go into that computers Admin Share by taking a Copy to clipboard and pasting the clipboard into my function.

 

Edit: Your code is not going to work as a .bat this is not Windows Scripting, this is AutoIT so you need to install AutoIT and save this code into a .au3 file or compile it from a computer with AutoIT and run it on another computer as a .exe

Edited July 22, 2015 by ViciousXUSMC

[granomark]

Try adding $bReturnPath = True to _FileListToArray() so that you will be sending the full file path to the ShellExecute() rather than just the file name.

Else the script needs to be in the same directory as those files.

 

Local $array = _FileListToArray("path\to\folder\", "*.xls", 1, True)

For $i = 1 To $array[0]
    ShellExecute($array[$i])
Next

 

Based on the fact you're working with Excel you may find it very handy to look at Waters Excel UDF, also I work with Excel and incorporate currently selected text into functions via hotkeys so I can say have a computer name selected in a cell and  press Ctrl+Shift+A to instantly go into that computers Admin Share by taking a Copy to clipboard and pasting the clipboard into my function.

 

Edit: Your code is not going to work as a .bat this is not Windows Scripting, this is AutoIT so you need to install AutoIT and save this code into a .au3 file or compile it from a computer with AutoIT and run it on another computer as a .exe

ok so I got it into AutoIT and put it in the script editor, added your before the "for" loop, and tried to run the script but I get an error for unknown function name. It says that

Local $array=^ERROR 

Not sure what this means I tried entering my directory address using the mapped drive letter "H:\" and also directly "\\server\data\...etc" but both gave me the same error. 

what it seemed to me, was that it just wasn't finding any files which gives the array an error value. but I am not sure how to fix this. 

 

Edited July 22, 2015 by granomark

[JohnOne]

Add...

#include <File.au3>

For unknown function name error.

[granomark]

Add...

#include <File.au3>

For unknown function name error.

Awesome that fixed it! I tried also to change the file extension to "*.*" to see if it would open up every type of file in the folder , (which won't be a problem for me, all the files I will be using this on are pdfs and I just wanted to try it for fun), and it did open everything, except it went in an infinite loop of trying to open everything over and over again until it crashed my computer. Don't know why this happened but thought I'd share. Thank you for your help!

[JohnOne]

Perhaps the for next loop is nested in some other loop, which might need to be exited