Maintaining your Virtual Machines – Windows XP Mode

Did you know you can actually maintain your Virtual Machines? I sure didn’t! I have been using Windows Virtual PC / Windows XP Mode for a little while now and never thought remotely about maintenance. It worked, so why concern myself.

That said, over time I noticed that some of my Virtual Machine were bloating in size when I thought of the contents. These Virtual Machines were GB larger than what I thought they should be and thus hogging valuable Hard Drive real estate for no reason. As such, I started nosing around and discovered that you can in fact Compact a Virtual Machine.

Sadly, once again Microsoft has buried something useful under layers of menus. No clue why they chose to hide such a command under the Modify button, but if you dig a little, the option to regain a little hard drive space is indeed there.

Below are the instructions on how to do so.

Open your Virtual Machine Folder (typically found in %UserProfile%/Virtual Machines/)
Select the Virtual Machine you would like to compact
Click on the Settings button in the toolbar immediately above the Virtual Machine Listing (a pop-up dialog will appear)
Select Hard Disk 1 (right-hand side of the dialog)
Click on the Modify Button (Left-hand side of the dialog – another pop-up will appear)
Click on Compact virtual hard disk
Click on Compact
Wait for the the Compaction Success Confirmation (this can take more or less time depending on many factors)
Click Close

Now, I’m not saying you need to do this every week, but after deleting a large amount of data, removing sizeable programs, … from a Virtual Machine, it may be a good idea to perform a quick compaction.

VBA – Remove HTML Tags from a String

I needed to clean some webpage texts for a private project I was working on and created the following function. Today, in a forum question, the subject resurfaced so I dug up my function and am posting it here should it might serve someone else.

So here is a simple function utilizing Regular Expressions to remove/sanitize/extract HTML Tags from the passed string. So you get returned the text and nothing else.

'---------------------------------------------------------------------------------------
' Procedure : RemoveHTML
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Remove any HTML tags and/or comments from a string
' 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:
' ~~~~~~~~~~~~~~~~
' sString   : String to sanitize (remove HTML tags from)
'
' Usage:
' ~~~~~~
' RemoveHTML("And then
 some")
'           Returns: And then some
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2009-Aug-26             Initial Release
' 2         2009-Oct-30             Changed pattern to include comments
'---------------------------------------------------------------------------------------
Function RemoveHTML(sString As String) As String
    On Error GoTo Error_Handler
    Dim oRegEx          As Object

    Set oRegEx = CreateObject("vbscript.regexp")

    With oRegEx
        'Patterns see: http://regexlib.com/Search.aspx?k=html%20tags
        '.Pattern = "<[^>]+>"    'basic html pattern
        .Pattern = "]*>"    'html tags and comments
        .Global = True
        .IgnoreCase = True
        .MultiLine = True
    End With

    RemoveHTML = oRegEx.Replace(sString, "")

Error_Handler_Exit:
    On Error Resume Next
    Set oRegEx = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: RemoveHTML" & vbCrLf & _
           "Error Description: " & Err.Description, _
           vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

Update 2016-08-26

Just learnt about a function, thank to fellow MVP theDBguy, that has existed since Access 2007 that can remove Rich Text formatting from a string. So you may wish to check out the PlainText Method. Its use is very straightforward:

? PlainText("And then
 some
)"

which returns:

And
then
some)

In my brief testing, for better or worse depending on what you are doing, this method seems to retain line feeds.

VBA – Split/Break a Camel Case String

I was having a recent discussion with a fellow MVP and came across a function I developed a while back and thought it could be useful to others. Below is a very simple function which uses a RegEx pattern to break apart a Came Case string into a legible string.

'---------------------------------------------------------------------------------------
' Procedure : RegEx_SplitCamelCase
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Split/Break a Camel Case string
' 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:
' ~~~~~~~~~~~~~~~~
' sString   : Camel Case string to break/split
' sDelim    : Character to use as a spcaer, if omitted will use a space
'
' Usage:
' ~~~~~~
' ? RegEx_SplitCamelCase("SplitCamelCase")
'       Returns Split Camel Case
' ? RegEx_SplitCamelCase("SplitCamelCase", "_")
'       Returns Split_Camel_Case
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2008-05-03              Initial Release
' 2         2022-11-16              Update Function name
'                                   Update Error Handler
'---------------------------------------------------------------------------------------
Public Function RegEx_SplitCamelCase(sString As String, Optional sDelim As String = " ") As String
    On Error GoTo Error_Handler
    Dim oRegEx          As Object

    Set oRegEx = CreateObject("VBScript.RegExp")
    With oRegEx
        .Pattern = "([a-z](?=[A-Z])|[A-Z](?=[A-Z][a-z]))"
        .Global = True
        RegEx_SplitCamelCase = .Replace(sString, "$1" & sDelim)
    End With

