Jump to content
DigDeep

Get Excel Columns

Recommended Posts

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

 

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

 

Share this post


Link to post
Share on other sites
DigDeep

Has anyone tried so far?

Share this post


Link to post
Share on other sites
benners

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

#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

Share this post


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

×