Choosing a File from a List

DatabaseLessons.com

'Serving the Microsoft® Access
Community since 1997'

News/Blog

Tables

Queries

Forms

Reports

Modules

Miscellaneous

Subscribe

Free Samples

Videos

Services

Links

About Us

Choosing a File from a List

I built an application recently that needed to present the user with a list of Excel file templates to choose from. The program then filled in the necessary data, using techniques from my "Sending Data to Excel" series (coming soon!).

(article continues after sponsor spot)

The list of available files was presented in a list box, and the user clicked on the one they wanted.

The steps I took were:

create a temporary table to hold the list of files (one time process)
empty the temp table
fill the table using the Dir function
requery the Listbox that shows the files

The first time you use the Dir function in Access VBA, it requires a file name (or wildcard version) as well as the folder (directory) path. If using a wildcard, then the subsequent calls to Dir will return the next file that matches. When there are no more files, the function returns an empty string.

Here is the code that I used.

NOTE: All the VBA code segments on the Database Lessons site assume that you have DAO references active. If you are not sure what this means, and you are using Microsoft Access 2000 or higher, click here.

Dim MyFile As String
Dim rst As Recordset
Dim db As Database

Set db = CurrentDb
Set rst = db.OpenRecordset("select * from [_tmpFileNames]")

db.Execute ("delete * from [_tmpFileNames]")

'--- Returns first file from list of available files
MyFile = Dir("C:\MyExcelTemplates\*.xls")

'--- add it to the temp table
rst.AddNew
rst!Filename = MyFile
rst.Update

'--- Call Dir again without arguments to return the next file
Do While MyFile <> ""     '--- do until no more files
   MyFile = Dir              '--- Get next entry.
   If MyFile <> "" Then
      rst.AddNew
      rst!Filename = MyFile
      rst.Update
   End If
Loop

Me.lstFileNames.Requery    '--- refresh list of file names in list box

Further Suggestions:

In the original application that I created, the templates are actually in the same folder as the database, so I have VBA code that determines the name of the folder and use that instead of the hard-coded "C:\MyExcelTemplates\".

Happy Coding

Note: This web site dedicated to MS Access database users is an independent publication of Richard W. Killey and is not affiliated with, nor has it been authorized, sponsored, or otherwise approved by Microsoft® Corporation.