Jump to content
KimberlyJillPereira

How to match the date and time in my Excel to the picture in the folder?

Recommended Posts

Using the alarm received parameter how to search for the picture that matches the year month day hour min and sec and by the name of the .jpg file the jpg file naming format is first letter is always m followed by last two digits of year then two digit month two digit day two digit hour two digit minute and two digit second then get that picture and paste it in Microsoft Excel.How to do it? Please help me. Thank you.:)

iii1.png

iii2.png

iii3.png

Share this post


Link to post
Share on other sites

Was a little bored, so put this together:

#include <Array.au3>
#include <Excel.au3>
#include <File.au3>

Local $oExcel = _Excel_Open()

Local $aImagePath
;~ Add Image Source Path
Local $sImagePath = "Y:\88_TanglinHaltRd\IllegalParking\88_TanglinHaltRd_Cam1\2017-02-24"
;~ Add Excel Spreadsheet Path
Local $sWorkbook = @ScriptDir & "\Results.xlsx"

Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)
$aWorkBook = _Excel_RangeRead($oWorkbook)
For $i = 1 To UBound($aWorkBook) - 1
    $aImagePath = _FileListToArray($sImagePath, "m" & StringTrimLeft($aWorkBook[$i][5], 2) & "*.jpg", 1, True)
    If UBound($aImagePath) >= 2 Then
        _Excel_PictureAdd($oWorkbook, Default, $aImagePath[1], "G" & $i + 1)
        $aImageSize = _ImageSize($aImagePath[1])
        $oRow = $oExcel.Rows($i + 1)
        $oRow.RowHeight = $aImageSize[1]
    EndIf
Next

;~ Malkeys code: https://www.autoitscript.com/forum/topic/108578-getting-image-size/
Func _ImageSize($sFileName)
    Local $aRet[2], $sExt = StringRegExpReplace($sFileName, "^.*\.", "")
    If (FileExists($sFileName) = 0) Or _
            ((StringLen($sExt) = 3) And (StringRegExp($sExt, "(?i)(bmp|gif|jpg|png|tif|emf|wmf)") = 0)) Or _
            ((StringLen($sExt) = 4) And (StringRegExp($sExt, "(?i)(tiff|jpeg)") = 0)) Then _
            Return SetError(1, 0, $aRet)
    Local $ghGDIPDll = DllOpen("GDIPlus.dll")
    Local $tInput = DllStructCreate("int Version;ptr Callback;int NoThread;int NoCodecs")
    Local $tToken = DllStructCreate("ulong_ptr Data")
    DllStructSetData($tInput, "Version", 1)
    DllCall($ghGDIPDll, "int", "GdiplusStartup", "ptr", DllStructGetPtr($tToken), "ptr", DllStructGetPtr($tInput), "ptr", 0)
    Local $aImage = DllCall($ghGDIPDll, "int", "GdipLoadImageFromFile", "wstr", $sFileName, "ptr*", 0)
    Local $aResult = DllCall($ghGDIPDll, "int", "GdipGetImageWidth", "handle", $aImage[2], "uint*", -1)
    $aRet[0] = $aResult[2]
    $aResult = DllCall($ghGDIPDll, "int", "GdipGetImageHeight", "handle", $aImage[2], "uint*", 0)
    $aRet[1] = $aResult[2]
    DllCall($ghGDIPDll, "int", "GdipDisposeImage", "handle", $aImage[2])
    DllCall($ghGDIPDll, "none", "GdiplusShutdown", "ptr", DllStructGetData($tToken, "Data"))
    DllClose($ghGDIPDll)
    Return SetError(0, 0, $aRet)
EndFunc ;==>_ImageSize

 

Share this post


Link to post
Share on other sites

You have to be more specific, I know the code works because I tested it first, what errors are you getting?

Share this post


Link to post
Share on other sites

#include <Array.au3>
#include <IE.au3>
#include <String.au3>
#include<Excel.au3>
#include <MsgBoxConstants.au3>
#include <File.au3>
;Author: Kimberly Jill Pereira
#cs ########################################################################################################
    #  Script Function:                                                                                    #
    #  It is an Auto Login bot. It also extracts specific data from table and store it in Microsoft Excel. #
#ce ########################################################################################################

