Excel RangeFind help

[ch9eseboy]

Hello,

 

I'm rather new to Autoit and have been reading these forums.  Generally, not sure if I'm going the right direction on this... so wanted to see if anyone can give me some specific guidance and/or resource page.

I'm basically trying to show the corresponding date, time, field #, and fruit to each Pick occurrence.

Should I be using VLOOKUP or something with the attributes under the RangeFind (or maybe it's RangeRead...)?

===

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

;open excel
Local $var = FileOpenDialog(">>>Select the Excel file","Desktop", "Excel (*.xlsx;*.xls)")
Local $oExcel_1 = _Excel_Open ()
Local $oWorkbook = _Excel_BookOpen($oExcel_1, $var)

Local $aResult_1 = _Excel_RangeFind($oWorkbook,"Pick")
_ArrayDisplay($aResult_1, "Summary", "")

===

Date Time Field 1 Field 2     Orange Watermelon Cherry Lemon Apple               20Jul2020,Mon 8:00     Pick       8:30

 

Any point in the right direction is much appreciated!

 

Apologies if I'm posting this incorrectly.

[Subz]

Are you putting this into a gui or is this just occurring in Excel?

[ch9eseboy]

This is occurring in an Excel.  I'm able to attach it now.

Pick Schedule.xlsx

[ch9eseboy]

Just wanted to check if anyone is able to give some help.  I'm still trying to find a way to reference different columns on the array display.

[Subz]

I could do it in an array, otherwise you'd need to reformat the workbook, to reference the array you use $aWorkbookRange[$iRow][$iCol] (where $iRow equals the row number and $iCol equals the column number).

[Subz]

Here is my attempt:

expandcollapse popup

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

Local $sWorkbook = "E:\Pick Schedule.xlsx"
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox(4096, "Error", "Error creating the Excel application object.")

Local $oWorkbook = _Excel_BookAttach($sWorkbook)
If @error Then
    $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)
    If @error Then
        MsgBox(4096, "Error", "Error opening workbook '" & $sWorkbook & "'.")
        _Excel_Close($oExcel)
        Exit
    EndIf
EndIf

Local $aWorkbook = _Excel_RangeRead($oWorkbook, Default, Default, 2, True)
Local $aPicks[0][4], $sLastDate, $sField, $sDateTime, $aDatePart, $sDatePart, $aTimePart, $sTimePart
For $i = 0 To UBound($aWorkbook) - 1
    If $i >= 2 Then
        ;~ Set the date in column 0 for each row
        If StringStripWS($aWorkbook[$i][0], 8) <> "" Then
            $sLastDate = $aWorkbook[$i][0]
        Else
            $aWorkbook[$i][0] = $sLastDate
        EndIf
    EndIf
    For $j = 2 To UBound($aWorkbook, 2) - 1
        ;~ Set the Field in row 0 for each column
        If $i = 0 Then
            If StringStripWS($aWorkbook[$i][$j], 8) <> "" Then
                $sField = $aWorkbook[$i][$j]
            Else
                $aWorkbook[$i][$j] = $sField
            EndIf
            ContinueLoop
        EndIf
        ;~ Find each cell with the word "Pick"
        If StringStripWS($aWorkbook[$i][$j], 8) = "Pick" Then
            ;~ Convert the Excel Date/Time to proper format
            ;~ nb: Needed to change the _ConvertSerialDateTime written by Water time format was incorrect
            $sDateTime = _ConvertSerialDateTime($aWorkbook[$i][0] + $aWorkbook[$i][1])
            ;~ Split the Date/Time
            _DateTimeSplit($sDateTime, $aDatePart, $aTimePart)
            $sDatePart = $aDatePart[3] & "/" & $aDatePart[2] & "/" & $aDatePart[1]
            $sTimePart = _12HourFormat($aTimePart[1], $aTimePart[2])
            _ArrayAdd($aPicks, $sDatePart & "|" & $sTimePart & "|" & $aWorkbook[0][$j] & "|" & $aWorkbook[1][$j])
            $aWorkbook[$i][2] = $sDateTime
        EndIf
    Next
Next
_ArrayDisplay($aPicks)

Func _ConvertSerialDateTime($nDT)
    Local Const $dtExcel = '1899/12/31 00:00:00'
    Local $iDate = Int($nDT)
    Local $iTime = Mod($nDT, 1) - 1
    $iTime = Int(24 * 3600 * $iTime)
    $dtRes = _DateAdd('D', $iDate, $dtExcel)
    $dtRes = _DateAdd('s', $iTime, $dtRes)
    Return $dtRes
EndFunc

Func _12HourFormat($_iHour = "", $_iMin = "00")
    If $_iHour = "" Or IsInt($_iHour) = False Then Return
    Select
        Case $_iHour <= 11
            Return StringFormat("%d:%02d AM", $_iHour, $_iMin)
        Case Else
            Return StringFormat("%d:%02d PM", $_iHour, $_iMin)
    EndSelect
EndFunc

 

[ch9eseboy]

This most certainly works.  Thank you water!

 

I've been trying to understand this for awhile now...

I thought I could simply increase the numbers to display other columns.  But this is much more complicated than I expected.  So thank you, as I don't think I would have arrived to a solution anytime.

 

For $i = 0 To UBound($aWorkbook) - 1
    If $i >= 2 Then
        ;~ Set the date in column 0 for each row
        If StringStripWS($aWorkbook[$i][0], 8) <> "" Then
            $sLastDate = $aWorkbook[$i][0]
        Else
            $aWorkbook[$i][0] = $sLastDate
        EndIf

 

[Subz]

water??

[ch9eseboy]

sorry...! i had a mental block of subzero and water after reading many threads.  Thanks Subz!!!