Error_Handler_Exit:
    On Error Resume Next
    Set oRegEx = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
        "Error Source: RegEx_SplitCamelCase" & vbCrLf & _
        "Error Number: " & Err.Number & vbCrLf & _
        "Error Description: " & Err.Description & _
        Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
        , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function

VBA – CDO Mail

Although I often refer people to use CDO mail, I have only briefly discussed it in prior postings and I never really gave any usage examples. Today that changes!

So what exactly is CDO Mail and when is it useful?

Collaborative Data Objects – (CDO) is the COM-based interface for accessing Exchange or any other Messaging API (MAPI)-compliant service such as fax services, POP3 services, and so on.Sean McCormick

CDO mail is one of many techniques available to developer to send e-mails.

If you are an MS Access developer you are surely aware of the SendObject method, but it will only allow one attachment and that attachment has to be an Access object (not an external file).

Another very common approach is to use Outlook automation, but with new security impositions by Microsoft it has become unreliable and now requires workarounds. Furthermore, after a recent discussion with fellow MVPs, we find out that having an outdated virus definition can stop longstanding code from working! As such, although powerful, outlook automation simply is not reliable for true production. For production tools, one must then look at integrating tools such as Outlook Redemption, but the down side to this solution is that it requires registering a COM library for it to work.

Which brings us to CDO Mail!

CDO Mail is a technique that binds you directly to your e-mail server to send out email(s).

CDO Mail Pros

No e-mail client (Outlook, Thunderbird, …) need be installed
- Hence you bypass any e-mail client limitations and/or security restrictions
Permits multiple attachments to be included
Permits external documents as attachments
Accepts both plain text and HTML formatted content
Can be used in any programs which uses VBA (MS Access, Excel, Word, PowerPoint, MS Project, …)
Can even be used in VBScript

CDO Mail Cons

Requires knowledge of all the server settings (port, username, password, authentication, …)
Does not keep a copy of sent e-mails so CCing or BCCing yourself becomes useful for archive purposes
Does not permit attaching database objects directly, but you can export them or print them as PDFs and attach those

CDO Mail VBA Code Sample

Below is a sample CDO Mail function I created many moons ago for my own purposes and have tweak slightly over the years.

Continue reading →

MS Access List Table Indexes

Once again, trying to help someone in a forum and quickly put together the following to enumerate a list of indexes for a given table:

'---------------------------------------------------------------------------------------
' Procedure : Table_ListIndexes
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : List the names of the indexes in the specified table
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Microsoft Office XX.0 Access database engine Object Library
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sTbl      : Name of the table to list the names of the Indexes from
'
' Usage:
' ~~~~~~
' Call Table_ListIndexes("tbl_Appointments")
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2014-11-20                  Initial Release
' 2         2022-03-25                  Header Usage Correction, per blog comment
' 3         2022-03-25                  Added field enumeration per Brent Spaulding and
'                                       Tom Wickerath's recommendations
'                                       Renamed function, updated header and error
'                                       handler
'---------------------------------------------------------------------------------------
Function Table_ListIndexes(sTbl As String)
On Error GoTo Error_Handler
    Dim db              As DAO.Database
    Dim tdf             As DAO.TableDef
    Dim tIndex          As DAO.Index
    Dim fld             As DAO.Field

    Set db = CurrentDb
    Set tdf = db.TableDefs(sTbl)

    For Each tIndex In tdf.Indexes
        Debug.Print tIndex.Name
        For Each fld In tIndex.Fields
           Debug.Print , fld.Name
        Next
    Next

Error_Handler_Exit:
    On Error Resume Next
    If Not fld Is Nothing Then Set fld = Nothing
    If Not tIndex Is Nothing Then Set tIndex = Nothing
    If Not tdf Is Nothing Then Set tdf = Nothing
    If Not db Is Nothing Then Set db = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
               "Error Number: " & Err.Number & vbCrLf & _
               "Error Source: Table_ListIndexes" & vbCrLf & _
               "Error Description: " & Err.Description & _
               Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
               , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function

