• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1100
  • Last Modified:

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?
1 Solution
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
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


      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.
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.

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

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.
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

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.

That's what the Wildcards (*) in the DIR statement are for.  As long as the company name is in the filename, it will work.
dgmoore1Author Commented:
Let me give it a try - thanks
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!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now