searching for files with part of file name

[MM889]

Background: I have a folder full of pdf's that all have a "code" and a year in the name of the file that distinguishes what the file is.
For example:
File_1_HJ8DF_2010.pdf
File_2_HLKSSSF_2011.pdf
File_3_HJ99ISDF_2010.pdf

I am trying to figure out a way using vba in access 2010 to search for the file i am after by using the "code" and the year in the title of the file as search criteria. I have tried using the FileSystem Object in access but havent gotten very far becuase it needs an exact match to the file name in order to find it.

I have also thought about sorting the files into folders per year to eliminate one of the criteria to the search and make things easier. This is becuase there may be a file with the "code" "JS8SD" for 2011 and 2010 so i need to select the one for the year i am working with.

Ideally i would like to return the full path to the file i am looking for so i can do what ever i want with it after that. My programming experiance is limited as i have just started teaching myself vba about a month ago.

 

[mintjulep]

Possible approach:

Load all the file names into the first row of a 2D array.
Loop through the array and parse out the "code" into the corresponding column in the second row.

Loop through the second row looking for what you need. Look in the corresponding first row for the full file name.

Go the the file you need.

Of course you could do all of that on the fly too, without the array.

 

[Andrzejek]

Let's say your files are in C:\Temp and you want to find all files File*2010.pdf:

Dim strMyFile As String

strMyFile = Dir("C:\Temp\File*2010.pdf")

Do While strMyFile <> ""
    MsgBox strMyFile
    strMyFile = Dir
Loop

Have fun.

---- Andy

 

[MM889]

Thanks for the quick replies!

mintjulep - Can you point me towards some info on arrays? I haven't used them yet in any of the programming i have done.

 

[SkipVought]

BTW,

using vba in access 2010...

Forum705

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mintjulep]

VBA's help for whatever application you are using would be a good place to start.

Or Google for "using arrays in VBA".

 

[PHV]

A starting point:

strCcode = "JS8SD"
strYear = "2011"
strFile = Dir("\path\to\folder\*_" & strCode & "_" & strYear & ".pdf")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MM889]

PHV - Have a working solution now, thanks for helping me get started with a little code