Capture image from device camera and paste link in excel cell?

[onefish]

G'day, I have dabbled with AutoIT years ago and was able to use it to perform all sorts of necessary tasks outside of a closed database, it worked very well. I'm faced with a new challenge and hopping that someone here can point me in the right direction.

I am trying to come up with a solution to speed up field collection of data on a tablet PC (yet to be purchased, will be Win 8.1). I would normally turn to relational database solutions for this but this is something that will be used for about 100 or so inspections and then shelved so I don't want to waste too much time and money on it. My investigations have also led me to believe that I will face the same issues with a database or a simple excel spreadsheet anyway in relation to capturing an image and storing the link in the correct place. It is not ideal but I can build all the relational tables and conditional dropdown boxes in excel so if I can somehow manage to script the functions outside excel and get the necessary data back into excel then it will do.

OK, so here is the problem...... I need to be able to put a button in a cell that, when clicked, launches the tablet's rear camera and allows me to take a photo, and somehow copies the path to the photo and pastes it into the excel cell where the button is located. I need to be able to take more than one photo per row (record) so I am assuming that having the button in the cell is the way to go but it may also be possible to use some logical arguments to use one button per row which tests the row/column (cell reference) for ISBLANK or similar and moves to the next column if =false (just a thought).

Pasting the actual image into the cell would be fine too but I don't believe that there's any way to paste an image into excel (have to use the insert menu, which I suppose could be scripted once the link is on the clipboard???). I don't mind using a combination of macros, VBA, AutoIT scripts, batch files, whatever I need to to make this work.

I don't foresee that it will be too difficult to create a macro or VBA script in excel which would launch an AutoIT executable script. The AutoIT script would need to launch the camera application (easy to script for) and then wait while a photo is taken, then somehow copy the image file name or path (whichever, can add the full path in excel calculations easily anyway) for the image that was just taken (there will be multiple images in the folder so need to select only the newest one, or script file moves, etc each time) and then either paste it directly into the cell in excel or alternatively (if copied to the windows clipboard), initiate another VBA script or macro in the excel doc that will paste the name/path into the cell.

Does anyone have any suggestions about the best way to achieve this?

Cheers!

[onefish]

Ahh, I see that AutoIT has a script step to write to excel but how to pass the cell address variable to the AutoIT script so it knows where to write the file name/path to?

[Geir1983]

Try the rewritten excel UDf, it has picture add function:

_Excel_PictureAdd($oWorkbook, Default, @ScriptDir & "\_Excel.jpg", "B2:D8")

'?do=embed' frameborder='0' data-embedContent>>

[onefish]

Geir1983, that is interesting but I'm not sure it will achieve what I need to accomplish. It could be used to insert a picture instead of just a link but I still need to pass the cell address to AutoIT and somehow select only the most recent image file in the folder to get the path or name. I am thinking that it would be best to have a small and always in front dialogue box in one of the screen corners (have to see where it won't be in the way of the camera app) with two buttons, 1 = Use Pic and 2 = Cancel. Use Pic would minimise the camera app (don't want to open and close it all the time), grab the file name/path of the most recent image still don't know how to do that), write the name/path in excel and end the script. Cancel would just minimise the camera app, return to the active cell in excel and end the script. I can work out most of it but these two things I don't know:

1. how to pass the cell reference from excel to AutoIT as a variable (is the Windows clipboard the way to go?)
2. how to select only the newest image file in a defined directory and commit the file name/path to AutoIT memory

Cheers!

[Geir1983]

You could send the cell reference with the Excel event, when the cell changes you could then search your directory. Check out the example for excel events:

'?do=embed' frameborder='0' data-embedContent>>

For finding the newest file you could use _FileListToArray and then loop through the files and check witch one is the newest.

_FileListToArray()
FileGetTime()

I made a quick example by editing the excel example, it searches for the newest file when you insert the text "Find newest Image" in a cell, then replaces that cell with newest file in your folder.:

expandcollapse popup

