Less than today's date

DigDeep · May 14, 2017

I am reading the Date column in the excel attached here to check if any row has date less than today "NowDate()", it will give the msgbox for that Title Row.

E.g.: if it reads the 2nd row as less than today for 4/30/2017, it should give the msgbox as Test 2.

I have not written the complete code here but I am getting the Date row going wrong.

Can someone please help in letting me know how can this be done?

#include <Excel.au3>


$List = "C:\Temp"
    Local $oExcel1 = _Excel_Open(False)
    Local $oWorkbook1 = _Excel_BookOpen($oExcel1, $List & "\Test.xls")

    Local $ColNum1 = $oWorkbook1.ActiveSheet.UsedRange.Columns.Count ; Get last column Number
    Local $ColName1
    For $l = $ColNum1 To 1 Step -1
        Local $DIR = @MyDocumentsDir & '\Test'
        Local $Results = $DIR & "\Results"
        DirCreate($Results)
        Local $FilePath1 = $Results & '\Final.txt'
        $FileOpen1 = FileOpen($FilePath1, 2)
        $FileWrite1 = FileWrite($FileOpen1, $l)
        FileClose($FilePath1)

        Local $TotalCol1 = FileRead($FilePath1)
        Local $ColName1 = _Excel_ColumnToLetter($TotalCol1) ; Get last column Header Name
        Local $Lines = $oWorkbook1.ActiveSheet.UsedRange.Rows.Count
        For $M = $Lines To 1 Step -1
            Local $sTabB = _Excel_RangeRead($oWorkbook1, Default, $ColName1 & $M)
            $Date = _NowDate()
            If $sTabB < $Date Then
                MsgBox(0, '', $sTabB)
            EndIf
        Next

        Next
_Excel_Close($oExcel1)

Test.xls

Edited May 14, 2017 by DigDeep

benners · May 14, 2017

This is my attempt. There's probably a better way. Credit to AdamUL for the _DateStandardToCalcDate() which I modified

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

Local $o_Excel = _Excel_Open(False)
Local $o_Workbook = _Excel_BookOpen($o_Excel, @ScriptDir & "\Test.xls")
Local $i_Rows = $o_Workbook.ActiveSheet.UsedRange.Rows.Count
Local $s_ColumnLetter = _Excel_ColumnToLetter($o_Workbook.ActiveSheet.UsedRange.Columns.Count)

Local $as_RangeRead = _Excel_RangeRead($o_Workbook, Default, $s_ColumnLetter & '2:' & $s_ColumnLetter & $i_Rows)
Local $s_NowDate = Number(StringRegExpReplace(_NowCalcDate(), '\D', ''))

For $i = 0 To UBound($as_RangeRead) - 1
    If _DateStandardToCalcDate($as_RangeRead[$i]) < $s_NowDate Then MsgBox(0, 'Date Earlier', 'Row: ' & $i + 2)
Next

_Excel_Close($o_Excel)

Func _DateStandardToCalcDate($sDate)
    If Not StringRegExp($sDate, "^(\d{1,2})/(\d{1,2})/(\d{4})$") Then Return SetError(1, 0, "")
    If @error Then Return SetError(1, 0, "")

    Local $sDateNew = StringRegExpReplace($sDate, "(\d{2})/(\d{2})/(\d{4})", "$3/$1/$2")
    $sDateNew = StringRegExpReplace($sDateNew, "(\d{2})/(\d)/(\d{4})", "$3/$1/0$2")
    $sDateNew = StringRegExpReplace($sDateNew, "(\d)/(\d{2})/(\d{4})", "$3/0$1/$2")
    $sDateNew = StringRegExpReplace($sDateNew, "(\d)/(\d)/(\d{4})", "$3/0$1/0$2")

    Return Number(StringRegExpReplace($sDateNew, '\D', ''))
EndFunc   ;==>_DateStandardToCalcDate

There's no error checking for failed objects etc

