Jump to content
KimberlyJillPereira

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

Recommended Posts

KimberlyJillPereira

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
KimberlyJillPereira

@LarsJ please help

Share this post


Link to post
Share on other sites
Subz

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
KimberlyJillPereira

@Subz I tried using this code but it didnt work. Please help me. Thanks.

Share this post


Link to post
Share on other sites
Subz

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
KimberlyJillPereira

@Subz This is the error which I get--> IE.au3 T3.0-2 Warning from function _IEAttach, $_IESTATUS_NoMatch.

Share this post


Link to post
Share on other sites
KimberlyJillPereira

#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
KimberlyJillPereira

The full code is above.

Share this post


Link to post
Share on other sites
Subz

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

    • FrancescoDiMuro
      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.
    • nacerbaaziz
      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
    • KimberlyJillPereira
      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.


    • PINTO1927
      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?
    • Wicked_Caty
      By Wicked_Caty
      I've written a bot that is loading down  pictures from a website. Basically, it opens the website, saves the picture via context menu, and hits a button for loading the next pic. Unfortunately, the pics and my internet connection aren't always the same and it takes longer to load at times. Right now I've got a sleep(3000) in my code, but it isn't very efficient. I waste time if it loads faster than 3000ms, and the program fails if it takes more than 3000ms. So I wanted to make a function that waits until the picture has loaded, and then saves it. 
      I don't know if that causes any problems with possible functions, but it isn't the tab that's loading. The loading circle doesn't appear. It's something on the website.
      I've adapted to program to chrome, so I'd have to rewrite it partially for the IE functions, so it'd be nice if there was a solution for chrome. --- Thanks!
×