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

M

maverick_b

New Member
Joined
Aug 12, 2005
Messages
8
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
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
Cindy Ellis said:
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?
Click to expand...
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.
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
open PDF & EXCEL files by click selected row in listbox on userform
Replies
1
Views
534
abdo meghari
A
A
  • Solved
macro to match part of PDF file name in cell with folders & subfolders to open it
2 3 4
Replies
37
Views
3K
Ali M
A
T
  • Solved
search PDF file based on main folder name & subfolder(month) throughout userform
Replies
7
Views
600
tubrak
T
K
  • Question
Need to create/source a macro/vba/program that auto populates info based on matching word criteria for community support agency
Replies
3
Views
465
Micron
M
H
  • Question
Macro to open up files in sub folder that have the lastest modified date as well as those 60 days before that date
Replies
1
Views
475
howard
H

Forum statistics

Threads
1,215,336
Messages
6,124,328
Members
449,155
Latest member
ravioli44

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top