Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Microsoft DOS Commands in VBA

Continued from last week’s Article: Disk Operating System Commands in VBA.

Once we determine the presence of a file in a folder with the Dir() Command we can do certain operations on the file, like opening that file in it’s parent application through the Shell() Command or make a copy of that file to a different location with the FileCopy() Command or delete it with the Kill() Command.

Example-1: Check for the presence of a text file in a folder and if found open it in Notepad.exe

Public Function OpenTextFile()
Dim txtFilePath As String
Dim NotePad As String

   txtFilePath = "C:\msaccesstips\htaccess.txt"
   NotePad = "C:\Windows\System32\Notepad.exe"

If Dir(txtFilePath, vbNormal) = "htaccess.txt" Then
   Call Shell(NotePad & " " & txtFilePath, vbNormalFocus)
Else
   MsgBox "File: " & txtFilePath & vbcr & "Not Found...!"
End If

End Function

Example-2: Make a copy of the file with the FileCopy() Command.

Public Function CopyTextFile()
Dim SourcefilePath As String
Dim TargetFilePath As String

   SourcefilePath = "C:\msaccesstips\htaccess.txt"
   TargetFilePath = "C:\New Folder\htaccess.txt"

If Dir(SourcefilePath, vbNormal) = "htaccess.txt" Then
   FileCopy SourcefilePath, TargetFilePath
   MsgBox "File copy complete."
   
Else
   MsgBox "File Not Found...!"
End If

End Function

Example-3: Find and Delete a File from specific location on Hard Disk.

Public Function DeleteFile()
Dim FilePath As String, msgtxt As String

   FilePath = "C:\New Folder\htaccess.txt"

If Dir(FilePath, vbNormal) = "htaccess.txt" Then
   msgtxt = "Delete File: " & FilePath & vbCr & vbCr
   msgtxt = msgtxt & "Proceed...?"
   If MsgBox(msgtxt, vbYesNo + vbDefaultButton2 + vbQuestion, "DeleteFile()") = vbNo Then
      Exit Function
   End If
   Kill FilePath
   MsgBox "File: " & FilePath & vbCr & "Deleted from Disk."
   
Else
   MsgBox "File: " & FilePath & vbCr & "Not Found...!"
End If

End Function

Dir() Function also can be used for checking the presence of a folder in preparation for creating a new folder in a particular location on the Hard Drive.

The following Command checks for the presence of a particular folder on C: drive:

strOut =  Dir("C:\Developers\Projects", vbDirectory)

The second parameter vbDirectory tells the Dir() command what to look for and if the folder Projects found under C:\Developers folder, then the folder name Projects returned in the strOut variable, otherwise returns an empty string.

The MkDir() Command can be used for creating a new folder if the Projects folder doesn't exists.

Let us write a small program to check the presence of Projects folder and if it doesn’t exists then let us create the folder.

Public Function CreateFolder()
Dim folderPath As String
Dim msgtxt As String

folderPath = "C:\Developers\Projects"

If Dir(folderPath, vbDirectory) = "" Then
   msgtxt = "Create new Folder: " & folderPath & vbCr & "Proceed ...?"
   If MsgBox(msgtxt, vbYesNo + vbDefaultButton1 + vbQuestion, "CreateFolder()") = vbNo Then
      Exit Function
   End If
   MkDir folderPath
   If Dir(folderPath, vbDirectory) = "Projects" Then
      msgtxt = folderPath & vbCr & "Created successfully."
      MsgBox msgtxt
   Else
      msgtxt = "Something went wrong," & vbCr & "Folder creation was not successful."
      MsgBox msgtxt
   End If
Else
   msgtxt = folderPath & vbCr & "Already exists."
   MsgBox msgtxt
End If

End Function

Dir() Command can be used to check the Volume label of a Disk Drive.

The following command, run directly from the Debug window, gets the Volume Label of the Hard Drive, if exists, otherwise returns an empty string:

? Dir("D:", vbVolume)

Result: RECOVERY

Share:

Disk Operating System Commands in VBA

The first edition of Microsoft Disk Operating System (MS-DOS 1.0) was launched in 1982.  The first edition of Microsoft Windows 1.0 Operating System, Disk Operating system with Graphical User Interface (GUI), released on November 20, 1985 – Source: www.wikipedia.org.  The Disk operating System Version under Windows7 is 6.1.7600.

Disk Operating System Commands (both Internal and External) are directly used under Command Prompt for managing Files, Folders on Disks and for retrieving information on them.

For example, the Directory Command (Dir /S/B/P) will provide a list of all files with full Path Names (like C:\My Documents\New Folder\Resume.doc) from your C: Drive and display them on screen, one page at a time. 

