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:

Screenshot of ExcelCam spreadsheet

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

Screenshot of ExcelCam Snapshot button subroutine in VBA editor

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:

Screenshot of files in ExcelCam folder

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
This entry was posted in Uncategorized and tagged , , . Bookmark the permalink.

99 Responses to Running CommandCam from Excel using VBA

  1. acraens says:

    This is a really good start for what I want to create. Thanks a lot

  2. Tom says:

    This is great work. I’m trying to found such solution for tablet Acer Iconiu Win8 32bit. I have problems with integrated cameras. External work, internal do not (avicap32.dll thing). If your code works on Acer you should get Nobel prize :). Great work!!!

  3. Tom says:

    And the winner is batchloaf :). No kiding, code workes like charm. I have checked all over internet there is no solution execpt yours..
    Your code works on tablet, me and my friend are tryung very hard to make it with preview in workbook. It isn’t easy so If know what to do please help.

  4. Mike says:

    having a problem with commandcam.exe and need some help. I am trying to run this program on a tablet using office 2013 access. The image.bmp, commandcam.exe and adp file are all in the same folder. if I run this program by itself (outside of an office shell) it works fine. however inside the shell it runs, doesn’t show an error (that I can tell) but the file never appears. I know the camera turns on because it has a light that turns on, retval says it is 0, but no file appears. Help!?

    • batchloaf says:

      Hi Mike,

      It sounds like the working directory must be defaulting to somewhere other than the folder your document is stored in. I have two suggestions:

      1.) Firtly, you should try doing a file search of your entire C: drive to see if you can find where the image files are actually ending up.

      2.) Have you tried specifying a complete path in your CommandCam command line? That way you should be able to control exactly where the file ends up, even if the working directory is set to another location. Specifically, I mean something like this:

      RetVal = Shell("CommandCam.exe /filename C:\myfolder\output.bmp", vbNormalFocus)
      

      Obviously, you would need to change that path to whatever folder you want the image in.

      Please let me know if either of those suggestions produce any results.

      Ted

      • Seudo says:

        Ok, in my case when run “RetVal = Shell(“CommandCam.exe”, vbNormalFocus)” on excel, it send image.bmp to “C:\Users\User\Documents”. I have to put the complete path everywhere
        RetVal = Shell(“C:\PATH\CommandCam.exe /filename C:\PATH\image.bmp”, vbNormalFocus)”
        and works!
        thankyou

  5. Damian says:

    Everything is fine but VBA has problem with this line:
    Image1.Picture = LoadPicture(“D:\Rochstar\AKREDYTACJE\IDENTYFIKACJA1\image.bmp”)
    and occured Run-time error ‘424’: Object required
    Pls help!

    • batchloaf says:

      It sounds like you didn’t create an image object on the spreadsheet before you ran the function. The image object (called “Image1”) must already exist before you press that button. The data in the image object (i.e. the picture it displays) will be replaced by whatever image is grabbed from the camera.

      Ted

  6. Damian says:

    Is there any chance of making a closer picture ??
    You have to change something in the code library or in the camera settings (this is a problem because I do not have this option in your camera in a laptop) ??

    • batchloaf says:

      I’m not quite sure what you mean by a “closer” picture. Do you literally mean more zoomed in? Most web cams don’t include a physical zoom feature. Of course some capture software will let you scale up a section of the image to a larger size, but that’s just a digital effect applied after the image has been captured. I suppose you could do something like that here using Visual Basic to modify the captured image.

      If your camera actually has a physical zoom feature that can be controlled from the PC, then it’s possible that it can be controlled via DirectShow (the Windows video capture API that CommandCam uses), but I haven’t tried that since I don’t own a camera with that feature.

  7. Damian says:

    thx for reply 🙂

    another question: how to save as new file this image.bmp – not overwrite ??

    • batchloaf says:

      There are a couple of ways to do it:

      CommandCam has a command line option to specify the filename. For example,

      CommandCam /filename output.bmp
      

      You could use VB to create a new string for the filename each time.

      Another way to do it is to allow it to continue using the default filename, but include a new shell command around line 24 of the example program to copy the image file to another filename.

      • Edsel says:

        Hi,
        Thank you for this program. This is good. I am a newbie in programming. Can you please help me. How can I stop overwriting the image.bmp and create new file everytime I run it? I am having trouble doing it. My code is below:
        Retval = Shell(“command.exe / filename output.bmp”, vbhide)
        While Dir(“output.bmp”) = “”
        Wend

  8. Pingback: Aquiring a webcam image using VBA - Page 3

  9. Frank Feldman says:

    I’m trying to take photos with a Acer tablet running Windows 8.1 and insert into a Excel 2013 spreadsheet. I’ve downloaded CommandCam and copied the VBA into my project. It works, but lacks some important functionality. It doesn’t provide a preview of what the camera is aimed at. I could only point in the general direction and hope for the best. I would also like to be able to adjust exposure as with the Camera.exe app that is native to 8.1. Is it possible to modify CommandCam to provide that functionality? or is it possible to Shell the windows App from Excel VBA?

    • batchloaf says:

      Hi Frank,

      CommandCam does provide a preview option. Try something like this:

      CommandCam.exe /preview /delay 5000
      

      That should delay for 5 seconds (5000 ms) before taking the snapshot. During that time, a preview window should display what the camera sees.

      Unfortunately, there’s no way to control the exposure using CommandCam. I don’t even know if that’s possible to do using the DirectShow API which CommandCam uses to access the camera. If you need to do that, perhaps the Windows 8.1 Camera.exe program might be a better way to do it?

      Ted

      • Brian says:

        I tried RetVal = Shell(“CommandCam.exe/preview /delay 5000”, vbHide) but it returns “Runtime-error 53” file not found. it works with RetVal = Shell(“CommandCam.exe”, vbHide) but not with the preview… is there a reason why?

      • batchloaf says:

        Hi Brian,

        You should have a space between “CommandCam.exe” and “/preview”.

        The name of the executable file is “CommandCam.exe”. The “/preview” is a command line option and must be separated from the name of the executable file by one or more spaces. Same for “/delay 5000”.

        Ted

    • Brian says:

      It worked! Thank you for the prompt reply! I really appreciate the work youve put it into this! It makes my life so much easier!

  10. Adam says:

    Hey, I ran the code and it’s working, but when I open the bmp file I get “Photo Viewer windows can not open this picture, because the file is probably damaged, corrupted, or is too large.” I tried to save it as jpeg or jpg and the story is the same… Any idea how to fix it?

    You did great work, thaks!

    Adam

    • batchloaf says:

      Hi Adam,

      I haven’t seen that problem before. A couple of questions:

      1. Can the image files be opened in other programs, such as IrfanView, MS Paint, Firefox, whatever?
      2. Have you tried running CommandCam outside Excel to see if the image files it produces can be opened in that case?
      3. What version of Windows are you running? I did some googling and saw descriptions of other people seeing that error message when they tried to open images in Windows 7, even though the same images work fine in Windows XP.

      Ted

      • Adam says:

        Thanks for answer.

        1. No.
        2. Yes and the same error.
        3. It’s W7 and the funny thing is that I tried it on other computers and it works perfectly even on the same model… it’s must be something with my system…

        Thanks.
        Adam.

      • batchloaf says:

        Hmmm, answer number 1 is potentially problematic, since it may indicate that the image files don’t actually contain the images at all. Is there any way you could share an example output image in Dropbox or Google Drive and then post the link here so that I can check whether I can open it and/or identify a workaround?

        Ted

  11. Adam says:

    below link to the file.
    https://drive.google.com/file/d/0B8h2vGElfpj4MTUyTGZ1M1pLTG8/edit?usp=sharing

    it’s 1KB file. nothing stored in it.

  12. Alexander says:

    Hi, It’s a great app, it works perfect on my computer when I run it outside of office, but when I try it with the macro, the image file is not created.
    My current configuration:
    SO: Windows 8
    Office: Office 2007

    • batchloaf says:

      Hi Alexander,

      Ok, thanks for letting me know. I’ll need to try this myself in Windows 8 to see if that’s causing a problem. Unfortunately, I’m running Windows 7, so I won’t be able to test it straight away.

      Ted

  13. Chris says:

    Any ideas with the code for Office 2013? Is there a way to specify a cell location instead of an object?

  14. Chris says:

    Well figured it out, location address is where mine works best, 1,1 would be default

    Private Sub SnapshotButton_Click()
    Dim RetVal
    Dim myWb As Excel.Workbook

    ‘ 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 /devnum 2”, 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”))

    Application.ActiveSheet.Shapes.AddPicture “C:\Users\Kevin\Desktop\image.bmp”, False, True, 1000, 200, 424, 240

    End Sub

    • batchloaf says:

      Thanks Chris. Hopefully, others will find this useful!

      For anyone that wants to use Chris’s code, this unformatted version might be easier to copy and paste:

      Private Sub SnapshotButton_Click()
      Dim RetVal
      Dim myWb As Excel.Workbook
      
      ' 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 /devnum 2", 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"))
      
      Application.ActiveSheet.Shapes.AddPicture "C:\Users\Kevin\Desktop\image.bmp", False, True, 1000, 200, 424, 240
      
      End Sub
      

      Thanks again,
      Ted

      • Jimbo says:

        No worries, this is a very handy little program. Is there any way to increase the size of the preview window?

      • Chris says:

        No worries, great program. Is there any way to increase the size of the preview window?

      • batchloaf says:

        Hi Chris,

        CommandCam just captures a frame at the camera device’s default video resolution, so it’s not straightforward to modify it. However, there may be some way to configure the default resolution of the device using the manufacturer’s configuration utilities, or even Windows’ built in configuration tools? I’m not sure about this, but it could be worth a try.

        An alternative is to use RobotEyez, which is another program I wrote. It includes an option to specify image resolution, which works at least some of the time!

        Here’s the link to RobotEyez:

        It’s also free and the full source code is on that page. It might do the trick for you.

        Ted

      • Chris says:

        Not sure if it is a Excel 2013 thing or not, but the kill command throws a error 53 if the specified file is not found and the operation fails. To avoid this I changed it to Kill (“*.bmp”) but be warned – it will delete all bitmap files in the folder. Doesn’t throw an exception if there is no image file present.

      • Chris says:

        Thanks for that Ted, but RobotEyez doesn’t seem to work with my Surface though CC does. Looks like my above code doesn’t really alleviate the issue of the error 53 either Thanks for sharing your work!

      • batchloaf says:

        Hi Chris,

        Could you do something like the following?

        If Dir("image.bmp") > "" Then
            Kill ("image.bmp")
        End If
        

        Dir("image.bmp") should return a list of files in the current directory that match the specified string. If there are no matching files, the list is just an empty string (i.e. “”) which is not greater than an empty string, so the kill command will not be carried out. However, if there’s a matching file, then the returned list will be greater than an empty string and the kill command will be carried out.

        Ted

      • Chris says:

        For some reason every time I open this and initially run it Excel crashes. It deletes the image file so the next time it runs and gets error 53 on the image file, but after recreating it the code runs. Any thoughts? Surface with 8.1 and Excel 2013

        Private Sub SnapshotButton_Click()
            Dim RetVal
            Dim myWb As Excel.Workbook
             
            ' 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 ("DONOTDELETE.bmp")
             
            ' Now, wait until image file is definitely gone
            While Dir("DONOTDELETE.bmp") > ""
            Wend
             
            ' Capture new image
            RetVal = Shell("DONOTDELETE.exe /filename DONOTDELETE.bmp /devnum 2 /preview /delay 5000 /quiet", vbNormalFocus)
             
            ' Wait until image file is definitely there
            While Dir("DONOTDELETE.bmp") = ""
            Wend
             
            ' Short delay to let new file finish saving
            Application.Wait (Now + TimeValue("00:00:01"))
                             
        End Sub
        
      • batchloaf says:

        Is it possible that “DONOTDELETE.bmp” is open in another application? If so, the Kill command probably won’t be allowed to delete the file. If that happens, the Kill command will fail to remove the file and this loop…

        ' Now, wait until image file is definitely gone
        While Dir("image.bmp") > ""
        Wend
        

        …could repeat forever, waiting pointlessly for the file to disappear, which might cause Excel to freeze.

        To deal with the case when “DONOTDELETE.bmp” is not present in the first place,you should probably also include the “if” statement strategy I wrote about earlier. specifically, you should wrap the Kill statement with something like this to check whether the file exists:

        If Dir("image.bmp") > "" Then
            Kill ("image.bmp")
        End If
        

        Ted

  15. Wietse says:

    Hi, I keep getting an error code 424 although I have been doing it exactly as mentioned above?

    Please help

    Private Sub CommandButton1_Click()
    Dim RetVal
    Dim myWb As Excel.Workbook

    ‘ 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 /devnum 2”, vbHide)

    ‘ 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”))

    Image1.Picture = LoadPicture(“image.bmp”)

    End Sub

  16. Wietse says:

    Sorry, forgot to mention, it takes the picture but does not replace the old image with the new one?

  17. Wietse says:

    I got it right! Only problem is the picture is now appearing all over the show 🙂 How can I get it to ‘paste’ on the same area and how do I get it right to automatically delete the old picture which is in the worksheet?

  18. batchloaf says:

    Hi Wietse,

    I’m a bit confused by your description. The code above updates the picture that’s displayed in an Image object on the spreadsheet. You need to create the Image object at the start (before you run the program) and then the VBA program updates what is displayed in the Image object.

    Did you create an image object on the spreadsheet before you ran the program?

    Assuming the Image object is created and positioned before the program runs, it should not be moved around at all by my example VB code. What it displays should change, but the object is never moved.

    Ted

    • Wietse says:

      Hi Ted,

      I have tried this over and over, I only ran the code after everything has been done and it still gives me a error code 424:

      Private Sub CommandButton1_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”, vbHide)

      ‘ 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

      I have also tried Chris’ code which works but it doesn’t delete the old pic, it only puts a new pic on top of the old one which then increases your file size over time:

      Private Sub CommandButton1_Click()
      Dim RetVal
      Dim myWb As Excel.Workbook

      ‘ 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 /devnum 2”, vbHide)

      ‘ 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
      Application.ActiveSheet.Shapes.AddPicture “C:\Users\wietseb\Desktop\Eish\image.bmp”, False, True, 20, 20, 200, 180

      End Sub

      Please help

  19. batchloaf says:

    Hi Wietse,

    Is the 424 error caused by the following line?

    Image1.Picture = LoadPicture("image.bmp")
    

    If so, then either you didn’t create an image object on the spreadsheet before running the subroutine, or else you created the image object but it’s called something other than Image1. If you’re using the code exactly as shown in the first example you included above, then the image object must be called Image1 or the subroutine will fail with error 424.

    If the error is caused by a different line in the subroutine, please let me know which one.

    To avoid any confusion, I’m going to paste in the exact code listing I’m referring to below.

    Ted

    Private Sub CommandButton1_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", vbHide)
    
        ' 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
    
    • Wietse says:

      Hi, so the image in the spreadsheet is called ‘image’ and this image is the same image that is lying in your folder with the ‘CommandCam.exe’ file? Which one must be image1?

  20. batchloaf says:

    The name of the image object in the spreadsheet must be Image1 – otherwise you need to modify the code. This is not a filename or anything like that. It’s the name of an Excel object (an image object that you have already created and placed on your spreadsheet).

    • Wietse says:

      Thanks! But you have another image in the folder where your spreadsheet is saved with the CommandCam.exe? What must that be called?

      • batchloaf says:

        It still sounds like you’re confused about what I mean by Image1. The image file (“image.bmp”) that appears in the same folder as your spreadsheet will be called “image.bmp” automatically by CommandCam. The VB code above assumes that the photo to be displayed on the spreadsheet is stored in a file called “image.bmp”. However, “image.bmp” is just a file. Like all files, it is contained in a folder – in this case, the same folder as your spreadsheet.

        “image.bmp” is NOT the object I am saying must be named Image1

        Your problem is nothing to do with this file. CommandCam automatically saves each photo as “image.bmp” and the VB code above loads the photo from the same filename (“image.bmp”). That part is probably working perfectly! Your problem is to do with the image object on the spreadsheet. Either the spreadsheet image object does not exist or it’s called something other than Image1. Inside Excel, you need to manually place an image object onto the spreadsheet and that object must be called Image1. This object is not a file (although it can display a photo loaded from a file), and it is therefore not contained “in a folder”. It’s an object that exists only within the Excel spreadsheet, similar to a textbox, or a graph, or a formula. It’s an object in the spreadsheet.

        I repeat: Image1 is the name of an object inside your Excel spreadsheet – it’s not a file, so it’s not “in a folder”. You need to menually create the image object in your Excel spreadsheet.

        The exact steps to do this in Excel depend on the version of Excel that you’re using, but in recent versions it’s probably something like: From the Insert tab, select Insert Picture. The image object that’s created on the spreadsheet will probably be called Image1 automatically, but you should check its properties to be sure.

        Hope that helps.

        Ted

      • Wietse says:

        Hi, I also think it is the Image1 on the spreadsheet which is giving the problem, I took a picture with CommandCam (image), made a copy of it and pasted it on the desktop and renamed it Image1, opened a spreadheet, inserted the picture from the desktop (Image1) onto the spreadsheet, put the code in as well as the Active X Command Button, saved it as a Excel macro-enabled workbook in the same folder as the CommandCam & image, when I press the button CommandCam takes a picture and replace the initial picture in the folder but it doesn’t replace the Image1 in the spreadsheet, sorry really struggling with this.
        It then gives the Error 424…

        Am I doing it right?

  21. Wietse says:

    Hi, please help! Is there a way that I can email you my spreadsheet? It keeps on giving me the ‘Error 424 – Object Required’ 😦 I have even renamed the object but nothing seems to work…
    I will really appreciate it if you can help me with this, I am no VB specialist as you can see but this is seriously doing my head in.

    • batchloaf says:

      Hi Wietse,

      Perhaps you can just share your spreadsheet publicly on Google Drive and post the link here?

      Ted

    • batchloaf says:

      Hi Wietse,

      Ok, I was able to replicate your “424” error on a Windows 7 laptop in work, so I’ll hopefully be able to work out a solution which will solve your problem. Unfortunately, I ran out of time to solve it before leaving the office yesterday evening, so it will have to wait until next week.

      I’ll keep you updated!

      Ted

  22. Wietse says:

    Hi Ted, thanks so much!!

    • batchloaf says:

      Hi Wietse,

      Ok, I think I have a solution for you. At least it’s working fine on this laptop where I’m running Microsoft Excel 2010.

      The updated VB code is:

      Sub ButtonSnapshot_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
          If Dir("image.bmp") > "" Then
              Kill ("image.bmp")
          End If
           
          ' 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
          Sheet1.Image1.Picture = LoadPicture("image.bmp")
      End Sub
      

      You can download my Excel file here.

      Here’s how my spreadsheet looks:

      Here’s how the VB module looks:

      The main problem seems to have been something to do with the object hierarchy that the Image object fits into. As you can see, I ended up referring to Image1 as “Sheet1.Image1” which is different from my original example. I guess something changed between the previous version of Excel and this one? Anyway, who cares… it’s working! I gave up trying to understand why anything is the way it is in VB years ago.

      Ted

      • Wietse says:

        Hi Ted,

        Me again 🙂 How do I get the button to work on one sheet (Sheet1) and the photo to sit on another (Sheet2)?

      • batchloaf says:

        Hi Wietse,

        Sorry for the delay responding – I was away on holidays for a while. You should be able to just put the photo on a different sheet without any difficulty. You just need to create the image object on the second sheet and then change the last line in the above example to something like…

        Sheet2.Image1.Picture = LoadPicture("image.bmp")
        

        Obviously, that assumes the image object (“Image1”) is already on Sheet2.

        Ted

  23. Wietse says:

    Hi Ted, Brilliant!! Thank you very much, it works!! Just another question 🙂 Can you include a section in the code which can print the worksheet after it has taken the photo?

  24. Wietse says:

    Thanks Ted! It works! thanks for all your help! You are a genius!

  25. Wietse says:

    Do you focus just on this specific code or do you deal with other types of code as well?

    • batchloaf says:

      If you mean Visual Basic, then no, I almost never use it! It just happened that someone left a comment here on the blog a while back asking how to use my CommandCam application in an Excel spreadsheet, so I spent a few minutes working out how to do it then posted a simple example (the one above). I used to use VB a lot several years ago – I even taught it for a couple of years – but I don’t really use it at all any more.

      I teach several programming classes here in the Dublin Institute of Technology (in Ireland). In my school (Electrical and Electronic Engineering), the language we use most is C/C++, probably following by Python and MATLAB. Students sometimes use VB in projects and some of our graduates certainly still use it in industry (e.g. in industrial automation / process control).

      CommandCam itself is written in C++ because it uses DirectShow. I hacked it together a few years ago for a project student who was doing his project on a simple vision system to monitor the growth of pigs on a farm. He just needed a simple way to capture images from the command line. As it turns out, quite a few people have found it useful for other projects so I try my best to help out when people have problems. It certainly isn’t my day job though!

      Ted

  26. jakes says:

    I tried it it work first capturing but does not show new pic on spreadsheet gives me an error. Run-in error object not found. Then in VBA it highlights the Image1.Picture = LoadPicture(“image.bmp”) yellow why. Only problem I have

  27. Ashty Rekani says:

    I’m using this on an HP Stream 7 and it takes the pictre with the front-facing camera. Is there any way to switch to the main camera?

  28. rousseau says:

    thanks a lot great to find the answer after so many days

  29. Mutaz says:

    Hello, and thanks for sharing… When I run your code, the shell window opens and this is what I see on the command line:
    Capture device: USB Video Device

    It seems that the program is searching for the webcam device to grab and its stuck in a loop… Any ideas on what to do to capture an image?

    Best

  30. Piet says:

    Hi,
    Could anyone tell me if it is possible to use a variable als filename.
    I used:
    Dim RetVal

    ‘ Foto’s worden geplaatst in submap “Foto”.
    PathName = ActiveWorkbook.Path ‘ the path of the currently active file
    SheetName = ActiveSheet.Name ‘ the name of the currently selected worksheet
    strFolder = PathName & “\” & SheetName & “\Foto\” ‘ the path to the Photo Direcory

    ‘ Get PhotoFileData from record of selected Row
    iRow = Selection.Row
    strName = Cells(iRow, 3)
    strType = Cells(iRow, 4)
    strFile = strName & “.” & strType
    strFullFileName = strFolder & strFile

    ‘ Make sure the current directory is changed to the foto-directory
    ChDir strFolder

    ‘ Capture new image
    RetVal = Shell(“CommandCam.exe /filename & strFullFileName”, vbNormalFocus)

    but the picture gets “strFullFilename” as filename instead of “foto01.bmp”

    Any help would be very nice
    Thank you!!

    • batchloaf says:

      Hi Piet,

      Sincere apologies for the delay in responding.

      The solution here is simple – you just have your closing inverted commas in the wrong place. Change this line…

      RetVal = Shell(“CommandCam.exe /filename & strFullFileName”, vbNormalFocus)
      

      …to this…

      RetVal = Shell(“CommandCam.exe /filename " & strFullFileName, vbNormalFocus)
      

      Ted

      • Piet says:

        Hi Ted,
        Thank you. This solved my problem.

        In the meantime i, as many others, struggled with resolution and stumbled upon your “Roboteyez”
        As commandcam works on our Terra Tablets, the resolution stops at default 448×252.
        So looking for more resolution i was happy to find your Roboteyez.
        Sadly Roboteyez does not work at all.
        After firing the command from an elevated dos-prompt it simply ends with “Could not render preview video stream”

        Any ideas what this could be?
        Your help would be very usefull.

        Piet

      • Piet says:

        Hi Ted,

        In addition to my reply, i just found out that roboteyez does work with resolution 448×252. I found this one out because Commandcam did make picture on the same tablet and this was it’s default resolution.
        I do hope now that there will be another higher resolution to take pictures with these tablets. I already tried the standard ones like 1024×768 and 1280×1024 et cetera. But because Roboteyez works with the “strange” resolution 448×252, maybe there is an higher “strange” equivelant. Any idea how to find this out.

        Thanks in advance, PIet

      • batchloaf says:

        Hi Piet,

        It seems very strange that RobotEyez would be producing 448×252 resolution images, but that’s got to be something to do with the camera (or its driver) rather than RobotEyez itself. Unless you specify the resolution, RobotEyez will just be using whatever resolution the camera defaults to.

        Is it possible that the camera device you’re opening really has a very low native resolution? Even when a camera has low resolution, its driver will sometimes be designed to allow it to be opened at a higher resolution, but the high resolution is only simulated by scaling up the low resolution frames on the fly (in software). Could this be the case with your camera?

        If your camera really can capture video at some specific higher resolution, I would expect RobotEyez to work at the resolution if you specify it. Please note though that RobotEyez opens the camera as a video device and just captures one frame. Some cameras support higher resolution for still image capture than they do for video, but RobotEyez will only work at resolutions that are supported for video capture.

        Microsoft provide an application called GraphEdit, which can be used to construct and test DirectShow filter graphs. In DirectShow parlance, a “filter” is just a software object that performs some individual element of a video capture operation – e.g., capture from camera, convert format, write to a file, etc. A filter graph is just a connected string of these “filters” that performs a complete video capture operation. Applications like RobotEyez need to construct a filter graph before “running” it to perform the actual capture operation. In the case of RobotEyez, a single frame is captured and stored to file and then the filter graph is stopped and the application closes. GraphEdit lets you try out different combinations of filters with your camera, so it might help you work out what resolutions are supported by your camera for video capture. Here’s the link to GraphEdit on the Microsoft website:

        https://msdn.microsoft.com/en-us/library/windows/desktop/dd407274%28v=vs.85%29.aspx

        Ted

  31. Alex says:

    Thanks for this. It has been very useful! It did take me half a day to improvise to make it work, but definitely worth it in the end. I will paste my working code below if any one else is having issues with their code. Below code works very well with Excel 2013.
    Sub SnapshotButton_Click()
    Dim RetVal
    ‘ Make sure the current directory is set to the one
    ‘ where the Excel file is saved
    ChDir (ThisWorkbook.Path)
    Set Sheet1.Image1.Picture = LoadPicture
    If Dir(“C:\CamToExcel\image.bmp”) “” Then
    ‘ First, delete image file if present
    Kill (“C:\CamToExcel\image.bmp”)
    ‘ Now, wait until image file is definitely gone
    While Dir(“C:\CamToExcel\image.bmp”) > “”
    Wend
    End If
    ‘ Capture new image
    RetVal = Shell(“C:\CamToExcel\CommandCam.exe”, vbNormalFocus)
    ‘ Wait until image file is definitely there
    While Dir(“C:\CamToExcel\image.bmp”) = “”
    Wend
    ‘ Short delay to let new file finish saving
    Application.Wait (Now + TimeValue(“00:00:01”))
    ‘ Load new image into image object in excel sheet1.
    Set Sheet1.Image1.Picture = LoadPicture(“C:\CamToExcel\image.bmp”)
    End Sub

    Cheers,
    Alex.

    • Alex says:

      Just a quick one. This program was working beautifully then not. Something to do with the CommandCam.exe not producing the image.bmp file and then causing the While-Wend in to a endless loop. NOT GOOD!!!
      Back to the drawing board. Sorry if I have stuffed any one else up.

      • Alex says:

        LOL. Anyway worked out the problem. When the CommandCam is run the image.bmp file goes to “C:\Users\Alex\Documents\”(This must be the default for CommandCam or my computer’s Camera cam or who knows!?). This quite obviously was a bit frustrating so I just implemented a test in the code when it does and move the file(Which is easy to do. (Name “C:\Users\Alex\Documents\image.bmp” As “C:\CamToExcel\image.bmp”)). So all is good again! Very useful piece of work by Ted Burke. I wish I had half that knowledge.

      • batchloaf says:

        Hi Alex,

        Thanks for your kind words!

        Just a quick point about the file location. You can tell CommandCam to save the image file wherever you want using the “/filename” command line option and providing a filename that includes the full path.

        For example, you could try changing this line…

        RetVal = Shell("C:\CamToExcel\CommandCam.exe", vbNormalFocus)
        

        to something like this…

        RetVal = Shell("C:\CamToExcel\CommandCam.exe /filename C:\CamToExcel\image.bmp", vbNormalFocus)
        

        Anyway, best of luck with the rest of whatever you’re working on!

        Ted

      • Alex says:

        Excellent works a treat. Thank you vm.

      • batchloaf says:

        You’re welcome!

        Ted

  32. Mathieu says:

    Hello,
    Thank you for this awesome program. I’m using it with a tablet to collect some data. I’m asking myself if the CommandCam already has something related to get the GPS coordinates of the photo? There is a GPS in the tablet so it would be very nice to put the coordinates in relation with each photo.

    Thank you
    Math

    • batchloaf says:

      Hi Math,

      No, I’m afraid there’s nothing like that built into CommandCam. If you have another command line GPS utility, it might be possible to write a little batch file to create a filename with the GPS coordinates and then use that filename when you run CommandCam? It’s just an idea – I haven’t ever tried it.

      Ted

  33. Tony says:

    First of all, great application. I am having an issue running the CommandCam from within Excel 2010, Windows 8.1. The following command had no issues when running within a Excel 2007, Windows 7 platform. Any suggestions?

    RetVal = Shell(“CommandCam.exe /filename image.bmp /preview /delay 1000”, vbNormalFocus)

    The CommandCam.exe will run fine outside of Excel as a standalone app within Windows 8.1, but freezes up Excel everytime I run the VBA script within Excel.

    Tony

    • batchloaf says:

      Hi Tony,

      Sorry I didn’t get a chance to reply when you left this message. That’s a strange problem. Does a command window open up when the Shell line executes? I think that VB will wait for CommandCam to exit before assigning the return value into RetVal, so I suppose if CommandCam freezes for some reason that could cause VB to freeze too.

      Did you try putting a simpler CommandCam command line into the Shell command?

      Since CommandCam runs ok outside Excel and also fine inside Excel 2007, I’m kind of at a loss as to what to suggest. Sorry I can’t offer any more useful advice.

      Ted

  34. ICT says:

    Hello

    I used this tool and it worked fine until I want to call the ‘command.exe’ with a subroutine and a parameter. Taking a picture is no problem but the picture can’t be loaded using a parameter.

    For example, in the subroutine ‘SnapshotButton_Click()’ I omit the load instruction,
    Activesheet.Image1.Picture = LoadPicture(“image.bmp”)
    and use this in another subroutine.

    In this subroutine I use following instructions:
    Call SnapshotButton_Click(parameter)
    Activesheet.Image1.Picture = LoadPicture(parameter) -> this line generates an error

    Second problem, if I want to delete a wrong picture the delete-instructions in VBA delete always all pictures and also the imagebox. So, a new picture can’t be inserted anymore in the Excel document.

    Does anybody know how this can be fixed?

    Thank you!

    ICT

  35. Mark says:

    Hello

    The code work! is there a possible way instead the preview will create own window it will load in the form I just created? A form with the img inside? that’s where I will put the live preview.

    • batchloaf says:

      Hi Mark,

      That sounds like it would take a bit of work to do. It’s an interesting problem and I’d love to have a go at it, but I’m currently way too busy with teaching stuff, so unfortuantely I can’t. I hope you find another solution!

      Ted

  36. Dwayne says:

    Hey I know this an old thread but is there a way to cancel this sub routine if the camera is not plugged in. It seems like the dir while loop just runs forever waiting for image.bmp which will never happen if there is no camera at all.

    • batchloaf says:

      Hi Dwayne,

      You could try something like this…

      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)
           
          ' Five second delay to allow new image to be captured
          Application.Wait (Now + TimeValue("00:00:05"))
           
          ' Check if the file is there
          If Dir("image.bmp") = ""
              ' No image found so display a message
              MsgBox "image.bmp not found"
          Else
              ' The image is in the directory
              ' Wait another second to ensure the file is complete
              Application.Wait (Now + TimeValue("00:00:01"))
      
              ' Load new image into image object on spreadsheet
              Image1.Picture = LoadPicture("image.bmp")
          End If
           
      End Sub
      

      Beware: I haven’t tried that code because I’m on my Linux laptop so I don’t have Excel here.

      Ted

  37. Fabrizio says:

    very good job…works like a charm!

  38. Diana C says:

    Hello,
    I hope you can help me because I’ve been breaking my head for the past few hours and I still can’t figure it out!
    Let me start by saying your application is absolutely magnificent!! so beautiful and efficient!

    So here comes my trouble:
    I’ve modified the VBA code to work with MS Access – it now saves the picture under the record’s ID number and upon loading certain forms, it also loads the appropriate picture. Worked fantastic!
    But then it suddently decided to change the location where it saves image.bmp 😦
    I’ve done my homework and found your replies to add “\filename” to the shell string…unfortunately it does not seem to work with Access 2013 VBA code…can you please help me? what is the appropriate MS Access VBA string to pass on the filename argument?

    Thank you so much in advance,

    Diana

  39. Diana C says:

    Hi! How do you pass the filename argument in a MS Access VBA project?!? 😦

  40. Brad says:

    Hi I was hoping for help with this wonderful program.
    I was wanting basically the same script as above (snapshot button_click) but the images saved to the local temp folder and retrieved from there. I want to run this from one drive on multiple devices but they overwrite each others images, if they could save to their default temp folder this would stop the overwriting of the image.bmp file

    • batchloaf says:

      Hi Brad,

      Interesting problem! I have two ideas how you might solve this.

      First way:

      Try changing this line…

          ChDir (ActiveWorkbook.Path)
      

      …to something like this…

          ChDir ("C:\Temp\")
      

      As a result, you may also need to change this line…

          RetVal = Shell("CommandCam.exe", vbNormalFocus)
      

      …to this…

          RetVal = Shell(ActiveWorkbook.Path + "CommandCam.exe", vbNormalFocus)
      

      Hopefully, each computer should then be storing the image file in its own temp folder.

      Second way:

      Continue storing the image files in the shared folder where the spreadsheet file is stored, but get each computer to use a different image filename. In the example below, I’m setting the image filename as the name of the computer with “.bmp” on the end…

      Private Sub SnapshotButton_Click()
          Dim RetVal
      
          Dim filename As String
          filename = Environ$("computername")
          filename = filename + ".bmp"
          
          ' Make sure the current directory is set to the one
          ' where the Excel file is saved
          ChDir (ActiveWorkbook.Path)
          
          ' First, delete image file is present
          Kill (filename)
          
          ' Now, wait until image file is definitely gone
          While Dir(filename) > ""
          Wend
          
          ' Capture new image
          RetVal = Shell("CommandCam.exe /filename " + filename, vbNormalFocus)
          
          ' Wait until image file is definitely there
          While Dir(filename) = ""
          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(filename)
      End Sub
      

      Please note that I haven’t been able to try this out because I’m not working on a Windows machine here!

      Ted

      • Brad says:

        Thank you for your record winning response time!
        I tried both ideas, I could only get your first idea to work, and it works perfectly. I modified some parts to use Environ$(“temp”).
        Idea #2 though I could not get to work, I think (dangerous territory now) it may have something to do with RetVal= line not specifying the image name (called filename in the rest of the code).
        I did try to use the /filename switch after as such
        RetVal = Shell(“CommandCam.exe /filename filename”, vbNormalFocus)
        but this gave me a file in the active workbook folder called filename with no extension.

        Anyway I absolutely love this program, and thank you for helping with this issue.

        Brad

      • batchloaf says:

        Hi Brad,

        Oops, yes – well spotted!

        Upon closer inspection, I now see that I never told CommandCam to use the new filename. That line should be something like…

            RetVal = Shell("CommandCam.exe /filename " + filename, vbNormalFocus)
        

        I’ve edited the above code listing to incorporate the change.

        Anyway, I’m glad you got things working!

        Ted

  41. bb says:

    Hi batchloaf,

    Firstly excellent work, amazing piece of code!

    I have got the camera program to work fine and have the preview window opening using the following “/preview” command. However when it opens the preview window it is offset and does not fill the screen (of my tablet). Is there a way to make the preview window fill the entire screen, similar to pressing the “maximize” button in windows?

    Cheers

    bb

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s