Archive for July, 2012

July 13th, 2012

VBA – List of files in a specified Folder or Directory

Ever needed to extract a list of files within a folder/directory? Ever needed to iterate through the files within a given folder/ directory? Below is a simple illustration of how you can do it using the Dir() function. Many examples commonly utilize the FileSystem object, when in reality there is no need to use an external library to do this simple task! As they say Keep It Simple Stupid (the KISS philosophy).

'---------------------------------------------------------------------------------------
' Procedure : fListDirFiles
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Return a list of files in a given directory
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sPath     : Full path of folder to examine with trailing \
' sFilter   : specific file extension to limmit search to, leave blank to list all files
'
' Usage:
' ~~~~~~
' fListDirFiles("C:\Users\Daniel\Documents\") 'List all the files
' fListDirFiles("C:\Users\Daniel\Documents\","xls") 'Only list Excel files
' fListDirFiles("C:\Users\Daniel\Documents\","doc") 'Only list Word files
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2012-Jul-13             Initial Release
'---------------------------------------------------------------------------------------
Function fListDirFiles(sPath As String, Optional sFilter As String = "*")
On Error GoTo Error_Handler
    Dim sFile As String
 
    sFile = Dir(sPath & "*." & sFilter)
        Do While sFile <> vbNullString
            If sFile <> "." And sFile <> ".." Then
                    Debug.Print sFile & " was found"
                    'Do something with the found file
            End If
            sFile = Dir 'Loop through the next file that was found
        Loop
 
Error_Handler_Exit:
    On Error Resume Next
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occured." & vbCrLf & vbCrLf & _
            "Error Number: " & Err.Number & vbCrLf & _
            "Error Source: fListDirFiles" & vbCrLf & _
            "Error Description: " & Err.Description, _
            vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function

Share and Enjoy

  • Google Plus
  • Facebook
  • LinkedIn
  • Twitter
  • Email
  • Print

CategoryMS Access VBA Programming, MS Excel VBA Programming, MS Office, MS Word VBA Programming | CommentNo Comments »

Tags:
July 10th, 2012

VBScript – Determine Executable Path for Given Application

Have you been trying to develop a flexible method of determining the path to launch an application. You quickly learn that software developers, in some instances, make it indredibly difficult for us to create a simple static path for the executable directory.

For instance, say you create a database and then deploy it, you can’t just use a static path to launch it, like:

C:\Program Files\Microsoft Office\OFFICE 11\MSACCESS.EXE

because this will work only with Office 2003. What happens if your user is using 2007, 2010, 2000, …???

So what can one do? Well, it is possible to create a series of if statements, to check and see if a 2010 folder exists, if not, then check for 2007, and so on, but this once again rellies on a series of static enties that you make as a developer. For typical installation this would work, but what about that special user that didn’t install Office in the typical location? Your script will fail, no and if or about it!

So What Can One Do?! Actually, the solution is quite straight foward and works for most every program. All we need to do is check with the registry as to where the program was installed! That’s right, let’s just ask the computer, “where did you put the application”? The beauty is in its simplicity.

For MS access, we only need to do the following

Dim WSHShell
Set WshShell = Wscript.CreateObject("Wscript.Shell")
MsgBox WSHShell.RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\MSACCESS.EXE\")

Obviously, instead of sending the result to a msgbox we could assign it to a variable and use it to actually launch a database.

What other programs can we use this technique with? You name it: Excel, Groove, Internet Explorer, FireFox, Info Path, MS Access, One Note, Outlook, Power Point, Word, … and the list goes on. Below is but a few examples of other programs.