Local $oIE
Local $oDivs
Local $aPageCount, $iPageCount
Local $aTable, $oTables
Local $aResults[0][7]
Local $oUserName, $oPassWord
Local $sUserName = "Oneberry"
Local $sPassWord = "Qwer1234"
Call("_ImageSize($sFileName)")
$oIE = _IECreate("http://www.timeview2.net/", 1); Opens IE browser
WinSetState("[ACTIVE]", "", @SW_MAXIMIZE);Maximize Internet Explorer window
$oForms = _IETagNameGetCollection($oIE, "form")
For $oForm In $oForms
    If $oForm.id = "UserLoginForm" Then
        $oUserName = _IEFormElementGetObjByName($oForm, "data[User][username]")
        _IEFormElementSetValue($oUserName, $sUserName)
        $oPassWord = _IEFormElementGetObjByName($oForm, "data[User][password]")
        _IEFormElementSetValue($oPassWord, $sPassWord)
        _IEFormSubmit($oForm)
    EndIf
Next

_IENavigate($oIE, "http://www.timeview2.net/alarms")
$oDivs = _IETagNameGetCollection($oIE, "div")
For $oDiv In $oDivs
    If $oDiv.ClassName = "alarms index" Then
        $aPageCount = _StringBetween($oDiv.InnerText, "Page 1 of ", ", showing ")
        If @error Then Exit
        $iPageCount = Number($aPageCount[0])
    EndIf
Next

For $i = 1 To $iPageCount
    _IENavigate($oIE, "http://www.timeview2.net/alarms/index/group:All%20Groups/page:" & $i, 1)
    $oTables = _IETagNameGetCollection($oIE, "table")
    For $oTable In $oTables
        If $oTable.ClassName = "pageindex" Then
            $nTable = $oTable.NextElementSibling
            $aTable = _IETableWriteToArray($nTable, True)
            If UBound($aTable) - 1 > 0 And UBound($aTable, 2) = 7 Then
                If $i = 1 Then
                    _ArrayConcatenate($aResults, $aTable, 0)
                Else
                    _ArrayConcatenate($aResults, $aTable, 1)
                EndIf
                ExitLoop
            EndIf
        EndIf
    Next
 Next
 Local $oExcel = _Excel_Open(); Opens Microsoft Excel

Local $oWorkbook = _Excel_BookNew($oExcel); Create new Microsoft excel
WinSetState("[ACTIVE]", "", @SW_MAXIMIZE);Maximize Microsoft Excel window
_ArrayColDelete($aResults, 6)
 _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aResults, "A1"); write the data to A1 cell of Microsoft excel
$oWorkbook.Activesheet.range("A1:G1").Font.Bold = True ; set font to bold
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "Pictures", "G1"); write 'Pictures' to A1 cell
$oWorkbook.Activesheet.range("E2:F100").NumberFormat = "yyyy/mm/dd" & " hh:mm:ss" & " AM/PM" ;set E2 to F100 cell to date format in yyyy/mm/dd
$oExcel.ActiveSheet.Columns("A:G").AutoFit
$oColumns = $oExcel.Columns("G")
$oColumns.ColumnWidth = 40.86
$oRows = $oExcel.Rows("2:100");Expands row 2 until 100
$oRows.RowHeight = 198.75;Automatically expands row
Local $oExcel = _Excel_Open()

Local $aImagePath
;~ Add Image Source Path
Local $sImagePath = "Y:\88_TanglinHaltRd\IllegalParking\88_TanglinHaltRd_Cam1\2017-02-24"
;~ Add Excel Spreadsheet Path
Local $sWorkbook = @ScriptDir & "\Results.xlsx"

Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)
$aWorkBook = _Excel_RangeRead($oWorkbook)
For $i = 1 To UBound($aWorkBook) - 1
    $aImagePath = _FileListToArray($sImagePath, "m" & StringTrimLeft($aWorkBook[$i][5], 2) & "*.jpg", 1, True)
    If UBound($aImagePath) >= 2 Then
        _Excel_PictureAdd($oWorkbook, Default, $aImagePath[1], "G" & $i + 1)
        $aImageSize = _ImageSize($aImagePath[1])
        $oRow = $oExcel.Rows($i + 1)
        $oRow.RowHeight = $aImageSize[1]
    EndIf
Next