Enumerate a List of Open but Hidden Forms

Once again, in trying to help someone in an Access forum, I came up with the following procedure(s) to be able to identify currently open, but hidden forms.

Option 1 – Print the results to the immediate window

'---------------------------------------------------------------------------------------
' Procedure : ListHiddenOpenFrms
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Generate a list of current open, but hidden forms and print their names to
'             the immediate window.
' 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).
'
' Usage:
' ~~~~~~
' Call ListHiddenOpenFrms
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2014-Nov-19                 Initial Release
'---------------------------------------------------------------------------------------
Function ListHiddenOpenFrms()
On Error GoTo Error_Handler
    Dim DbF     As Form
    Dim DbO     As Object
    
    Set DbO = Application.Forms 'Collection of all the open forms
    
    For Each DbF In DbO    'Loop all the forms
        If DbF.Visible = False Then
            Debug.Print DbF.Name
        End If
    Next DbF

Error_Handler_Exit:
    On Error Resume Next
    Set DbF = Nothing
    Set DbO = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
            "Error Number: " & Err.Number & vbCrLf & _
            "Error Source: ListHiddenOpenFrms" & vbCrLf & _
            "Error Description: " & Err.Description, _
            vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

Option 2 – Return a delimited listing which can be used elsewhere

'---------------------------------------------------------------------------------------
' Procedure : ListHiddenOpenFrms
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Generate a list of current open, but hidden forms and return a delimited listing which 
'             can be used elsewhere.
' 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).
'
' Usage:
' ~~~~~~
' Call ListHiddenOpenFrms
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2014-Nov-19                 Initial Release
'---------------------------------------------------------------------------------------
Function ListHiddenOpenFrms()
On Error GoTo Error_Handler
    Dim DbF     As Form
    Dim DbO     As Object
    Dim Frms   As Variant
    
    Set DbO = Application.Forms 'Collection of all the open forms
    
    For Each DbF In DbO    'Loop all the forms
        If DbF.Visible = False Then
            Frms = Frms & ";" & DbF.Name
        End If
    Next DbF
    
    If Len(Frms) > 0 Then
        Frms = Right(Frms, Len(Frms) - 1)   'Truncate initial ;
    End If
 
    ListHiddenOpenFrms = Frms

Error_Handler_Exit:
    On Error Resume Next
    Set DbF = Nothing
    Set DbO = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
            "Error Number: " & Err.Number & vbCrLf & _
            "Error Source: ListHiddenOpenFrms" & vbCrLf & _
            "Error Description: " & Err.Description, _
            vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

Hopefully this can help someone else out.

CreateObject(“Outlook.Application”) Does Not Work, Now What?

As most developers know, when automating MS Office applications you always start by binding to an existing instance, or creating a new instance if one is not already running. As such, you’d commonly use something like:

    Dim oExcel          As Object

    On Error Resume Next
    Set oExcel = GetObject(, "Excel.Application")    'Bind to existing instance of Excel

    If Err.Number <> 0 Then    'Could not get instance of Excel, so create a new one
        Err.Clear
        On Error GoTo Error_Handler
        Set oExcel = CreateObject("excel.application")
    End If
    On Error GoTo Error_Handler

Now for Excel, Word, PowerPoint, … this works beautifully. However, if you wish to automate Outlook, you will quickly realize that this approach does not work (anymore)!

Continue reading →

MS Access – VBA – Change the Application Printer to a Given Printer Name

Once again trying to help someone in a thread who needed to set the Application Printer to a specific printer name. Now the issue being, and if you are here you are already ware of the problem, is that when you try and set the application printer it is expecting the printer No, not its name! So what is one to do? Below is a simple function which basically loops through the printer collection and checks for the printer name being called, and once it find that one, it sets the application printer to that printer.

'---------------------------------------------------------------------------------------
' Procedure : SwitchAppPrinter
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Change the application printer
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: None required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sPrinterName  : Name of the printer to set the application to use by default
'
' Usage:
' ~~~~~~
' Call SwitchAppPrinter("Snagit 10")
' Call SwitchAppPrinter("Brother MFC-240C")
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2014-10-23              Initial Release
' 2         2019-03-09              Fixed typo (sPinterName -> sPrinterName)
'                                   Updated error handler and function header
'                                   Made function return boolean if successful or not
' 3         2022-11-15              Fixed Typo in Function name
'---------------------------------------------------------------------------------------
Function SwitchAppPrinter(sPrinterName As String) As Boolean
    On Error GoTo Error_Handler
    Dim prtAvailPrinters      As Printer

    For Each prtAvailPrinters In Application.Printers
        If prtAvailPrinters.DeviceName = sPrinterName Then
            Set Application.Printer = prtAvailPrinters
            SwitchAppPrinter= True
            Exit For
        End If
    Next prtAvailPrinters

