Jump to content

Recommended Posts

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.

Link to post
Share on other sites

Here is my attempt:

#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

 

Link to post
Share on other sites

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

 

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
  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...