By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
449,315 Members | 1,672 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 449,315 IT Pros & Developers. It's quick & easy.

How to read Excel file names from Access?

P: 49
I was wondering how I could read excel file names in a particular folder using Access VBA. I need to compare the file names in that folder with what I have in a field of Access table.
Feb 25 '11 #1
Share this Question
Share on Google+
2 Replies


beacon
100+
P: 579
Hi Ehsan,

You can use VBScript from inside VBA to read the file names. I've included a sample script below that you can add to a new module in VBA.

The Debug.Print will print the names of the files in the Immediate Window, so you'll likely want to change this part to have it do something more meaningful, like add the file names to a variable to make comparing them to your tables easier.

Expand|Select|Wrap|Line Numbers
  1. Sub ReadFiles()
  2.  
  3.     Dim fso, root, file, files, path
  4.  
  5.     'you may have to use the complete path if you're
  6.     'trying to access a folder on a server instead of 
  7.     'just referencing a drive letter.
  8.  
  9.     path = "Your path here"
  10.  
  11.     Set fso = CreateObject("Scripting.FileSystemObject")
  12.  
  13.     Set root = fso.GetFolder(path)
  14.  
  15.     Set files = root.files
  16.  
  17.     For Each file In files
  18.         Debug.Print file.Name
  19.     Next
  20.  
  21. End Sub
  22.  
If you have multiple folders with files in it that you need to check, you can add a recursive step to the code I provided by setting the root equal to the subfolder and looping through each with an additional call to the ReadFiles subroutine.

Hope this helps and good luck,
beacon
Feb 25 '11 #2

P: 49
Hi thanks for the response.
The thing with my file names is that they are long and I only need to read the date from the names. So I have something like this: ALBERTA Daily Report-JULY 08 AE.xls or ALBERTA Daily Report 2008-07-10.xls and I only need to read the July date and compare that to a column named Date in my Access table. As the name implies these reports come daily so I need to compare all of them within each neighbourhood folder and compare that to my Date field in the Access table. I used the following code for opening the files for transfer into my Access table:
Expand|Select|Wrap|Line Numbers
  1.  
  2. strButtonCaption = "Import"
  3. strDialogTitle = "Select The appropriate Daily Report to Import"
  4.  
  5.   Set fd = Application.FileDialog(msoFileDialogFilePicker)
  6.   fd.InitialFileName = "\\cepfrmt11\Drainage\Construction\Engineering\Upgrading Group\Mature Neighbourhood Rehab Drainage\2. CCTV\2.06 Daily Reports"
  7.  
  8.  
  9.  With fd
  10.     .AllowMultiSelect = False
  11.  
  12.     If .Show = -1 Then
  13.  
  14.         For Each vrtSelectedItem In .SelectedItems
  15.         sOutput = vrtSelectedItem
  16.         Next vrtSelectedItem
  17.  
  18.         For Each vaFileName In .SelectedItems
  19.  
  20.         stMessage = stMessage & vbCr & vaFileName
  21.         Next vaFileName
  22.         MsgBox stMessage
  23.  
  24.  
  25.     Else
  26.  
  27.     End If
  28.  
  29.    End With
  30.  
  31.  
  32. DoCmd.Hourglass True
  33.  
  34.  
  35.  
  36.  
Feb 28 '11 #3

Post your reply

Sign in to post your reply or Sign up for a free account.