Error_Handler_Exit:
    On Error Resume Next
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: SwitchAppPrinter" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

It is a simple workaround to Microsoft not having given us any direct way to set the application printer to a given printer name, which let’s be serious, it the way we need to work! Who knows what number a printer is from one system to another. Setting a printer based on an arbitrary number is moronic! Yes I said it!!!

I should also specify that it would probably be a good idea to initially capture the active default printer and set it back once you are done (as you close your database for instance).

As such, you can determine the active default printer with a couple simple lines of code:

Dim sDefaultPrnt As Printer
Set sDefaultPrnt = Application.Printer

Other Resources:

If you are looking for more printer functions, pere_de_chipstick was kind enough to share some of his code in the UtterAccess Code Archive, see:
Select Printers, Configuration, Paper Sizes And Paper Bins For Reports, Any Version

VBA-WMI-Determine If a Process or Program Is Running Or Not

Here’s a simple technique (pulling it out of archive to help answer a thread in a discussion forum) to verify whether or not a program (actually a process) is currently running on a computer, local or remote (if the permissions have been establish to validate such information remotely), using a simple WMI query.

'---------------------------------------------------------------------------------------
' Procedure : WMI_IsProcessRunning
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Determine if a process or Program is running or not
'               Returns: True/False
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Uses Late Binding, so none required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sExeName  : Name of the process to look for
' sHost     : Host computer to query, omit for the local PC
'
' Usage:
' ~~~~~~
' WMI_IsProcessRunning("firefox.exe")
' WMI_IsProcessRunning("outlook.exe")
' WMI_IsProcessRunning("msaccess.exe", "HomePC01")
' WMI_IsProcessRunning("msaccess.exe", "192.168.100.12")
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2010-Feb-03             Initial Release
' 2         2020-08-21              Renamed the Function
'                                   Updated Proc Header
'                                   Code updated
'                                   Updated Error Handler
'---------------------------------------------------------------------------------------
Public Function WMI_IsProcessRunning(sProcessName As String, Optional sHost As String = ".") As Boolean
    On Error GoTo Error_Handler
    Dim oWMI                  As Object    'WMI object to query about the PC's OS
    Dim sWMIQuery             As String    'WMI Query
    Dim oCols                 As Object
    Dim oCol                  As Object

    Set oWMI = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & sHost & "\root\cimv2")
    sWMIQuery = "SELECT * FROM Win32_Process WHERE Name = '" & sProcessName & "'"
    Set oCols = oWMI.ExecQuery(sWMIQuery)
    If oCols.Count <> 0 Then WMI_IsProcessRunning = True

Error_Handler_Exit:
    On Error Resume Next
    Set oCols = Nothing
    Set oWMI = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: WMI_IsProcessRunning" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

There are some other great resources on the subject of WMI to further develop such code. I good starting point is MSDN, see: WMI Tasks: Processes

MS Access – List Back-End Data Sources

Ever taken over a database that was a complete mess and quickly wanted to determine what Back-End data sources were involved? Below is a simple function to enumerate the Back-End data sources in a given database.

Public Sub ListBESources()
    On Error Resume Next
    Dim colTables             As New Collection
    Dim db                    As DAO.Database
    Dim tdf                   As DAO.TableDef
    Dim sBackEnd              As String
    Dim BE                    As Variant

    Set db = CurrentDb

    'Loop through the TableDefs Collection.
    For Each tdf In db.TableDefs
        'Ensure the table is a linked table.
        If Left$(tdf.Connect, 10) = ";DATABASE=" Then
            'Get the path/filename of the linked back-end
            sBackEnd = Mid(tdf.Connect, 11)
            'Ensure we have a valid string to add to our collection
            If Len(sBackEnd & "") > 0 Then
                colTables.Add Item:=sBackEnd, Key:=sBackEnd
            End If
        End If
    Next tdf

    On Error GoTo 0
    Debug.Print colTables.Count & " Data Source(s) found:"
    For Each BE In colTables
        Debug.Print BE
    Next BE
End Sub

Continue reading →