Search and open a file that can be pdf,word,jpeg etc

[maverick_b]

Hi,

I am a newbie with macros and trying to learn but it is not easy . What I need to do should be easy but I have no clue how to achieve it.

We have lots of files (pdf,doc,docx,xls,xlsx,jpeg,png), in folders and subfolders. There are many subfolders in the main folder. It is sometimes hard to find the file we need as we need to click many times until we get to the folder we want and open the file.

We are keeping a log of the file names in excel sheet. We need to enter the name of the file we want to open in the inputbox in excel sheet and the code will search for the file name typed in the main folder and subfolders and open it. That is all we need.

Any help regarding this problem is extremely highly appreciated. Thank you

 

[maverick_b]

nobody ??

 

[Cindy Ellis]

Hi maverick_b, and welcome to the forum.
It seems that you haven't had much luck with getting answers to your questions that you've posted. I'll try to help, but I'll need your help to clarify what it is you're asking for.
The scenario you describe in your current question is a bit confusing.
Do you already have a list of filenames (including the folder name) in a list maintained within Excel? If so, this is a relatively easy problem to solve.
Or do you want the user to be able to use Excel to replicate Windows file manager "search" functionality? If the answer to this is "yes", then the problem isn't so easy...could you use Windows search instead of Excel?
Hoping to help,

Cindy

 

[maverick_b]

Hello Cindy,
Thank you very much for your reply.
Well actually we do have the list of file names and folder names.
We have the main folder in my documents and folder name is xxxx. In xxxx we have files and subfolders. In subfolders we also have files. So I basically want to point the excel macro to that main folder and search for the file name in that main folder as well as subfolders. Once the file is found, it should be just open. That is all.
I hope it is not too hard.

 

[Cindy Ellis]

Is the folder for each file included with the filename in the Excel file (in a column)? If not, what if there is more than one file with the same name?

 

[Cindy Ellis]

Before I spend a lot of time going down the wrong path, here is a description of a proposed solution, with the following assumptions:

• The path names are in column A
• The filenames are in column B

If, by chance, the information is in that format, then a macro could create a hyperlink to the file in Column C. To use it, you would use auto-filter to select the filename. If there were duplicates, they would both be listed. The user would then click the hyperlink in column C to open the file.

Would a solution like that work?

 

[maverick_b]

Well, actually I was hoping that user would be able to punch in the file name in the input box and the required file would open. The macro would search the defined path and its sub folders to locate the file and open it. Is that possible? I know that excel 2003 had a function for searching files which does not work in office 2007 and 2010.
There are no path names in the worksheet. Only the file names are recorded. File names are recorded in columns E, H, I, J,K,L,M,N,O.

 

[Cindy Ellis]

OK then, several important questions:

1. What if the same filename is present in 2 or more directories? It will be more complicated if the macro has to notify the user of this. The default behavior will be to stop at the first file that matches the filename entered.
2. What if there is no file by the name "punched in" by the user?
3. If the user is typing in the filename, then does it matter at all that they're already listed on the worksheet?

 

[maverick_b]

OK then, several important questions:

1. What if the same filename is present in 2 or more directories? It will be more complicated if the macro has to notify the user of this. The default behavior will be to stop at the first file that matches the filename entered.
2. What if there is no file by the name "punched in" by the user?
3. If the user is typing in the filename, then does it matter at all that they're already listed on the worksheet?

File names are unique.
If the file name punched does not exist, we can always get the error message.
The reason of listing them on worksheet is to keep track and open them fast when we need to locate the file. Because the subfolders are dynamic. We are creating a subfolder everyday with a new date and there are many subfolders. It is requires a few click until we reach to the folder and it is not so straight forward.

One idea that I have maybe is to have the search function ( you know the ctrl+f) in macro so they can type the file name there and locate it in the excel sheet. Than if it is already hyperlinked, than it can be open by simply a click. This is not the best solution however it maybe a workaround solution.

 

[maverick_b]

I need to catch the plane now. Traveling in 3 hours to Poland I will reply back in 12 hours or so. Thx for the help.