Running CommandCam from Excel using VBA

Reader Percy Kumah wrote in with a query about running CommandCam (my free command line image capture utility) from MS Access using VBA. Since I don’t have Access installed myself, I tried it in Excel instead (also using VBA).

Here’s how I did it.

Firstly, I created a simple spreadsheet with a command button and an image object:

Then I added a subroutine for the “Take Snapshot” button:

By the way, if you want the Shell command to run without opening a console window, just replace “vbNormalFocus” with “vbHide” in the code. I deliberately made the console window visible so that I could see what was going on while writing the program, but it works fine if you keep it hidden.

To keep things simple, I copied the file “CommandCam.exe” (which can be downloaded from the CommandCam web page) into the same directory as the Excel file:

When the “Take Snapshot” button is clicked, the subroutine performs the following steps:

1. Before doing anything else, the current directory is set to the same location as the Excel file. The file “CommandCam.exe” is already in this directory and the file “image.bmp” will also be stored there.
2. The “Kill” command is used to delete the file “image.bmp” if it already exists.
3. The subroutine then waits until the file “image.bmp” is deleted.
4. Once the file “image.bmp” is gone, the subroutine uses the Shell command to run CommandCam. Command line arguments for CommandCam can be included within the inverted commas if required (e.g. to select a specific camera or include a delay before taking the snapshot).
5. Again, we need to give CommandCam time to take the photo and store it to a file, so at this point the subroutine just keeps checking to see if the file exists yet.
6. Once the file exists, an additional short delay is required to give CommandCam time to finish writing data to the file and close it.
7. Finally, the image is loaded from the file into the Picture member of the Image1 object (that’s the image object on the spreadsheet).

Finally, in case you want to cut and paste the subroutine code, here it is again:

Private Sub SnapshotButton_Click()
    Dim RetVal
    
    ' Make sure the current directory is set to the one
    ' where the Excel file is saved
    ChDir (ActiveWorkbook.Path)
    
    ' First, delete image file if present
    Kill ("image.bmp")
    
    ' Now, wait until image file is definitely gone
    While Dir("image.bmp") > ""
    Wend
    
    ' Capture new image
    RetVal = Shell("CommandCam.exe", vbNormalFocus)
    
    ' Wait until image file is definitely there
    While Dir("image.bmp") = ""
    Wend
    
    ' Short delay to let new file finish saving
    Application.Wait (Now + TimeValue("00:00:01"))
    
    ' Load new image into image object on spreadsheet
    Image1.Picture = LoadPicture("image.bmp")
End Sub