473,325 Members | 2,816 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

How to read Excel file names from Access?

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
2 3292
beacon
579 512MB
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
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

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

Similar topics

2
by: dmateer | last post by:
I have the following code in an asp.net page: System.Data.OleDb.OleDbConnection cn = new System.Data.OleDb.OleDbConnection(); cn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data...
12
by: kath | last post by:
How do I read an Excel file in Python? I have found a package to read excel file, which can be used on any platform. http://www.lexicon.net/sjmachin/xlrd.htm I installed and working on the...
0
by: Dodong | last post by:
Hi, i am a new vb6 programmer. I would like to know the vb6 code to read excel file and save it to access database. I am using ADO.
0
by: madhubabumallidi | last post by:
i am designing a web page so that i need to browse a folder and capture all excel file names in that folder, using c#
0
by: Ashish Tiwari | last post by:
Can i read excel file into asp.net 2.0
2
by: ALaurie10 | last post by:
I have an excel file in Accounting Format, my feild names go down the page and the data goes across. There are four values for each feild. After importing my excel file into a table, the feilds are...
2
by: KC-Mass | last post by:
Is it possible to read the spreadsheet names in an Excel workbook with VBA and no Excel?
6
by: provor | last post by:
Hello, I have the following code that I am using when a user presses a button to import an excel file into a table. The code is hard coded to point to the correct table. This works great for this...
0
by: Manesh Pawar | last post by:
Hello All, I need to read a Excel file to Import that data into the database. I can read it When Excel data Is In Tabular Form, but here, Excel data is in Random cells Format. ...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.