Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

DIR Getting File Names From Folder

Introduction.

We all know Dir() Function from the time of Windows DOS Operating System.  This is the first Command introduced to those who sit on a Personal Computer to learn how to use Computers.  This Command have several options, to get the output from the disk in so many ways, under Windows Operating System.  You can take a full list of Folders, Sub-folders and Files from the hard disk in a single command.  The entire list can be sent to a Printer or save them into text file with the use of redirection symbol (>).

We are not going to use all those options here.  We will see how Dir() Function used in VBA to read file names from a Folder one by one and display them in Debug Window.  Every time we run this function with a Folder Path as parameter it returns the first file name from the folder.  Now, the question is how to get the next few file names or all the files one-by-one from the Folder.


Using DIR Command in Debug Window.

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

  1. Open Microsoft Access VBA Window and then display Debug Window (Ctrl+G).
  2. Type the following command in the Debug Window and press Enter Key:
    ? Dir("")

    Dir() Function with an empty string as parameter will fetch the first file name from the Current Folder and display it in the debug window. 

    Since, we have not given any specific folder name in the function parameter it looks for files in the active folder on the disk.

  3. Now, issue the following Command without any parameter to get the next file name in the current folder
    ? Dir()
    OR
    ? Dir
  4. Each time you run the DIR() command it will get the next file from the folder.
  5. Use a specific Folder Path as parameter, in place of the empty string to get files from that particular folder.
  6. Example:
    ? Dir("D:\Documents\")
    OR
    ? Dir("D:\Documents\*.*")
    

If D:\Documents\ folder doesn't have any files in it then the above command will return and empty string. If you go further and execute the ? Dir command it will end up with an error message.

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


A VBA Wrapper Function for DIR Function.

I have 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 by giving the full path of the Folder as parameter.

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

All the files from the specified folder will be printed with a serial number in the debug window. After reading and printing the last file from the folder the Dir() function executes one more time and end up with an empty string. The Do While condition will prove false and the program stops.

If you need only specific Type of Files to be read and displayed then you may specify the parameter with the file type extension.

Example:

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

The above example will read only Excel files and print in the Debug window.

I have used the term Function and Command interchangeably. Dir() is referred to as a Function in VBA reference documents and as Command in Disk Operating System documents, both refers to the same operations done in different environments.

Share:

No comments:

Post a Comment

Comments are subject to moderation before publishing.

Translate



PageRank
Subscribe in a reader
Your email address:

Delivered by FeedBurner

Search

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email

Labels

Forms Functions How Tos MS-Access Security Reports Class Module msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Array External Links Queries msaccess reports msaccess tips Accesstips Downloads Objects Property Collection Object Event Menus and Toolbars Controls MsaccessLinks Process Controls WithEvents Art Work msaccess How Tos Combo Boxes Graph Charts List Boxes VBA msaccessQuery Command Buttons Dictionary Object Form Report Calculation Command Button Data Emails and Alerts Query RaiseEvent Custom Functions Custom Wizards DOS Commands Data Type Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Item msaccessprocess security advanced Access Security Add Auto-Number Field Type Fields Form Instances Key Macros Menus SubForm Top Values Variables msaccess email msaccess menus progressmeter Access2007 Copy Excel Expression Join Methods Microsoft Numbering System Records Recordset Security Split Table Time Difference Utility Workgroup Wrapper Classes database function ms-access msaccess wizards reference text tutorial vba code Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Conditional Formatting Data Filtering Defining Pages Diagram Disk Dynamic Lookup Error Handler Export External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Tables Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif msaccess msaccess alerts pdf files restore switch toolbar updating upload

Featured Post

Opening Access Objects from Desktop

Frequently Used Methods. Set the Form Name in Display Form Option of Current Database in Access Options . BIG DEAL!, this is the first t...

Labels

Blog Archive

Recent Posts