Search File (String) name and Path

HI All,

I am looking for a VBA Excel code to look at the file name (column a) and return the full path (column b).
The File name may be in any of the sub folders and the code should return the exact file name and path to show where it is located.

Example

Column A : Test1
Column b: C:\Temp\Documents\Test1

Column A: Test99
Column B: C:\Temp\Documents\Test99


Appreciate your help on this.

Reference : have followed the steps within this documentation https://help.salesforce.com/articleV...e=en_US&type=1

Thank you,
Masond3

are you wanting:
- some VBA to search through all the sub-folders of C:\Temp\Documents\ and create a 2 column worksheet
OR:
-some VBA to look-up the values in column A and return the path in column B by searching through sub-folders

Is there only one level of sub-folder or are there sub-sub folders?

Ok - will post some code tommorow morning

I have elected to dump all values to the workbook
- makes it much easier to test and see what is going on

This should get you very close:
- Open the attached file
- Add some values to search for into column A in sheet "Results"
- run macro ListMyFiles with {CTRL} L
(creates entries in sheets F1 and F2)
- run macro EnterPath with {CTRL} K
(puts found paths in columnB in sheet "Results"

The default is that column A is assumed to not include the file extension and that is what is searched for
- the first matched file is returned - so this may give wrong result if a PDF and Excel file contain the same name
- the code is flexible and we can fix that easily
- the workbook VBA includes an example of how to restrict search to xlsx extensions (will explain thsi later if necessary)

Sublayers: - also for later - but flexibility is in-built
set to 0 excludes sub-folders
1 = 1 level, 2 = 2 levels

Any problems, please do not struggle, just let me know

All code is in same module:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

HI Kev,

Thanks for providing a working solution.

When I was doing my analysis I noticed that there was duplicate File Names.
What i will do is appen an Id infront of the FileName which will make the name distinct.

I am going through the steps you provided but i am encountering one issue.

On the Results tab column B "Not Found". However when i look at tabs F1/F2 the data is populated

This is the current structure

Pathway : C:\Temp\Documents
Filename : 00P0V00000oS28HUAS

Tab F1: – Returns “C:\Temp\Documents”
Tab F2: returns “00P0V00000oS28HUAS 00P0V00000oS28HUAS C:\Temp\Documents\00P0V00000oS28HUAS

duplicate File Names:
What i will do is appen an Id infront of the FileName which will make the name distinct

- sensible approach (if small in number)
Why the duplicate?
- do they have different extensions?

Tab F1: – Returns “C:\Temp\Documents”
Tab F2: returns “00P0V00000oS28HUAS 00P0V00000oS28HUAS C:\Temp\Documents\00P0V00000oS28HUAS

You have thrown a curveball here!

Please check the file's name on your system - looks like it may be missing its extension perhaps
- is there anything in column B on sheet F2?

Modification
I will send you a modification to the code to notify user if a file does not have an extension
- the correct approach here is to fix the file name rather than allow for it in the code -do you agree?

Are you asking me a question?

So we have a DB which holds attachments.
In the attachment DB we have a couple of columns Id, parentid, name etc ...
Id = Unique attachment Id
Parentid : Record the attachment is attached to
name : Name of attachment

What is the DB?
- another Excel file, another worksheet, an (Access?) database


So to help you I need to know where this data is held, how it is linked to its parent(=name) etc and how we get things right in the file listings

Am i missing something
- my browser cannot open that link you included in post#1 so I may be "flying blind"

not sure if I can help you with Pathway DB etc
- but if you can get the values into a workbook so that I can set up a lookup table..... etc

So we may have a 1 pdf called (Mason) which is attached to 5 records. In our DB that would come through 5times. Thats why why ID is important as its unique

Just thinking aloud (sorry if I am rambling, but I cannot see your data...)

- in column A in your original file (ie what comes off your system) does Mason appear 5 times?
- if so, can you tell the 5 Masons apart when looking in your original file?
- if you cannot, could VBA perhaps amend the name in columnA as it grabs the filepath for each of the 5 entries (I am hoping the 5 files are listed on F2)

- in folder C:\Temp\Documents (or sub-folders) does Mason appear with 5 different extensions or 5 different names?
- if so that would suggest that the file listings in sheet F2 contain the name Mason 5 times - how do they differ?

Where my mind is heading...
- can we get a handle on the different Masons in your original file in a way that we can tie them up to the listed files on F2?
- or if the files are listed 5 times in F2 listings can we work backwards from there and marry everything up somehow