Search a folder with sub folders to find and open a PDF based on partial file name.

Hello everyone! Learning to use VBA and still a novice!

I have a job list where each row is information about the job. Column B is the job number always in the same format of letters and numbers (example: 2015-AAAA-001). The file related to it will always be a .pdf and will always start with job number (example 2015-AAAA-001 - Document1.pdf). I am trying to create a macro that allows the user to either us an Application.Input off of a command button and then click the job#, or if it would automatically hyperlink to the file. I am not sure which would be faster/more realistic to code. The master directory could be C:\Master and have subfolders with subfolders that would have to be searched through.

I have tried to code this different ways and have failed every time! If anyone can help it would be greatly appreciated!

Thank you!

Thanks for reaching out Tom!

I have a function to open the PDF, and I have the input box collecting the data properly. The rest of the code is something I found on another part of the forums and was trying to modify it to work. So far it does not compile or work! Any help is appreciated.

Please Login or Register  to view this content.

I would like the macro to work off a control box inside the excel workbook. When the control box is clicked it will open up the input data box allowing the user to click on the entry from column B containing the job number. It should then find the pdf files based off of the partial name and open.

So you have the job #'s listed in column B. All are formated as 4 numbers - 4 letters - 3 numbers. example is 2015-AAAA-001. All of the corresponding fPDFS files to that job number start with the job number. For example 2015-AAAA-001 - Document1.pdf. The macro would have to search the folder and sub folders to find these files based on the job # and open the pdfs.

Please let me know if I am not expressing it properly and I will try to clarify better!

Hi guys,

That's an amazing code from AlphaFrog.

Sorry to steal the thread now after some time, but I've been trying to convert it into a code that would open only a folder based on input, to no avail.

(example: strRoot remains the same, when the folder is variable and consists of a number and a name; so I need to input a number like 123456 and it should open strRoot\123456 - Frank Zappa)

AlphaFrog do you know what piece of code should be changed and into what? I assume something in the function but I can't get my head arount it

Hi AlphaFrog

Sorry for not being clear enough there.

What I meant is that I know a root folder and in this folder there are a lot of subfolders dedicated to various clients.

Each subfolder has a name which consists of a number like 123456 and the client's name. So an example would be C:\Test\123456 Frank Zappa\

And what I'm aiming to do is to simply open that folder, based on the number only (which would be in a cell).

I hope it makes more sense now..

Hi there, I guess no luck with this one?

No, I literally want to open the folder with the windows explorer

Basically you run the code, you're prompted for a number referring to a client and a folder opens in the windows explorer containing all the files inside - that's the idea.
The folder name consists of Number + Name + Surname, but it would need to find the correct folder with the Number part only.

English is not my native and I'm not that expert at technical lingo so I'm not sure if I'm making it clearer or rather more convoluted.

You're goddamn reptile magician, AlphaFrog. Big thanks!!

Hey AlphaFrog, I wanted to raise a weird point.

It all works nicely (you should have seen how my colleagues were extatic when I implemented this today) with a standard button.

However I wanted to put something nicer on the spreadsheet and used a coloured text box.

The weird part is that the macro ran OK, but afterwards the CTRL+F shortcut stopped functioning?? What is even weirder that it would come back after activating another sheet.

This is more of a FYC because I suppose this is some Excel quirk that probably makes zero sense.

No worries it's only aesthetics. I'm very glad anyway as you helped a lot!

Hi there,

Sorry to be a nuisance again, but I've got a question about this.

Sometimes one client will have multiple subfolders in the main folder and they will be called e.g. 12345 John Smith, 12345 John Smith 2, 12345 John Smith 3 and so on.

Do you think it's possible to capture a list of these folder names (if VBA finds more than one with number 12345) and give a choice to the user asking which one he wants to open (e.g. via a userform)? Or even open all of them for what it's worth?

Hi Alfafrog,

Thank you so much for VBA Code.

Please advise based on Post#6

It works fine if the "Test" folder is saved in C: drive.
Just wondering, what if we move this "Test" folder to another location, this macro needs to be updated. Is there anyway I can make it global meaning where ever the Test folder is saved the macro will run?

Thanks in advance.

Mashiul

Hi AlphaFrog,

Please refer to your #6 post.

Global means instead of main directory which is a specific location "C:\Test" can it be something like "\\Test" and macro will look for "Test" folder available anywhere in the computer?

That way if this excel file is saved to USB or DVD it will not give any error.

Is it little bit clear?

Mash

Hi AlphaFrog,

Thanks very much.

Mashiul