Edited May 14, 2017 by benners
Missed closing Excel

Subz · May 14, 2017

Another way:

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

$List = @ScriptDir
    Local $oExcel1 = _Excel_Open(False)
    Local $oWorkbook1 = _Excel_BookOpen($oExcel1, $List & "\Test.xls")
    Local $aWorkBook = _Excel_RangeRead($oWorkbook1)
    For $i = 1 To UBound($aWorkBook) - 1
        $sWorkDate = $aWorkBook[$i][UBound($aWorkBook, 2) - 1]
        If _DateDiff('d', StringFormat("%04i/%02i/%02i", StringRight($sWorkDate, 4), StringLeft($sWorkDate, 2), StringMid($sWorkDate, 4, 2)), _NowCalc()) < 0 Then
            MsgBox(0,'', $aWorkBook[$i][0] & @CRLF & $aWorkBook[$i][1] & @CRLF & $aWorkBook[$i][2] & @CRLF & $aWorkBook[$i][3])
        EndIf
    Next
_Excel_Close($oExcel1)

DigDeep · May 14, 2017

I like both and can use at someway I think of.

However, there's some additions here.

@benners Instead of showing the msgbox with Row numbers, can we get the text from C3, C4, C5? That's what I would need.

@Subz I changed at the end as: _NowCalc()) > 0 Then

and got the results but keeping anyways, it is not reading all the rows correctly. Skipping few of them. Also, if I try to delete any of the columns, the code gives error at MsgBox line.

benners · May 14, 2017

You can but the results will always be the same value no matter the row. Unless you mean columns A,B and C of the matching row

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

Local $o_Excel = _Excel_Open(False)
Local $o_Workbook = _Excel_BookOpen($o_Excel, @ScriptDir & "\Test.xls")
Local $i_Rows = $o_Workbook.ActiveSheet.UsedRange.Rows.Count
Local $s_ColumnLetter = _Excel_ColumnToLetter($o_Workbook.ActiveSheet.UsedRange.Columns.Count)
Local $as_RangeRead = _Excel_RangeRead($o_Workbook, Default, $s_ColumnLetter & '2:' & $s_ColumnLetter & $i_Rows)
Local $s_NowDate = Number(StringRegExpReplace(_NowCalcDate(), '\D', ''))
Local $as_Cells = 0

For $i = 0 To UBound($as_RangeRead) - 1
    If _DateStandardToCalcDate($as_RangeRead[$i]) < $s_NowDate Then
        $as_Cells = _Excel_RangeRead($o_Workbook, Default, 'C3:C5')
        _ArrayDisplay($as_Cells, 'Row: ' & $i + 2)
    EndIf
Next

_Excel_Close($o_Excel)

Func _DateStandardToCalcDate($sDate)
    If Not StringRegExp($sDate, "^(\d{1,2})/(\d{1,2})/(\d{4})$") Then Return SetError(1, 0, "")
    If @error Then Return SetError(1, 0, "")

    Local $sDateNew = StringRegExpReplace($sDate, "(\d{2})/(\d{2})/(\d{4})", "$3/$1/$2")
    $sDateNew = StringRegExpReplace($sDateNew, "(\d{2})/(\d)/(\d{4})", "$3/$1/0$2")
    $sDateNew = StringRegExpReplace($sDateNew, "(\d)/(\d{2})/(\d{4})", "$3/0$1/$2")
    $sDateNew = StringRegExpReplace($sDateNew, "(\d)/(\d)/(\d{4})", "$3/0$1/0$2")

    Return Number(StringRegExpReplace($sDateNew, '\D', ''))
EndFunc   ;==>_DateStandardToCalcDate

Edited May 14, 2017 by benners

DigDeep · May 14, 2017

23 minutes ago, benners said:

You can but the results will always be the same value no matter the row. Unless you mean columns A,B and C of the matching row

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

