DigDeep Posted October 29, 2017 Share Posted October 29, 2017 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 popupLocal $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 Link to comment Share on other sites More sharing options...
DigDeep Posted October 31, 2017 Author Share Posted October 31, 2017 Has anyone tried so far? Link to comment Share on other sites More sharing options...
benners Posted November 13, 2017 Share Posted November 13, 2017 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 Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now