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

KimberlyJillPereira · April 20, 2017

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.

KimberlyJillPereira · April 20, 2017

@kylomas , @BrewManNH please help. thanks.

KimberlyJillPereira · April 20, 2017

@kylomas , @BrewManNH please help. thanks.

KimberlyJillPereira · April 24, 2017

@LarsJ please help

Subz · April 26, 2017

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

KimberlyJillPereira · April 27, 2017

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

Subz · April 27, 2017

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

KimberlyJillPereira · April 28, 2017

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

KimberlyJillPereira · April 28, 2017

#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

KimberlyJillPereira · April 28, 2017

The full code is above.

Subz · April 28, 2017

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

Sign In

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

Recommended Posts

KimberlyJillPereira

KimberlyJillPereira

KimberlyJillPereira

KimberlyJillPereira

Subz

KimberlyJillPereira

Subz

KimberlyJillPereira

KimberlyJillPereira

KimberlyJillPereira

Subz

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members

Similar Content

Dropbox Travel Backup Helper

[Solved] _Word_DocFindReplace() doesn't find and replace in Headers/Footers 1 2

How to Find Files in Subfolders

date modified in file explorer?

Find and highlights in to GuiCtrlListView

Browse

AutoIt Resources

Release

Beta