;~ Malkeys code: https://www.autoitscript.com/forum/topic/108578-getting-image-size/
Func _ImageSize($sFileName)
    Local $aRet[2], $sExt = StringRegExpReplace($sFileName, "^.*\.", "")
    If (FileExists($sFileName) = 0) Or _
            ((StringLen($sExt) = 3) And (StringRegExp($sExt, "(?i)(bmp|gif|jpg|png|tif|emf|wmf)") = 0)) Or _
            ((StringLen($sExt) = 4) And (StringRegExp($sExt, "(?i)(tiff|jpeg)") = 0)) Then _
            Return SetError(1, 0, $aRet)
    Local $ghGDIPDll = DllOpen("GDIPlus.dll")
    Local $tInput = DllStructCreate("int Version;ptr Callback;int NoThread;int NoCodecs")
    Local $tToken = DllStructCreate("ulong_ptr Data")
    DllStructSetData($tInput, "Version", 1)
    DllCall($ghGDIPDll, "int", "GdiplusStartup", "ptr", DllStructGetPtr($tToken), "ptr", DllStructGetPtr($tInput), "ptr", 0)
    Local $aImage = DllCall($ghGDIPDll, "int", "GdipLoadImageFromFile", "wstr", $sFileName, "ptr*", 0)
    Local $aResult = DllCall($ghGDIPDll, "int", "GdipGetImageWidth", "handle", $aImage[2], "uint*", -1)
    $aRet[0] = $aResult[2]
    $aResult = DllCall($ghGDIPDll, "int", "GdipGetImageHeight", "handle", $aImage[2], "uint*", 0)
    $aRet[1] = $aResult[2]
    DllCall($ghGDIPDll, "int", "GdipDisposeImage", "handle", $aImage[2])
    DllCall($ghGDIPDll, "none", "GdiplusShutdown", "ptr", DllStructGetData($tToken, "Data"))
    DllClose($ghGDIPDll)
    Return SetError(0, 0, $aRet)
EndFunc ;==>_ImageSize

Share this post


Link to post
Share on other sites

Not getting any errors, although I have fixed the code as it wasn't saving the document so no way for the code to associate images.

#include <Array.au3>
#include <IE.au3>
#include <String.au3>
#include<Excel.au3>
#include <MsgBoxConstants.au3>
#include <File.au3>
;Author: Kimberly Jill Pereira
#cs ########################################################################################################
    #  Script Function:                                                                                    #
    #  It is an Auto Login bot. It also extracts specific data from table and store it in Microsoft Excel. #
#ce ########################################################################################################

Local $sExcelPath = @ScriptDir
Local $sImagePath = "Y:\88_TanglinHaltRd\IllegalParking\88_TanglinHaltRd_Cam1\2017-02-24"

Local $oIE
Local $oDivs
Local $aPageCount, $iPageCount
Local $aTable, $oTables
Local $aTimeView[0][7]
Local $oUserName, $oPassWord
Local $sUserName = "Oneberry"
Local $sPassWord = "Qwer1234"
$oIE = _IECreate("http://www.timeview2.net/", 1); Opens IE browser
WinSetState("[ACTIVE]", "", @SW_MAXIMIZE);Maximize Internet Explorer window
$oForms = _IETagNameGetCollection($oIE, "form")
For $oForm In $oForms
    If $oForm.id = "UserLoginForm" Then
        $oUserName = _IEFormElementGetObjByName($oForm, "data[User][username]")
        _IEFormElementSetValue($oUserName, $sUserName)
        $oPassWord = _IEFormElementGetObjByName($oForm, "data[User][password]")
        _IEFormElementSetValue($oPassWord, $sPassWord)
        _IEFormSubmit($oForm)
    EndIf
Next

_IENavigate($oIE, "http://www.timeview2.net/alarms")
$oDivs = _IETagNameGetCollection($oIE, "div")
For $oDiv In $oDivs
    If $oDiv.ClassName = "alarms index" Then
        $aPageCount = _StringBetween($oDiv.InnerText, "Page 1 of ", ", showing ")
        If @error Then Exit
        $iPageCount = Number($aPageCount[0])
    EndIf
Next

For $i = 1 To $iPageCount
    _IENavigate($oIE, "http://www.timeview2.net/alarms/index/group:All%20Groups/page:" & $i, 1)
    $oTables = _IETagNameGetCollection($oIE, "table")
    For $oTable In $oTables
        If $oTable.ClassName = "pageindex" Then
            $nTable = $oTable.NextElementSibling
            $aTable = _IETableWriteToArray($nTable, True)
            If UBound($aTable) - 1 > 0 And UBound($aTable, 2) = 7 Then
                If $i = 1 Then
                    _ArrayConcatenate($aTimeView, $aTable, 0)
                Else
                    _ArrayConcatenate($aTimeView, $aTable, 1)
                EndIf
                ExitLoop
            EndIf
        EndIf
    Next
 Next
 Local $oExcel = _Excel_Open(); Opens Microsoft Excel