Local $o_Excel = _Excel_Open(False)
Local $o_Workbook = _Excel_BookOpen($o_Excel, @ScriptDir & "\Test.xls")
Local $i_Rows = $o_Workbook.ActiveSheet.UsedRange.Rows.Count
Local $s_ColumnLetter = _Excel_ColumnToLetter($o_Workbook.ActiveSheet.UsedRange.Columns.Count)
Local $as_RangeRead = _Excel_RangeRead($o_Workbook, Default, $s_ColumnLetter & '2:' & $s_ColumnLetter & $i_Rows)
Local $s_NowDate = Number(StringRegExpReplace(_NowCalcDate(), '\D', ''))
Local $as_Cells = 0

For $i = 0 To UBound($as_RangeRead) - 1
    If _DateStandardToCalcDate($as_RangeRead[$i]) < $s_NowDate Then
        $as_Cells = _Excel_RangeRead($o_Workbook, Default, 'C3:C5')
        _ArrayDisplay($as_Cells, 'Row: ' & $i + 2)
    EndIf
Next

_Excel_Close($o_Excel)

Func _DateStandardToCalcDate($sDate)
    If Not StringRegExp($sDate, "^(\d{1,2})/(\d{1,2})/(\d{4})$") Then Return SetError(1, 0, "")
    If @error Then Return SetError(1, 0, "")

    Local $sDateNew = StringRegExpReplace($sDate, "(\d{2})/(\d{2})/(\d{4})", "$3/$1/$2")
    $sDateNew = StringRegExpReplace($sDateNew, "(\d{2})/(\d)/(\d{4})", "$3/$1/0$2")
    $sDateNew = StringRegExpReplace($sDateNew, "(\d)/(\d{2})/(\d{4})", "$3/0$1/$2")
    $sDateNew = StringRegExpReplace($sDateNew, "(\d)/(\d)/(\d{4})", "$3/0$1/0$2")

    Return Number(StringRegExpReplace($sDateNew, '\D', ''))
EndFunc   ;==>_DateStandardToCalcDate

Yes, I specifically want only the data from Column C. My Rows might increase or decrease and I might just keep columns C and D. So the only aim is to get the result what is in C2 - end.

In this excel it will be:

Test 2

Test 3

Test 4

Test 5

Test 8

Test 10

benners · May 14, 2017

Return the text in a delimited string or array?

DigDeep · May 14, 2017

20 minutes ago, benners said:

Return the text in a delimited string or array?

Appology @benners I believe it should be delimited string. Either the result should come 1 by 1 like the msgbox displays each value after every clicks or all the values together as they appear in Array.

Edited May 14, 2017 by DigDeep

benners · May 14, 2017

Try this. You can edit it to give you what you want

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

Local $v_Return = Column_GetValues(@ScriptDir & '\Test.xls')

If Not IsArray($v_Return) Then
    MsgBox(0, 'C Cell', $v_Return)
Else
    _ArrayDisplay($v_Return, 'C Cell')
EndIf

; #FUNCTION# ====================================================================================================================
; Syntax ........: Column_GetValues($s_WorkBook[, $i_ReturnArray = 0])
; Parameters ....: $s_WorkBook    - The string path to the excel file
;                  $i_ReturnArray - Integer 0 or 1
; Return values .: Success - 0: Returns a delimited string and shows a message box for every match
;                          - 1: Returns an array
; ===============================================================================================================================
Func Column_GetValues($s_WorkBook, $i_ReturnArray = 0)
    Local $o_Excel = _Excel_Open(False)
    Local $o_Workbook = _Excel_BookOpen($o_Excel, $s_WorkBook)
    Local $s_ColumnLetter = _Excel_ColumnToLetter($o_Workbook.ActiveSheet.UsedRange.Columns.Count)
    Local $as_RangeRead = _Excel_RangeRead($o_Workbook, Default, $s_ColumnLetter & '2:' & $s_ColumnLetter & $o_Workbook.ActiveSheet.UsedRange.Rows.Count)
    Local $s_CellC = ''

    For $i = 0 To UBound($as_RangeRead) - 1
        If _DateStandardToCalcDate($as_RangeRead[$i]) < Number(StringRegExpReplace(_NowCalcDate(), '\D', '')) Then
            If Not $i_ReturnArray Then MsgBox(0, 'Row: ' & $i + 2 & ' Cell C', _Excel_RangeRead($o_Workbook, Default, 'C' & $i + 2))
            $s_CellC &= _Excel_RangeRead($o_Workbook, Default, 'C' & $i + 2) & '|'
        EndIf
    Next

    _Excel_Close($o_Excel)

    ; trim the trailing delimter
    $s_CellC = StringTrimRight($s_CellC, 1)

    If $i_ReturnArray Then Return StringSplit($s_CellC, '|')
    Return $s_CellC
