asked on

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?

Rey Obrero (Capricorn1)

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

AccessGuy1763

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.

dgmoore1

ASKER

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

dgmoore1

ASKER

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.

ASKER CERTIFIED SOLUTION

IrogSinta

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dgmoore1

ASKER

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

IrogSinta

That's what the Wildcards (*) in the DIR statement are for. As long as the company name is in the filename, it will work.

dgmoore1

ASKER

Let me give it a try - thanks

dgmoore1

ASKER

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!