andomatic Posted August 29, 2013 Share Posted August 29, 2013 Hi, I'm very new to excel manipulation and have a sheet I need to process some data from. The sheet has 29 columns. I need to read by row the information in, for example, columns 2,3,6,9,13,19. I am looking at the _ExcelReadArray and _ExcelReadSheetToArray but neither seems to offer a way to read only certain columns. or read in all the columns and then get an index? I'm feeling like it is right in front of me but for the life of me I'm stumped. Any direction is greatly appreicated Thanks Andy Link to comment Share on other sites More sharing options...
Moderators JLogan3o13 Posted August 29, 2013 Moderators Share Posted August 29, 2013 (edited) Hi, andomatic. If you read the entire sheet in and do an _ArrayDisplay you should be able to see how it is sorted, and read only from the columns you would like. One alternative would be to read the sheet in, and then delete the columns you do not want. Something like this: Edit: Full credit to PsaltyDS for this _ArrayDeleteCol($aArray, 0) Func _ArrayDeleteCol(ByRef $aArray, $iCol) If Not IsArray($aArray) Then Return SetError(1, 0, 0); Not an array If UBound($aArray, 0) <> 2 Then Return SetError(1, 1, 0); Not a 2D array If ($iCol < 0) Or ($iCol > (UBound($aArray, 2) - 1)) Then Return SetError(1, 2, 0); $iCol out of range If $iCol < UBound($aArray, 2) - 1 Then For $c = $iCol To UBound($aArray, 2) - 2 For $r = 0 To UBound($aArray) - 1 $aArray[$r][$c] = $aArray[$r][$c + 1] Next Next EndIf ReDim $aArray[UBound($aArray)][UBound($aArray, 2) - 1] Return 1 EndFunc Edited August 29, 2013 by JLogan3o13 "Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball How to get your question answered on this forum! Link to comment Share on other sites More sharing options...
water Posted August 29, 2013 Share Posted August 29, 2013 If speed is an issue you could try the beta version of my rewrite of the Excel UDF. Function _Excel_RangeRead is much faster (20-50 times). My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
kylomas Posted August 29, 2013 Share Posted August 29, 2013 (edited) andomatic, This might get you started (same result as JLogan's but skipping the delete part - populate the array directly) kylomas expandcollapse popup#include <Constants.au3> #include <Excel.au3> #include <Array.au3> ;--------------------------------------------------------------------------------------------------- ; create an Excel test spreadsheet ;--------------------------------------------------------------------------------------------------- local $oExcel = _ExcelBookNew() if @error = 1 then msgbox($mb_ok,'Excel Error','Excel Object does not exist' & @lf & 'Object = ' & $oExcel) Exit endif for $1 = 1 to 10 for $2 = 1 to 29 _excelwritecell($oExcel,stringformat('[ %02i - %02i ]',$1,$2),$1,$2) Next Next ;--------------------------------------------------------------------------------------------------- ; create a 2D array comprised of columns 2,3,6,9,13,19 ;--------------------------------------------------------------------------------------------------- local $aResult[11][6] for $1 = 1 to 10 $aResult[$1][0] = _ExcelReadCell($oExcel, $1,2) $aResult[$1][1] = _ExcelReadCell($oExcel, $1,3) $aResult[$1][2] = _ExcelReadCell($oExcel, $1,6) $aResult[$1][3] = _ExcelReadCell($oExcel, $1,9) $aResult[$1][4] = _ExcelReadCell($oExcel, $1,13) $aResult[$1][5] = _ExcelReadCell($oExcel, $1,19) Next _arraydisplay($aResult) _ExcelBookClose($oExcel) edit: crap! I keep forgetting to close the workbook...code amended Edited August 29, 2013 by kylomas Forum Rules Procedure for posting code "I like pigs. Dogs look up to us. Cats look down on us. Pigs treat us as equals." - Sir Winston Churchill Link to comment Share on other sites More sharing options...
Moderators JLogan3o13 Posted August 29, 2013 Moderators Share Posted August 29, 2013 (edited) Very nice, kylomas Edited August 29, 2013 by JLogan3o13 "Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball How to get your question answered on this forum! Link to comment Share on other sites More sharing options...
kylomas Posted August 29, 2013 Share Posted August 29, 2013 @JL - Thanks Forum Rules Procedure for posting code "I like pigs. Dogs look up to us. Cats look down on us. Pigs treat us as equals." - Sir Winston Churchill 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