Jump to content
KimberlyJillPereira

How to copy the first 10 characters in excel and paste it in the search box??

Recommended Posts

May I know how to copy the first 10 characters in excel and paste it in the file explorer textbox? How to do? I know that you show use the StringLeft() function but I am not sure how to do. Please help me. Thank you! :)

kimberlyyy.png

lll.png

Share this post


Link to post
Share on other sites

This will search for the images throughout Y:\ and add them to Excel if found:

#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:\"

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 = _FileListToArrayRec($sImagePath, "m" & StringTrimLeft($aWorkBook[$i][5], 2) & "*.jpg", 1, 1, 0, 2)
    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

The pictures are not displayed but the name of the excel file was changed. Actually what I wanted to do open file explorer then I wanted to copy the first 10 characters which is the date and paste it in the file explorer searchbox and then select the first folder which has the same date as the copied date. then in that folder has a list of pictures. As seen in the picture below for the first picture the time is 10:59 AM. Is it possible to use the time in excel only hour and minute and use the date modified in file explorer and do a comparision for example like if (datemodified< time_excel)  then condition is to display the picture. Please help me. I have to complete this as I am doing this for my internship. Thanks alot @Subz.:)

j.png

aaq.png

qwww.png

Share this post


Link to post
Share on other sites

The _FileListToArrayRec should automatically do the searching for you, it does a recursive search of the entire "Y:" drive, in a new script what does the following return, it should return an array of all .jpg in the Y: drive with the full file path, can you please confirm?

#include <Array.au3>
#include <File.au3>
Global $aImagePath = _FileListToArrayRec("Y:\", "m*.jpg", 1, 1, 0, 2)
_ArrayDisplay($aImagePath)

 

Share this post


Link to post
Share on other sites

It only shows the links of the images but doesnt show the images and I wanted the images to be shown in G2 in Excel. Is it use _Excel_PictureAdd() function then will display picture?? It is almost solving but not solved. Please help. Thanks alot.:):).

qqad.png

mmnb.png

Share this post


Link to post
Share on other sites

From Row 80 of the code above can you replace with the following code and then let me know what the results are:

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)
_ArrayDisplay($aWorkBook, "Workbook Array")
Local $aImagePath
For $i = 1 To UBound($aWorkBook) - 1
    $aImagePath = _FileListToArrayRec($sImagePath, "m" & StringTrimLeft($aWorkBook[$i][5], 2) & "*.jpg", 1, 1, 0, 2)
    MsgBox(48, "Filename Mask", "m" & StringTrimLeft($aWorkBook[$i][5], 2) & "*.jpg")
    _ArrayDisplay($aImagePath, "Jpg Filenames based upon Mask")
    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

 

Share this post


Link to post
Share on other sites

"C:\Users\User\Desktop\1222eee.au3" (78) : ==> Variable used without being declared.:
Local $sWorkbook = $sExcelPath & "\" & StringReplace(StringReplace($aTimeView[UBound($aTimeView) -1][4] & "-" & $aTimeView[1][4], ":", "-"), " ", "_") & "Book1.xlsx"
Local $sWorkbook = ^ ERROR

 

I get this error

Share this post


Link to post
Share on other sites

You'll need to post the entire code as I'm not sure where the error is coming from, all I did was add in some checking (_ArrayDisplay) which should not have any effect on the script.

Share this post


Link to post
Share on other sites

I managed to automatically go into the date folder but I still cant match excel time with the filename time. Please help me thank you.

Local $sString = StringLeft($rRangeRead,8); only read the first 10 characters