EndFunc   ;==>Column_GetValues

Func _DateStandardToCalcDate($s_Date)
    If Not StringRegExp($s_Date, "^(\d{1,2})/(\d{1,2})/(\d{4})$") Then Return SetError(1, 0, "")
    If @error Then Return SetError(1, 0, "")

    Local $s_DateNew = StringRegExpReplace($s_Date, "(\d{2})/(\d{2})/(\d{4})", "$3/$1/$2")
    $s_DateNew = StringRegExpReplace($s_DateNew, "(\d{2})/(\d)/(\d{4})", "$3/$1/0$2")
    $s_DateNew = StringRegExpReplace($s_DateNew, "(\d)/(\d{2})/(\d{4})", "$3/0$1/$2")
    $s_DateNew = StringRegExpReplace($s_DateNew, "(\d)/(\d)/(\d{4})", "$3/0$1/0$2")

    Return Number(StringRegExpReplace($s_DateNew, '\D', ''))
EndFunc   ;==>_DateStandardToCalcDate

DigDeep · May 14, 2017

Thanks @benners. This solves.

benners · May 14, 2017

Happy to help. You may want to add some error checking to check for objects and arrays etc

DigDeep · May 14, 2017

Just now, benners said:

Happy to help. You may want to add some error checking to check for objects and arrays etc

Subz · May 14, 2017

You probably needed to use _NowCalc >= 0 as can't see how it could miss dates since it's comparing the entire column in the array. Also if you looked at the MsgBox, $aWorkbook[$i][x] each "x" represents the column number so you could have just used $aWorkbook[$i][3] to return the third column.

benners · May 17, 2017

Had another play and Frankenstiened Subz and my code together and shortened it. Delete as required to get the return values you want.

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

Local $v_Return = Column_GetValues(@ScriptDir & '\Test.xls')

If Not IsArray($v_Return) Then
    MsgBox(0, 'C Cell', $v_Return)
Else
    _ArrayDisplay($v_Return, 'C Cell')
EndIf

; #FUNCTION# ====================================================================================================================
; Syntax ........: Column_GetValues($s_WorkBook[, $i_ReturnArray = 0])
; Parameters ....: $s_WorkBook    - The string path to the excel file
;                  $i_ReturnArray - Integer 0 or 1
; Return values .: Success - 0: Returns a delimited string and shows a message box for every match
;                          - 1: Returns an array
; ===============================================================================================================================
Func Column_GetValues($s_WorkBook, $i_ReturnArray = 0)
    Local $o_Excel = _Excel_Open(False)
    Local $o_Workbook = _Excel_BookOpen($o_Excel, $s_WorkBook)
    Local $as_RangeRead = _Excel_RangeRead($o_Workbook)
    Local $s_CellC = ''
    Local $as_Date = 0

    For $i = 1 To UBound($as_RangeRead) - 1
        $as_Date = StringSplit($as_RangeRead[$i][UBound($as_RangeRead, $UBOUND_COLUMNS) - 1], '/')

        If  Number(StringFormat("%04i%02i%02i", $as_Date[3], $as_Date[1], $as_Date[2])) < Number(StringRegExpReplace(_NowCalcDate(), '\D', '')) then
            MsgBox(0,'Cell C', $as_RangeRead[$i][UBound($as_RangeRead, $UBOUND_COLUMNS) - 2])
            $s_CellC &= $as_RangeRead[$i][UBound($as_RangeRead, $UBOUND_COLUMNS) - 2] & '|'
        EndIf

    Next

    _Excel_Close($o_Excel)

    $s_CellC = StringTrimRight($s_CellC, 1)

    If $i_ReturnArray Then Return StringSplit($s_CellC, '|')
    Return $s_CellC