Let us try an example.

Click on the Start Menu.

Type cmd and press Enter Key, DOS Command window will open up with the Command Prompt C:\>.  Type the following Command to display a list of Folders/Files from your C: drive, one page at a time.  You must press a Key to advance the list from one page to the other.

Warning:  The list of Folders/Files on your C: drive will run into hundreds of pages.  Press Ctrl+C (break the command) to terminate the list from displaying further.

C:\> Dir /S/B/P

C:\> is the command prompt

Dir (command stands for Directory)

Command switch /S includes Files in Sub-directories also in the output.

Command switch /B provides a bare-formatted list of files, i.e. gives only file path names without creation date, file sizes or any other information of files.

Command switch /P displays the output on Screen one Page (one screen full) at a time.  Needs to press a Key on the Keyboard to advance the list of files to the next Page.

If you want to save the entire list into a text file, without page breaks, issue the following command with the output redirection symbol (>) with a text file name.  The re-direction symbol will send the output of the Directory command to a specified text file, without displaying them on screen.

C:> Dir /S/B > myDirList.txt

Note: Leave a space on either side of the > symbol.

You may open myDirList.txt file in Notepad and check the contents. You can display the contents of myDirList.txt file with the following DOS Command:

C:\> TYPE myDirList.txt

Press Ctrl+C to stop the runaway display. TYPE command displays the contents of a text file on screen. But, it will not display the output one screen full at a time. To do that we can seek the help of another DOS command: MORE with the use of piping symbol (|).

C:\> TYPE myDirList.txt | MORE

In the above command we are using the piping symbol (|) to join the TYPE filename.txt | MORE commands to get the required output. The TYPE command reads the text file contents and pass it on to the next command MORE, without directly sending the output to the Screen. MORE Command takes it's input from TYPE command, through the pipe, and displays it one screen-full at a time. Press SPACEBAR to display the next screen-full of text.

OR

C:\> MORE < myDirList.txt

If > (greater than) symbol is known as re-direction symbol in DOS then < (less than) symbol is known as Source symbol for the MORE command. MORE command reads data from the file name given immediately after the Source Symbol (<) and displays one screen-full at a time.

Let us come back to the Dir Command, it is available in VBA also. But it is used for a different purpose. We can use this command to check the presence of a particular file or the presence of any file in a folder. The usage of this command is as shown below:

strOutput = Dir("C:\My Documents\Resume.doc", vbNormal)

The Dir Command checks for the presence of Word File Resume.doc in Folder C:\My Documents, if found then the file name 'Resume.doc' is returned in strOutput Variable, otherwise it will return an empty string.

strOutput = Dir("C:\My Documents\*.*", vbNormal)

This command will get the first file name and save it in strOutput Variable. You may try out this command in the Debug Window directly, like:

? Dir("C:\My Documents\*.*")

This will print the first file name found in the folder C:\My Documents in the Debug Window. To get subsequent file names from the same folder you can run the command without any parameters to the function, like:

? Dir()

Note: First time when you run this Command you should provide a Path as parameter otherwise it will end up with error.

Place the insertion point on the Dir() Command and press F1 to display the details of this Command in Access Help Documents.

There are other interesting Windows Operating System Commands like ChDrive, ChDir, MkDir, RmDir etc. and we will learn their usage in VBA Next week.

Share:

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 How Tos Functions MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet Class Module MS-Access Scurity MS-Access and Internet Queries msaccess reports Array External Links msaccess tips Accesstips Menus and Toolbars Objects Downloads MsaccessLinks Process Controls Property Art Work Event Collection Object Controls WithEvents msaccess How Tos Graph Charts VBA msaccessQuery Combo Boxes List Boxes Command Buttons Data Emails and Alerts Form Query Report Calculation Command Button Custom Wizards DOS Commands Data Type Dictionary Object ms-access functions msaccess functions msaccess graphs msaccess reporttricks Item Object Reference RaiseEvent msaccessprocess security advanced Access Security Add Custom Functions Field Type Fields Form Instances Key Macros Menus SubForm Top Values Variables msaccess email msaccess menus progressmeter Access2007 Auto-Number Copy Expression Join Methods Microsoft Numbering System Records Recordset Security Split Table Time Difference Utility Workgroup Wrapper Classes database ms-access msaccess wizards 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 Excel 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 function hyperlinks iSeries Date iif msaccess msaccess alerts pdf files reference restore switch toolbar updating upload

Featured Post

WithEvents and Report Line Highlighting

Introduction This is really a re-run of an earlier Post: Highlighting Reports published during August, 2007.  The full Code was written on ...

Labels

Blog Archive

Recent Posts