Access VBA: Comparing filenames in folder to list of names in table

I have a folder containing a number of files whose filename includes the name of a company. There may be more than one file per company in the folder (e.g., Report From Company 1_1.xlsx, Report From Company 1_2.xlsx, Report From Company 2.xlsx, etc.).

I have a table containing company names (Company 1, Company 2, ... Company n).

I need to determine whether each company has submitted at least one file to the folder (the companies email these files to a Sharepoint library, which I have mapped to a local drive to make it easier to work with).

Can anybody suggest an efficient way to compare the company names in the table with the company names in the filenames in the folder?
dgmoore1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
you can use the following codes

dim rs as dao.recordset, xlFile as string, folderPath as string
folderPath=<full path to the folder>  
set  rs=currentdb.openrecorset("tableCompany")

do until rs.eof
xlFile=dir(folderPath & "\*.xlsx")
while xlFile<>""
      if Instr(xlFile, rs!CompanyName) then
           debug.print "Company : " & rs!company & " have file in the folder"
      end if
      xlfile=dir
wend
rs.movenext
loop
0
AccessGuy1763Commented:
Well, if I had to do this I think I would create a function that I would call from a query which would check for the file.  The column setup would look something like this:

HasSubmittedFile: fcnCheckForSubmittedFile([CompanyName])

Open in new window


For the function I think you can get by just using Dir() for this:

Public Function fcnCheckForSubmittedFile(inCompanyName as string) as boolean

   If Dir("C:\InsertYourFolderPathHere\*" & inCompanyName & "*") <> "" then

      fcnCheckForSubmittedFile = True

   Else

      fcnCheckForSubmittedFile = False

   End if

End Function

Open in new window


The function needs to be added to a global module in order to be called from a Query.
0
dgmoore1Author Commented:
I found a better solution for my case. I parse the company names from the filenames and put them in a temp table, and then compare the temp table to my table of company names by query. This is simpler and works better than iterating through the folder and applying logic.

Thanks
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

dgmoore1Author Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for dgmoore1's comment #a39491221

for the following reason:

My approach works better for my particular situation.
0
IrogSintaCommented:
I posted an objection because I have another solution for you that's much much simpler; however, if still want to go with your solution then just select your solution again as the answer.

Create a query similar to the following and it will display 2 columns, the company name and whether they have a file:
SELECT [CompanyName], Dir("C:\PathToFolder\*" & [CompanyName] & "*.jpg")<>"" AS HasFile
FROM tblCompanies

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dgmoore1Author Commented:
It's not quite that simple -  the filename is not just CompanyName.xlsx - it contains data other than just the company name (like "Project 1 [Company 1 Name] Report 091313.xlsx", "Project 2 [Company 3 Name] Report A 092513.xlsx",etc.) and so it has to be parsed from the filename before it can be compared to the Company Name table.

Thanks
0
IrogSintaCommented:
That's what the Wildcards (*) in the DIR statement are for.  As long as the company name is in the filename, it will work.
0
dgmoore1Author Commented:
Let me give it a try - thanks
0
dgmoore1Author Commented:
After tracking down an error "undefined function Dir in expression", I was able to get your SQL to work. Great solution - teaches an old dog a new trick.

Turns out that in order to get Dir() to work in a query (Access 2010, Win7 Pro) I have to use a wrapper function Dir2 in the query: Dir2 = Dir(Pathname). This works fine, but using Dir(Pathname) directly in the query throws the error. I have no idea why this is, but found a reference to it buried in a TechNet Archive post.

In any case, many thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.