EndFunc   ;==>Column_GetValues

DigDeep · May 19, 2017

@benners sorry for getting back late for this. I tried with the new change you gave above, but it throws error for the line:

If Number(StringFormat("%04i%02i%02i", $as_Date[3], $as_Date[1], $as_Date[2])) < Number(StringRegExpReplace(_NowCalcDate(), '\D', '')) Then

Error: Array variable has incorrect number of subscripts or subscript dimension range exceeded.

I was trying out the previous example posted above with a new file but the issue occurs that it reads all the cells in Column A. Currently I have changed the Columns but I want to display only the row if the Date in Column B is less than today.

If Column B2 says 8/12/2017 (which is a future date), it should not read the A2. If B3 says today's date or past date, then it should display the B3.

Could you please help here? I have posted a new excel too.

Local $v_Return = Column_GetValues('C:\Temp\Test.xls')

If Not IsArray($v_Return) Then
    MsgBox(0, 'C Cell', $v_Return)
Else
    _ArrayDisplay($v_Return, 'C Cell')
EndIf

; #FUNCTION# ====================================================================================================================
; Syntax ........: Column_GetValues($s_WorkBook[, $i_ReturnArray = 0])
; Parameters ....: $s_WorkBook    - The string path to the excel file
;                  $i_ReturnArray - Integer 0 or 1
; Return values .: Success - 0: Returns a delimited string and shows a message box for every match
;                          - 1: Returns an array
; ===============================================================================================================================
Func Column_GetValues($s_WorkBook, $i_ReturnArray = 0)
    Local $o_Excel = _Excel_Open(False)
    Local $o_Workbook = _Excel_BookOpen($o_Excel, $s_WorkBook)
    Local $as_RangeRead = _Excel_RangeRead($o_Workbook)
    Local $s_CellC = ''
    Local $as_Date = 0

    For $i = 1 To UBound($as_RangeRead) - 1
        $as_Date = StringSplit($as_RangeRead[$i][UBound($as_RangeRead, $UBOUND_COLUMNS) - 1], '/')

        If Number(StringFormat("%04i%02i%02i", $as_Date[3], $as_Date[1], $as_Date[2])) < Number(StringRegExpReplace(_NowCalcDate(), '\D', '')) Then
            MsgBox(0, 'Cell C', $as_RangeRead[$i][UBound($as_RangeRead, $UBOUND_COLUMNS) - 2])
            $s_CellC &= $as_RangeRead[$i][UBound($as_RangeRead, $UBOUND_COLUMNS) - 2] & '|'
        EndIf

    Next

    _Excel_Close($o_Excel)

    $s_CellC = StringTrimRight($s_CellC, 1)

    If $i_ReturnArray Then Return StringSplit($s_CellC, '|')
    Return $s_CellC
EndFunc   ;==>Column_GetValues

Test.xls

Edited May 19, 2017 by DigDeep

benners · May 20, 2017

Yep, the code only worked based on that sheet. I have changed the code and added a few simple error checks. With your new sheet the date column is in the date format but with the old one it was in the General format so I have tried to allow for that.

Now you can specify the column with the date and the column who's value you want to return. See how you go but it's not infallible

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