Local $oWorkbook = _Excel_BookNew($oExcel); Create new Microsoft excel
WinSetState("[ACTIVE]", "", @SW_MAXIMIZE);Maximize Microsoft Excel window
_ArrayColDelete($aTimeView, 6)
 _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aTimeView, "A1"); write the data to A1 cell of Microsoft excel
$oWorkbook.Activesheet.range("A1:G1").Font.Bold = True ; set font to bold
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "Pictures", "G1"); write 'Pictures' to G1 cell
$oWorkbook.Activesheet.range("E2:F100").NumberFormat = "yyyy/mm/dd" & " hh:mm:ss" & " AM/PM" ;set E2 to F100 cell to date format in yyyy/mm/dd
$oExcel.ActiveSheet.Columns("A:G").AutoFit
$oColumns = $oExcel.Columns("G")
$oColumns.ColumnWidth = 40.86
$oRows = $oExcel.Rows("2:100");Expands row 2 until 100
$oRows.RowHeight = 198.75;Automatically expands row

Local $sWorkbook = $sExcelPath & "\" & StringReplace(StringReplace($aTimeView[UBound($aTimeView) -1][4] & "-" & $aTimeView[1][4], ":", "-"), " ", "_") & "_TimeView.xlsx"
Local $oExcel = _Excel_BookSaveAs($oWorkbook, $sWorkbook)
$aWorkBook = _Excel_RangeRead($oWorkbook)
Local $aImagePath
For $i = 1 To UBound($aWorkBook) - 1
    $aImagePath = _FileListToArray($sImagePath, "m" & StringTrimLeft($aWorkBook[$i][5], 2) & "*.jpg", 1, True)
    If UBound($aImagePath) >= 2 Then
        _Excel_PictureAdd($oWorkbook, Default, $aImagePath[1], "G" & $i + 1)
        $aImageSize = _ImageSize($aImagePath[1])
        $oRow = $oExcel.Rows($i + 1)
        $oRow.RowHeight = $aImageSize[1]
    EndIf
Next

;~ Malkeys code: https://www.autoitscript.com/forum/topic/108578-getting-image-size/
Func _ImageSize($sFileName)
    Local $aRet[2], $sExt = StringRegExpReplace($sFileName, "^.*\.", "")
    If (FileExists($sFileName) = 0) Or _
            ((StringLen($sExt) = 3) And (StringRegExp($sExt, "(?i)(bmp|gif|jpg|png|tif|emf|wmf)") = 0)) Or _
            ((StringLen($sExt) = 4) And (StringRegExp($sExt, "(?i)(tiff|jpeg)") = 0)) Then _
            Return SetError(1, 0, $aRet)
    Local $ghGDIPDll = DllOpen("GDIPlus.dll")
    Local $tInput = DllStructCreate("int Version;ptr Callback;int NoThread;int NoCodecs")
    Local $tToken = DllStructCreate("ulong_ptr Data")
    DllStructSetData($tInput, "Version", 1)
    DllCall($ghGDIPDll, "int", "GdiplusStartup", "ptr", DllStructGetPtr($tToken), "ptr", DllStructGetPtr($tInput), "ptr", 0)
    Local $aImage = DllCall($ghGDIPDll, "int", "GdipLoadImageFromFile", "wstr", $sFileName, "ptr*", 0)
    Local $aResult = DllCall($ghGDIPDll, "int", "GdipGetImageWidth", "handle", $aImage[2], "uint*", -1)
    $aRet[0] = $aResult[2]
    $aResult = DllCall($ghGDIPDll, "int", "GdipGetImageHeight", "handle", $aImage[2], "uint*", 0)
    $aRet[1] = $aResult[2]
    DllCall($ghGDIPDll, "int", "GdipDisposeImage", "handle", $aImage[2])
    DllCall($ghGDIPDll, "none", "GdiplusShutdown", "ptr", DllStructGetData($tToken, "Data"))
    DllClose($ghGDIPDll)
    Return SetError(0, 0, $aRet)
