dgmoore1
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?
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?
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:
For the function I think you can get by just using Dir() for this:
The function needs to be added to a global module in order to be called from a Query.
HasSubmittedFile: fcnCheckForSubmittedFile([CompanyName])
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
The function needs to be added to a global module in order to be called from a Query.
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
Thanks
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.
Accepted answer: 0 points for dgmoore1's comment #a39491221
for the following reason:
My approach works better for my particular situation.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
Thanks
That's what the Wildcards (*) in the DIR statement are for. As long as the company name is in the filename, it will work.
ASKER
Let me give it a try - thanks
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!
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!
dim rs as dao.recordset, xlFile as string, folderPath as string
folderPath=<full path to the folder>
set rs=currentdb.openrecorset(
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