Local $v_Return = Column_GetValues('D:\Downloads\Test.xls', 'B', 'A')

If Not IsArray($v_Return) Then
    MsgBox(0, 'Result', $v_Return)
Else
    _ArrayDisplay($v_Return, 'Cells')
EndIf

; #FUNCTION# ====================================================================================================================
; Syntax ........: Column_GetValues($s_WorkBook[, $i_ReturnArray = 0])
; Parameters ....: $s_WorkBook     - The string path to the excel file
;                  $s_DateColumn   - String, the Excel column containing the dates for comparison (a-z) not case sensitive
;                  $s_ReturnColumn - String, the Excel column containing the values to return (a-z) not case sensitive
;                  $i_ReturnArray  - Integer 0 or 1
; Return values .: Success - 0: Returns a delimited string and shows a message box for every match
;                          - 1: Returns an array
; ===============================================================================================================================
Func Column_GetValues($s_WorkBook, $s_DateColumn, $s_ReturnColumn, $i_ReturnArray = 0)
    Local $o_Excel = _Excel_Open(False)
    If @error Then Return SetError(@error, @extended, 'Unable to open an Excel instance')

    Local $o_Workbook = _Excel_BookOpen($o_Excel, $s_WorkBook)
    If @error Then Return SetError(@error, @extended, 'Unable to open the specified work book')

    Local $as_RangeRead = _Excel_RangeRead($o_Workbook)
    If @error Then Return SetError(@error, @extended, 'Unable to read the specified range')

    ; convert the column with the date to a number and - 1 to give the corresponding array column
    Local $i_DateColumn = _Excel_ColumnToNumber($s_DateColumn) - 1
    If @error Then Return SetError(@error, @extended, '$s_DateColumn is not a valid string')

    ; get a number for the column with the desired return value and -1 to give the array column
    Local $i_ReturnColumn = _Excel_ColumnToNumber($s_ReturnColumn) - 1
    If @error Then Return SetError(@error, @extended, '$i_ReturnColumn is not a valid string')

    ; check the array max values
    If $i_DateColumn > UBound($as_RangeRead, $UBOUND_COLUMNS - 1) Then Return SetError(1, 0, 'Column (' & $s_DateColumn & ') not found in ' & $s_WorkBook)
    If $i_ReturnColumn > UBound($as_RangeRead, $UBOUND_COLUMNS - 1) Then Return SetError(1, 0, 'Column (' & $s_ReturnColumn & ') not found in ' & $s_WorkBook)

    Local $s_Return = ''

    For $i = 1 To UBound($as_RangeRead) - 1
        If ReturnDateFormat($as_RangeRead[$i][$i_DateColumn]) <= Number(StringRegExpReplace(_NowCalcDate(), '\D', '') & '000000') Then
            MsgBox(0, 'Cell ' & $s_ReturnColumn, $as_RangeRead[$i][$i_ReturnColumn])
            $s_Return &= $as_RangeRead[$i][$i_ReturnColumn] & '|'
        EndIf

    Next

    _Excel_Close($o_Excel)

    $s_Return = StringTrimRight($s_Return, 1)

    If $i_ReturnArray Then Return StringSplit($s_Return, '|')
    Return $s_Return
EndFunc   ;==>Column_GetValues

Func ReturnDateFormat($v_Date)
    ; check if the date value has forward slashes i.e 20/5/2107
    ; as excel cells could be in General format
    If StringInStr($v_Date, '/') Then
        Local $as_Date = StringSplit($v_Date, '/')
        ; convert to number and desired format (YYYYMMDD) and pad with zeros to correct length
        $v_Date = StringFormat("%04i%02i%02i", $as_Date[3], $as_Date[1], $as_Date[2]) & '000000'
    EndIf

    ; the excel cells could be in date format and returns something like 20170520000000
    ; so convert date to number for comparison
    Return Number($v_Date)
EndFunc   ;==>ReturnDateFormat

Less than today's date

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members