Jump to content

Recommended Posts

Posted

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.

Posted

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.

Posted

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).

Posted

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

 

Posted

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

 

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...