Get Excel Columns

[DigDeep]

Sometime back @benners had helped me to get the Excel Columns filtered based on 'Less than today's date'.

It's working good when there are just 2 columns available. but if I am trying to keep 3 or 4 columns, I can only get the results of the last 2 columns from the right.

I have checked that if I have 3 columns then changing the below line as '- 2' instead of '- 1' gives me the result as Column A. But I need both Column A and B as the end result if there are 3 columns.

    ; 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')

 

expandcollapse popup

Local $v_Return = Column_GetValues('C:\Temp\Test.xls', 'C', '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] & '|'
            MsgBox(0, '', $s_Return)
        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

 

[DigDeep]

Has anyone tried so far?

[benners]

Try this. Removed the message box returns, now only returns arrays. 

expandcollapse popup

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

Local $av_Return = Column_GetValues(@scriptdir & '\Test.xls', 'd', 'a,b,c')

If IsArray($av_Return) Then
    _ArrayDisplay($av_Return, 'Cells')
Else
    MsgBox(0, 'Error', $av_Return)
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_ReturnColumns - Comma delimitied string, the Excel column(s) values to return (a-z) not case sensitive
; Return values .: Success - Returns an array
; ===============================================================================================================================
Func Column_GetValues($s_WorkBook, $s_DateColumn, $s_ReturnColumns)
    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, _Excel_Close($o_Excel), 'Unable to open the specified work book')

    Local $as_RangeRead = _Excel_RangeRead($o_Workbook)
    If @error Then Return SetError(@error, _Excel_Close($o_Excel), '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, _Excel_Close($o_Excel), '$s_DateColumn is not a valid string')

    ; split the wanted columns string into an array for looping later
    Local $as_Columns = StringSplit($s_ReturnColumns, ',')

    ; check for the date column
    If $i_DateColumn > UBound($as_RangeRead, $UBOUND_COLUMNS) - 1 Then Return SetError(1, _Excel_Close($o_Excel), 'Column (' & $s_DateColumn & ') not found in ' & $s_WorkBook)

    Local $as_Return[UBound($as_RangeRead, $UBOUND_COLUMNS - 1)][$as_Columns[0]] ; array for column values
    Local $i_Column = 0, $i_Row = 0

    ; loop through the spreadsheet array
    For $i = 1 To UBound($as_RangeRead) - 1
        If ReturnDateFormat($as_RangeRead[$i][$i_DateColumn]) <= Number(StringRegExpReplace(_NowCalcDate(), '\D', '') & '000000') Then

            For $j = 1 To $as_Columns[0] ; loop through the return columns array
                $i_Column = _Excel_ColumnToNumber($as_Columns[$j]) - 1 ; convert the column letter to a number for the array
                if @error then ContinueLoop ; simple error check for invalid column string

                ; check for nonexisting columns
                If $i_Column > UBound($as_RangeRead, $UBOUND_COLUMNS) - 1 Then ; add default text to array
                    $as_Return[$i_Row][$j - 1] = 'N/A'
                Else ; add cell to the return array
                    $as_Return[$i_Row][$j - 1] = $as_RangeRead[$i][$i_Column]
                EndIf
            Next

            $i_Row += 1 ; increase the counter for redim later
        EndIf

    Next

    _Excel_Close($o_Excel) ; close the excel instance
    ReDim $as_Return[$i_Row][$as_Columns[0]] ; only needed if you dont want empty rows displayed

    Return $as_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

 

Test.xls