Dim WSHShell
Set WshShell = Wscript.CreateObject("Wscript.Shell")
'MS Excel
MsgBox WSHShell.RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\excel.exe\")
'Groove
MsgBox WSHShell.RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\GROOVE.EXE\")
'Internet Explore
MsgBox WSHShell.RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\IEXPLORE.EXE\")
'Info Path
MsgBox WSHShell.RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\infopath.exe\")
'MS Access
MsgBox WSHShell.RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\MSACCESS.EXE\")
'MS One Note
MsgBox WSHShell.RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\OneNote.exe\")
'MS Outlook
MsgBox WSHShell.RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\OUTLOOK.EXE\")
'PowerPoint
MsgBox WSHShell.RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\powerpnt.exe\")
'MS Word
MsgBox WSHShell.RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\Winword.exe\")
'WordPad
MsgBox WSHShell.RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\WORDPAD.EXE\")
'Write
MsgBox WSHShell.RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\WRITE.EXE\")

So you can simply test out other application by using the appropriate executable or use regedit navigate to the “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths” key and review the list of application present on your system.

But as you can see, with but 3 little lines, we can have a flexible way to get the path to our executable. No more guessing, no more messing!

Share and Enjoy

  • Google Plus
  • Facebook
  • LinkedIn
  • Twitter
  • Email
  • Print

CategoryMS Access General Information, VBScript & Batch Files | CommentNo Comments »

Tags:
July 7th, 2012

VBA – Export to Text File

Below is a similar function to my AppendTxt function, expect this one overwrites any existing data in the destination text file instead of appending it like in the AppendTxt function.

'---------------------------------------------------------------------------------------
' Procedure : OverwriteTxt
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Output Data to an external file (*.txt or other format)
'             ***Do not forget about access' DoCmd.OutputTo Method for
'             exporting objects (queries, report,...)***
'             Will overwirte any data if the file already exists
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFile - name of the file that the text is to be output to including the full path
' sText - text to be output to the file
'
' Usage:
' ~~~~~~
' Call OverwriteTxt("C:\Users\Vance\Documents\EmailExp2.txt", "Text2Export")
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2012-Jul-06                 Initial Release
'---------------------------------------------------------------------------------------
Function OverwriteTxt(sFile As String, sText As String)
On Error GoTo Err_Handler
    Dim FileNumber As Integer
 
    FileNumber = FreeFile                   ' Get unused file number
    Open sFile For Output As #FileNumber    ' Connect to the file
    Print #FileNumber, sText                ' Append our string
    Close #FileNumber                       ' Close the file

Exit_Err_Handler:
    Exit Function
 
Err_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: OverwriteTxt" & vbCrLf & _
           "Error Description: " & Err.Description, vbCritical, "An Error has Occured!"
    GoTo Exit_Err_Handler
End Function

Share and Enjoy

  • Google Plus
  • Facebook
  • LinkedIn
  • Twitter
  • Email
  • Print

CategoryMS Access VBA Programming, MS Excel VBA Programming, MS Word VBA Programming | CommentNo Comments »

Tags:
July 6th, 2012

VBA – Count files in Folder/Directory

Below is a simple function that will return the count (number) of files contained within a supplied folder path.

'---------------------------------------------------------------------------------------
' Procedure : FlrFileCount
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Return a count of the number of files in a specified folder/directory
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFileFlr  : Full path of the folder to count the number files within
'
' Usage:
' ~~~~~~
' FlrFileCount("C:\Users\Esther\Documents\cdo")  ::  Will return a numeric value
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2010-Jan-11             Initial Release
'---------------------------------------------------------------------------------------
Function FlrFileCount(sFileFlr As String) As Long
On Error GoTo Error_Handler
    Dim fso As Object
    Dim flr As Object
 
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set flr = fso.GetFolder(sFileFlr)
 
    FlrFileCount = flr.Files.Count
 
Error_Handler_Exit:
    On Error Resume Next
    Set flr = Nothing
    Set fso = Nothing
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occured." & vbCrLf & vbCrLf & _
            "Error Number: " & Err.Number & vbCrLf & _
            "Error Source: FlrFileCount" & vbCrLf & _
            "Error Description: " & Err.Description, _
            vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function

Share and Enjoy

  • Google Plus
  • Facebook
  • LinkedIn
  • Twitter
  • Email
  • Print

CategoryMS Access VBA Programming, MS Excel VBA Programming, MS Word VBA Programming | CommentNo Comments »

Tags: