DIR Getting File Names From Folder ~ LEARN MS-ACCESS TIPS AND TRICKS

Thursday, June 7, 2018

DIR Getting File Names From Folder

DIR: Getting File Names From a Folder.

Most of us are familiar with the Dir() function from the DOS operating system. Dir() is often the very first command introduced to anyone learning how to use a personal computer. Under Windows, this command still offers several options for retrieving Disk information and displays it in various ways. For example, you can generate a complete list of folders, subfolders, and files on a hard disk with a single command and even redirect that list to a printer or save it to a text file using the redirection symbol (>).

In this article, however, we will focus only on how the Dir() function is used in VBA to read file names from a folder one by one and display them in the Debug window. When you run this function with a folder path as a parameter, it returns the first file name found in that folder. The question then is: how do we retrieve the remaining file names—one after another—from the same folder?

Using the DIR Command in the Debug Window.

We will try the Dir() Function from the Debug Window, and it is easy to understand how to use this function to get a few file names from a folder one after the other.

Open the Microsoft Access VBA Window and then display the Debug Window (Ctrl+G).
Type the following command in the Debug Window and press Enter:
```
? Dir("")
```
Dir() Function with an empty string as a parameter will fetch the first file name from the Current Folder and display it in the Debug window.
Since we have not given a specific folder or pathname in the function parameter, it looks for files in the active folder on the disk.
Now, issue the following command without any parameters to get the next file name in the current folder
```
? Dir()
OR
? Dir
```
Each time you run the DIR() command, it gets the next file from the same folder.
Use a specific Folder Path as the parameter, in place of the empty string, to get files from that particular folder.

Example:

? Dir("D:\Documents\")
OR
? Dir("D:\Documents\*.*")

If the folder D:\Documents\folder contains no files, the above command will return an empty string. Running the DIR command a second time will result in an error.

There is an optional second parameter to the DIR() Command that we have not used in the above examples. Since this is a DOS Command executed in its own window, you can specify this second parameter to show its normal window(vbNormal) or hide the execution window (vbHidden), among other options available.

A VBA Wrapper Function for the DIR Function.

Written a small function for you to list all the files in a folder in the Debug Window.

Public Function TestDir(ByVal strFolder As String) As String
'Function Usage e.g.: TestDir "D:\Documents\"
Dim j As Integer, strFile As String
'files counter
j = 1
'Run the function with the specified folder in a hidden window
strFile = Dir(strFolder, vbHidden)
'Next steps of Dir() function is nested in a loop
'to read all the files and print in the Debug Window

Do While Len(strFile) > 0
 Debug.Print j & ":" & strFile
 j = j + 1
 strFile = Dir()
Loop
End Function

Call the function from the Debug Window and give the Folder path as the parameter.

? TestDir("D:\Documents\")
OR
? TestDir("D:\Documents\*.*")

All the files from the specified folder are listed in the Debug window along with the serial number. After reading and printing the last file in the folder, the DIR() function is called one more time until it returns an empty string. At this point, the Do While condition evaluates to False, and the program stops.

If you need only a specific File Type, then you may specify the parameter with the file type extension.

Example:

? TestDir("D:\Documents\*.xls")

The above example retrieves only Excel files and prints them in the Debug window.

I have used the terms Function and Command interchangeably. The Dir() procedure is referred to as a Function in VBA reference documents and as a Command in Disk Operating System (DOS) documents. Both terms refer to the same operation, performed in different environments.