EndFunc ;==>_ImageSize

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Similar Content

    • By rudi
      Hello,
      the following script is running quite nicely for a friend of mine doing a year of work and travel to have a backup at home for all the pics and movies taken with her mobile phone,
       
      The facts:
       
      At home: a VMWARE virtual machine with dropbox installed for her DB account
      On-the-Road: One mobile phone, Camera Uploads are activated for her dropbox account, one Laptop.
       
       
      The idea is to have a copy of *ALL* pictures and movies taken with the mobile phone *OUTSIDE* the dropbox folder, so that the DB Max Size is never exceeded.
      The script is running at home and doing this:
      Copy all content from the "Camera Upload Folder" within the dropbox folder to some folder *OUTSIDE* the DB folder Move all content from the "Camera Upload Folder" do some other folder *INSIDE* the DB folder to indicate, that the backup copies at home were done successfully  
      Dropbox is also installed on the Laptop she has with her. So on the Laptop she checks from time to time the destination folder inside the dropbox folder and moves the pics / movies on the laptop to some other folder outside the dropbox as well. By that final step the images are moved out of the dropbox on the mobile phone as well, so that there is always space left to sync more pics / movies.
       
      As moving pics / movies out of the dropbox folder on the laptop doesn't touch the copies in the mobile phone's "Gallery", she has all the pics / movies at all three locations:
      Mobile Phone Laptop VM at home  
      The folder names propably are different for non-German localized Windows and Dropbox, just modify them to meet your localization.
      DBox-Sync.au3
    • By FrancescoDiMuro
      Good evening everyone
      I am working with Word UDF ( thanks @water! ), and, especially, with the function _Word_DocFindReplace().
      The replace does work everywhere in the document, but, it does not work in Headers or Footers.
      Am I missing something or am I forced to use the code below?
      I have already looked in the Help file ( about _Word_DocFindReplace() ), but there are no mentions about replace text in Headers/Footers.
      Sub FindAndReplaceFirstStoryOfEachType() Dim rngStory As Range For Each rngStory In ActiveDocument.StoryRanges With rngStory.Find .Text = "find text" .Replacement.Text = "I'm found .Wrap = wdFindContinue .Execute Replace:=wdReplaceAll End With Next rngStory End Sub Thanks everyone in advance


      Best Regards.
    • By nacerbaaziz
      hello dears
      First I would like to apologize to you for my many questions.
      I have a new question if you allow
      I am programming an audio player for blinds
      I had a problem
      I put an option in the folders context menu to Opens the audio files that in the selected folder
      I did not know how to make the Autoit Search the subfolders
      Please provide an example of how to search for  files in the subfolders
      Let's say, for example, MP3 files
      Just give me a simple example and I will try to modify it as appropriate for the program I designing it
      Please help me to find the solution
      Thanks in advance
    • By KimberlyJillPereira
      How to use the date and time in Microsoft Excel and use it find to find it in Windows explorer under date modified(date and time) if it matches then extract that picture and paste it in Microsoft Excel. I know have to use FileGetTime() but not sure how to do. Please help me. Thank you.


    • By PINTO1927
      Hello guys, I'm working on this script:
      $search = GUICtrlCreateButton("Search", 10, 10, 90, 30) $find = GUICtrlCreateInput("", 710, 10, 200, 25) GUICtrlSetState($find, $GUI_DISABLE) $ok_find = GUICtrlCreateButton("Find", 925, 10, 65, 25) GUICtrlSetState($ok_find, $GUI_DISABLE)  
      Case $search GUICtrlSetState($ok_trova, $GUI_ENABLE) GUICtrlSetState($trova, $GUI_ENABLE) _GUICtrlListView_SimpleSort($list, $g_bSortSense, 0, False) _GUICtrlListView_DeleteAllItems($list) $File_txt = _FileListToArray("\\PATH\", "*.ini", $FLTA_FILES, True) Local $aLines, $File_txt For $i = 1 To $File_txt[0] _FileReadtoArray($File_txt[$i], $aLines) For $j = 1 To $aLines[0] GUICtrlCreateListViewItem($aLines[$j], $list) Next Next For $i2 = 0 To _GUICtrlListView_GetColumnCount($list) _GUICtrlListView_SetColumnWidth($list, $i2, $LVSCW_AUTOSIZE_USEHEADER) Next Case $ok_find $iI = _GUICtrlListView_FindInText($list, GUICtrlRead($input_find)) MsgBox($MB_SYSTEMMODAL, "Information", "Target Item Index: " & $iI) _GUICtrlListView_EnsureVisible($list, $iI) through a inputbox I would search for the desired word and highlight it in the GuiCtrlListView.
      Can you help me?
×
×
  • Create New...