; *****************************************************************************
; Example Script
; Handle Excel worksheet change event when a cell has been changed and set the
; color of the cell to red.
; This script loops until Shift-Alt-E is pressed to exit.
; *****************************************************************************

#include <Excel Rewrite.au3>
#include <Constants.au3>
 #include <File.au3>

HotKeySet("+!e", "_Exit") ;Shift-Alt-E to Exit the script
MsgBox(64, "Excel UDF: Events Example", "Hotkey to exit the script: 'Shift-Alt-E'!")

; Create application object and open a workbook
Global $oAppl = _Excel_Open()
If @error <> 0 Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: Events Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Global $oWorkbook = _Excel_BookNew($oAppl)
If @error <> 0 Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: Events Example", "Error opening workbook '_Excel2.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oAppl)
    Exit
EndIf
ObjEvent($oWorkbook.Activesheet, "Worksheet_")
While 1
    Sleep(10)
WEnd
Exit
FileGetTime
; Excel - Worksheet change event - http://msdn.microsoft.com/en-us/library/ff839775(v=office.14).aspx
Func Worksheet_Change($oRange)
    Local $CurrentFile, $NewestFile, $NewestIdx
    IF $oRange.value = "Find newest Image" Then
        Local $Files = _FileListToArray(@ScriptDir)
        For $Idx = 1 to $Files[0]
            $CurrentFile = FileGetTime($Files[$Idx], $FT_CREATED, 1)
                IF Number($CurrentFile) > Number($NewestFile) Then
                    $NewestFile = $CurrentFile
                    $NewestIdx = $Idx                   
                EndIf
        Next
        $oRange.value = $Files[$NewestIdx]
    EndIf
EndFunc   ;==>Worksheet_Change

Func _Exit()
    Exit
EndFunc   ;==>_Exit

Edited May 2, 2014 by Geir1983

[onefish]

Geir1983, thank you. I haven't tested but from looking at the script it appears to create a new excel file each time it runs. I assume that the script could run without that part of the code and simply reference the existing open excel workbook?

Also, for this to work I would need to use VBA to insert the trigger text into a cell and then run the AutoIT script. Will the script still perform properly if it is initiated after the excel event or does the script need to be running and watching for the trigger event to occur?

I assume that this script is searching for the newest image file in the directory where the script is stored, is that correct? Shouldn't be too difficult to tell it where to look so I don't have to store the script exe in the pictures folder right?

Sorry for all the questions, I just want to make sure that I can make a solution work before I run out and purchase hardware for the purpose.

Cheers!

[Geir1983]

Yes of course you can open an existing excel document. In the example i posted the script needs to be running when you insert the trigger text to a cell, but it is possible to do this differently. And again of course you can change the directory to search. Not sure why you would need to use VBA instead of doing it all in the same autoit script.

This is definitely doable with a little effort

[onefish]

I'll be entering lots of data in the spreadsheet and then hitting a button that will trigger the whole camera/get image file path/insert path into excel. The AutoIT script needs the trigger text to be inserted into a cell in order to know which cell to use and to trigger it to move to the next step so that would have to be done outside the AutoIT script right?

It would be better if the AutoIT script could be initiated and then parse an already open excel worksheet for the trigger text. Would that be a different function than the UDF Events in the example?

An overview of actions:

1. User hits button in excel worksheet - runs macro or VB script to enter trigger text into correct cell (I can manage this easily enough)
2. macro or VB script above then runs the AutoIT exe and ends itself
3. AutoIT script open/runs native camera application and puts a small dialogue box over the top of the camera app and waits for further user input via the dialogue box
4. User takes photo, if good then hits the use button on the AutoIT dialogue box (user could take more than one photo to get a good one using this method)
5. AutoIT script minimises the camera application
6. AutoIT script retrieves the file name (or full path) of the image just taken
7. AutoIT script activates the excel window and overwrites the contents of the cell containing the trigger text with the image file name (or path)
8. AutoIT script ends