Good morning All,
I have fiddled with this for about an hour to no avail. I had the below code working flawlessly in Excel 2013 but with a recent upgrade to 2016 I can't get it to work.
Sub TakePhoto()
Dim FixtureDesignation As Range
'Fixture Designation of cell you want to take a picture of
Set FixtureDesignation = ActiveSheet.Buttons(Application.Caller).TopLeftCell
'Exit if there is no fixture designation
If FixtureDesignation = 0 Then
MsgBox ("A fixture designaiton is required.")
GoTo 10
End If
'Check if picture of fixture designation exists
Dim FilePath As String
Dim TestStr As String
FilePath = Application.ActiveWorkbook.Path & "\Photos"
TestStr = ""
On Error Resume Next
TestStr = Dir(FilePath & "\" & FixtureDesignation & ".bmp")
If TestStr = "" Then
GoTo 20
Else: GoTo 30
30
If MsgBox("A photo of this fixture designation already exists. Would you like to overwrite it and take a new photo?", vbYesNo) = vbNo Then Exit Sub
End If
20 'Fixture Designation does not exist or "yes" was answered to overwrite existing photo
Dim RetVal
' Make sure the current directory is set to the one
' where the Excel file is saved
ChDir (Application.ActiveWorkbook.Path & "\Photos")
' First, delete image file if present
If Dir(FixtureDesignation & ".bmp") > "" Then
Kill (FixtureDesignation & ".bmp")
End If
' Now, wait until image file is definitely gone
While Dir(FixtureDesignation & ".bmp") > ""
Wend
' Capture new image
RetValA = Shell("cd Photos")
RetVal = Shell("CommandCam.exe /preview /delay 5000 /filename " & FixtureDesignation & ".bmp", vbHide)
' Wait until image file is definitely there
While Dir(FixtureDesignation & ".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(FixtureDesignation & ".bmp")
'CLEANUP!
'Set FilePath = Nothing
'Set TestStr = Nothing
'Set Dir = Nothing
Set RetValA = Nothing
Set RetVal = Nothing
10 End Sub
It appears everything goes as expected until I hit this line where I suspect the problem is and have searched for a fix and tested different variations;
' Capture new image
RetValA = Shell("cd Photos")
RetVal = Shell("CommandCam.exe /preview /delay 5000 /filename " & FixtureDesignation & ".bmp", vbHide)
Where it will hang on this line for a while until it finally freezes
' Wait until image file is definitely there
While Dir(FixtureDesignation & ".bmp") = ""
Wend
Any hints? Thanks for any help.
Bookmarks