$exceldate = StringRegExpReplace($sString, "\A(\d{4})(\d{2})(\d{2})","$1-$2-$3");to put slash inbetween the year month and day
$path = "Y:\7_JlnBatu\IllegalParking\7_JlnBatu_cam2";opens y drive ftp
$files = ($path & "\" & $exceldate);go into the place where it has all images

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 <Date.au3>
#include <FileConstants.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"

$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);delete the seventh column 'Actions'
$rangewrite = _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:F1000").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:1000");Expands row 2 until 1000
$oRows.RowHeight = 198.75;Automatically expands row
Local $rRangeRead = _Excel_RangeRead($oWorkbook, Default, "F2")
Local $sString = StringLeft($rRangeRead,8); only read the first 10 characters
Local $wString = StringMid($rRangeRead,11,2);read the minute
Local $qString = StringMid($rRangeRead,9,2);read the hour
$exceldate = StringRegExpReplace($sString, "\A(\d{4})(\d{2})(\d{2})","$1-$2-$3");to put slash inbetween the year month and day
;Local $www = ClipPut($exceldate);copies the text of 10 characters
$path = "Y:\7_JlnBatu\IllegalParking\7_JlnBatu_cam2";opens y drive ftp
$files = ($path & "\" & $exceldate);go into the place where it has all images


;   _Excel_PictureAdd($oWorkbook, Default,"G2", $sPicture, 250, 300, 300, 250)

ShellExecute($files)
WinSetState("[ACTIVE]", "", @SW_MAXIMIZE);Maximize Internet Explorer window

Local $sString = StringLeft($rRangeRead, 16)
Local $oString = StringMid($sString, 3, 12)
;MsgBox("Hi","Hi",$oString)
Local $filename = "m" & $oString
Local $hfilename = $files & "\" & $filename
ShellExecute($hfilename)
;Local $www = ClipPut($filename);copies the text of 10 characters

;Send("{TAB 4}");to select the textbox
;Send("^v");paste it into the textbox
;Sleep(10000);wait for 10 seconds
;Local $sPicture = Send("{DOWN 2}")
;Sleep(2000)
;Local $mRangeRead = _Excel_RangeRead($oWorkbook, Default, "G2")
$oWorkBook.ActiveSheet.Range("G2").Select
; $filecopy = FileCopy($filename,$mRangeRead,$FC_OVERWRITE + $FC_CREATEPATH)
;_ClipPutFile($filecopy)

;_ArrayDisplay($aResults)
;~ Save to CSV
Local $hFileOpen = FileOpen(@ScriptDir & "\Results.csv", 2)
For $i = 0 To UBound($aResults) - 1
    FileWrite($hFileOpen, '"' & _
    StringStripWS($aResults[$i][0], 7) & '","' & _
    StringStripWS($aResults[$i][1], 7) & '","' & _
    StringStripWS($aResults[$i][2], 7) & '","' & _
    StringStripWS($aResults[$i][3], 7) & '","' & _
    StringStripWS($aResults[$i][4], 7) & '","' & _
    StringStripWS($aResults[$i][5], 7) & '"' & @CRLF)
Next
FileClose($hFileOpen)
 

 

This is my full code. please help me. Thanks @Subz

Share this post


Link to post
Share on other sites

Can you try the following code, just a few notes:

You can't save a csv with images, it has to be xls or xlsx.
The method I'm using to insert images is using Excel UDF, the use of send, clip functions are not recommended.

#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:\7_JlnBatu\IllegalParking\7_JlnBatu_cam2"

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 = _FileListToArrayRec($sImagePath & "\" & StringFormat("%4i-%2i-%2i", StringLeft($aWorkBook[$i][5], 4), StringMid($aWorkBook[$i][5], 5, 2), StringMid($aWorkBook[$i][5], 7, 2)), "m" & StringTrimLeft($aWorkBook[$i][5], 2) & "*.jpg", 1, 1, 0, 2)
    ConsoleWrite($sImagePath & "\" & StringFormat("%4i-%02i-%02i", StringLeft($aWorkBook[$i][5], 4), StringMid($aWorkBook[$i][5], 5, 2), StringMid($aWorkBook[$i][5], 7, 2)) & @CRLF)
    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
Local $sString = StringLeft($rRangeRead, 16); reads yyyymmddhhmmss
Local $oString = StringMid($sString, 3, 12); reads last two number of year e.g. yymmddhhmmss

Local $filename = "m" & $oString; puts string letter m together with yymmddhhmmss
Local $hfilename = $files & "\" & $filename &   "_GE_JB1_7_2_DYL.jpg"

I have a problem in selecting the filename because of the digits after the seconds for example like m170503123455434_GE_JB1_7_2_DYL. Because I dont know how to get those three numbers but the rest of it I can. Is there a way to get partially the filename and select the filename. Is there a way to do it?? Please help me and thank you very much.

Share this post


Link to post
Share on other sites

If you look at the code in the script:

$aImagePath = _FileListToArrayRec($sImagePath & "\" & StringFormat("%4i-%2i-%2i", StringLeft($aWorkBook[$i][5], 4), StringMid($aWorkBook[$i][5], 5, 2), StringMid($aWorkBook[$i][5], 7, 2)), "m" & StringTrimLeft($aWorkBook[$i][5], 2) & "*.jpg", 1, 1, 0, 2)
    ConsoleWrite($sImagePath & "\" & StringFormat("%4i-%02i-%02i", StringLeft($aWorkBook[$i][5], 4), StringMid($aWorkBook[$i][5], 5, 2), StringMid($aWorkBook[$i][5], 7, 2)) & @CRLF)

So for instance if Alarm Received = "20170503201956" the _FileListToArrayRec should resolve to:

$aImagePath = _FileListToArrayRec("Y:\7_JlnBatu\IllegalParking\7_JlnBatu_cam2\2017-05-03", "m170503201956*.jpg", 1, 1, 2)

So as you can see it's searching the folder "Y:\7_JlnBatu\IllegalParking\7_JlnBatu_cam2\2017-05-03" for any files with the name "m170503201956*.jpg" (note * is wild card).

What you should do is put an _ArrayDisplay below the line above to see if its finding the files.  I have tested this by renaming some .jpg files as m17... and they were all inserted into Excel fine.

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 ThePoro
      Hello,
      I have a problem with getting first 10 characters with a big txt file, about 50k lines. I only want 10 characters of each line and save it into another file. I tried to use Stringleft every line and save it into another file but it was really slow to do it. I tried to save it into a variation like $var=$var&stringleft($file,10)&@CRLF. It's faster but still really slow.
      Can anybody help me with this
      Thank you so much
    • By XinYoung
      HI! ... this is a big one (at least for me) 
      You guys previously helped me copy the used range in column A and paste them into a Website one at a time in a loop. Cool! Now, for another function, I have 2 columns, A and B, and two input boxes in the Website. I'm having a hard time replicating the loop for the 2 columns. 
      This is how I'm opening the Excel workbook (copied from the previous function that only had 1 column). I need to also get the used range in column B.
      Func OpenExcelForCopy() Global $aBBTableData Global $oExcel = _Excel_Open() Global $oWorkbook = _Excel_BookOpen($oExcel, $ChosenFileName, Default, True, True) $oExcel.Sheets("CopyCourses").Activate ;~ Get all used cells in column A:A Global $aSearchItems = _Excel_RangeRead($oWorkbook, 1, $oWorkbook.Sheets("CopyCourses").Usedrange.Columns("A:A")) ;~ Duplicate the $aSearchItems Array Global $aSearchResult = $aSearchItems ;~ Loop through the array starting at 0 until the end of the array which is (Ubound($aSearchItems) - 1) For $i = 0 To UBound($aSearchItems) - 1 $aSearchResult[$i] = SearchCourseForCopy($aSearchItems[$i]) Next _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1") Finished() EndFunc ;==>OpenExcelForCopy Then we eventually get here. I don't think anything needs to change here but I'm not sure. This is where I paste the data from Column A into an input field (which is a search tool in a website). If the search is good, then we get to the tricky part...
      ;~ OK, we logged in and we searched for a course. Lets COPY it! Func CopyCourseBegin() Local $sResult $iSearchIndex = _ArraySearch($aBBTableData, "Course ID", 0, 0, 0, 1, 1, 0) ;~ If the course was not found, do this. If $iSearchIndex = -1 Then ;~ MsgBox(4096, "Search Error", "Item not found") $sResult = "Source Not Found" _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1") ;~ Now go back to the Excel sheet and search for the next one. ;~ If the course was found, begin the COPY process. Else For $i = 0 To UBound($aSearchItems) - 1 $aSearchResult[$i] = CopyCourseNow($aSearchItems[$i]) Next $sResult = "Copied" _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1") EndIf Return $sResult EndFunc ;==>CopyCourseBegin This is the "tricky part" where I'm confused. I can copy and paste what's in column A just fine, but I can't manage to replicate it for column B. I need to paste whats in Column B into "destinationCourseId"
      ;~ The course search was successful. COPY the course now. Func CopyCourseNow($_sSearchResult) ;~ Navigate to the course copy page. _IENavigate($oIE, $urlBBCourseCopy) ;~ Copy the SOURCE course ID from the Excel sheet ;~ Paste whats copied from column A into the Source Course ID text box Local $oForm = _IEGetObjByName($oIE, "selectCourse") Local $oSearchString = _IEFormElementGetObjByName($oForm, "sourceCourseId") _IEFormElementSetValue($oSearchString, $_sSearchResult) ;~ Paste whats copied from column B into the Destination Course ID text box ?!?!?!?! Local $oForm = _IEGetObjByName($oIE, "selectCourse") Local $oSearchString = _IEFormElementGetObjByName($oForm, "destinationCourseId") _IEFormElementSetValue($oSearchString, $_sSearchResult) ;~ Just exit cause im stuck :( _Exit() EndFunc ;==>CopyCourseNow After I paste the data from column A into "sourceCourseId" and column B into "destinationCourseId", I'll make it do some stuff. Then I need it to loop around until the used ranges in column A & B is finished.
      Does the entire code need to change now that there's two columns?
       
       
    • By Epic007
      Hey guys I'm stuck with a program.....I need a make an automation which copies text from a chat bot app and paste it in notepad to log the replies of my chat bot and to analyse stuff....a lil help pls
       
       
       
       
       
       
       
      Thanks in advance
    • By AnonymousX
      Hello,
      I'm trying to write a script that moves copies excel cells into an array. I'll than manipulate the values and send array into another program. 
      I don't want range to be specific to a workbook, or sheet, or set of cells.
      I want user to be able to highlight desired cells and to copy either normally ("Ctrl+C") or by a hotkey ("Alt+C"). 
      Could someone help me with this?
      Thank you,
      I've tried to write the framework: (edited)
      #include <MsgBoxConstants.au3> #include <Array.au3> #include <Excel.au3> HotKeySet("!v", "Pastedata") While True Sleep(1000) WEnd func Makearray() local $bArray ;User has cells already copied ;Convert clipboard into an array ;I don;t know how excel stores data to clipboard so don;t know how to bring it into array _Arraydisplay($bArray) MsgBox(0,0,$bArray) return $bArray endfunc func Pastedata() Local $aArray MsgBox(0,0,"wait",1) ;make array based on assumption user has already copied a range to clipboard $aArray = Makearray() ;paste code ;don;t worry about this I got the rest endfunc  
×
×
  • Create New...