cag8f Posted April 7, 2014 Share Posted April 7, 2014 Yay! Link to comment Share on other sites More sharing options...
cag8f Posted April 7, 2014 Share Posted April 7, 2014 Type on _Excel_ColumnToLetter.htm: Converts the column number to letter(s). #Include <Excel.au3> _ExcelColumnToLetter($iColumn) I believe last line should read "_Excel_ColumnToLetter($iColumn)" Link to comment Share on other sites More sharing options...
water Posted April 7, 2014 Author Share Posted April 7, 2014 Thanks. Has been fixed. My UDFs and Tutorials: Spoiler UDFs: Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki Link to comment Share on other sites More sharing options...
KLM Posted April 29, 2014 Share Posted April 29, 2014 Hello Everyone, How to change or apply background color for a cell(s) in a entire row or column ? I tried font.background, which didn't worked. Can anyone share an example for changing background color ? K L M ------------------ Real Fakenamovich ------------------ K L M ------------------ Real Fakenamovich ------------------ Link to comment Share on other sites More sharing options...
reb Posted April 29, 2014 Share Posted April 29, 2014 KLM REB MEASURE TWICE - CUT ONCE Link to comment Share on other sites More sharing options...
Geir1983 Posted April 30, 2014 Share Posted April 30, 2014 I am having some problems reading a range in another sheet. I want to read from E5 to E(as many rows that contain data). I get it working by the following code, but it returns 65535(?) elements in the array where there is only 5400~. Any hints? Global $aSheets = _Excel_SheetList($oWorkbook) For $SheetIdx = 0 to Ubound($aSheets)-2 Global $aResult = _Excel_RangeRead($oWorkbook, $aSheets[$SheetIdx][0], "D:D") For $RowIdx = 5 to UBound($aResult)-1 Link to comment Share on other sites More sharing options...
KLM Posted April 30, 2014 Share Posted April 30, 2014 Thanks reb. Problem Solved. K L M ------------------ Real Fakenamovich ------------------ K L M ------------------ Real Fakenamovich ------------------ Link to comment Share on other sites More sharing options...
Geir1983 Posted May 2, 2014 Share Posted May 2, 2014 I am having some problems reading a range in another sheet. I want to read from E5 to E(as many rows that contain data). I get it working by the following code, but it returns 65535(?) elements in the array where there is only 5400~. Any hints? Global $aSheets = _Excel_SheetList($oWorkbook) For $SheetIdx = 0 to Ubound($aSheets)-2 Global $aResult = _Excel_RangeRead($oWorkbook, $aSheets[$SheetIdx][0], "D:D") For $RowIdx = 5 to UBound($aResult)-1 How to change current active sheet? Then i can use the .ActiveSheet.Usedrange.Columns method. Ive tried lots of variants now, but no luck.. Global $aSheets = _Excel_SheetList($oWorkbook) For $SheetIdx = 0 to Ubound($aSheets)-2 $oWorkbook.WorkBooks($aSheets[$SheetIdx][0]).Activate() Global $aResult = _Excel_RangeRead($oWorkbook, default, $oWorkbook.ActiveSheet.Usedrange.Columns("D:D")) Link to comment Share on other sites More sharing options...
water Posted May 4, 2014 Author Share Posted May 4, 2014 Why change the active sheet? Replace "ActiveSheet" with the object of the sheet you wish to process as returned by function _Excel_SheetList. My UDFs and Tutorials: Spoiler UDFs: Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki Link to comment Share on other sites More sharing options...
water Posted May 4, 2014 Author Share Posted May 4, 2014 Hello Everyone, How to change or apply background color for a cell(s) in a entire row or column ? I tried font.background, which didn't worked. Can anyone share an example for changing background color ? I will add some examples to the wiki page I 'm currently putting together. My UDFs and Tutorials: Spoiler UDFs: Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki Link to comment Share on other sites More sharing options...
Geir1983 Posted May 5, 2014 Share Posted May 5, 2014 Why change the active sheet? Replace "ActiveSheet" with the object of the sheet you wish to process as returned by function _Excel_SheetList. Care to elaborate, how to insert a variable to this? Global $aSheets = _Excel_SheetList($oWorkbook) For $SheetIdx = 0 to Ubound($aSheets)-2 Global $aResult = _Excel_RangeRead($oWorkbook, default, $oWorkbook.$aSheets[$SheetIdx][0].Usedrange.Columns("D:D")) Link to comment Share on other sites More sharing options...
water Posted May 5, 2014 Author Share Posted May 5, 2014 You already have variables inserted her: $oWorkbook.$aSheets[$SheetIdx][0].Usedrange.Columns("D:D") So where is the problem? My UDFs and Tutorials: Spoiler UDFs: Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki Link to comment Share on other sites More sharing options...
Geir1983 Posted May 5, 2014 Share Posted May 5, 2014 It gives "error: syntax error" on that line when i tried to insert a variable instead of ActiveSheet Link to comment Share on other sites More sharing options...
water Posted May 5, 2014 Author Share Posted May 5, 2014 Can you please post the exact line you use? My UDFs and Tutorials: Spoiler UDFs: Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki Link to comment Share on other sites More sharing options...
Geir1983 Posted May 5, 2014 Share Posted May 5, 2014 This is what i tried ( I do ofc open an excel workbook with several pages before this): Global $aSheets = _Excel_SheetList($oWorkbook) ;_ArrayDisplay($aSheets) For $SheetIdx = 0 to Ubound($aSheets)-2 Global $aResult = _Excel_RangeRead($oWorkbook, default, $oWorkbook.$aSheets[$SheetIdx][0].Usedrange.Columns("D:D")) _ArrayDisplay($aResult) Link to comment Share on other sites More sharing options...
water Posted May 5, 2014 Author Share Posted May 5, 2014 Try this: Global $aResult = _Excel_RangeRead($oWorkbook, Default, $aSheets[$SheetIdx][1].Usedrange.Columns("D:D")) My UDFs and Tutorials: Spoiler UDFs: Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki Link to comment Share on other sites More sharing options...
Geir1983 Posted May 5, 2014 Share Posted May 5, 2014 Perfect, thanks Link to comment Share on other sites More sharing options...
water Posted May 5, 2014 Author Share Posted May 5, 2014 My UDFs and Tutorials: Spoiler UDFs: Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki Link to comment Share on other sites More sharing options...
illostos Posted May 16, 2014 Share Posted May 16, 2014 (edited) Hi, i have an Excel File with 1993 rows and 49 columns. The total count of entries is 100.000. When i kill most of the data so just 2000 rows and 2 colums are left and use $aArray2 = _Excel_RangeRead($oExcel_Matrix,Default,Default,1,0) it works and i become the array displayed. But when i use the complete array i become this Error: C:Program Files (x86)AutoIt3IncludeExcel.au3 (1232) : ==> The requested action with this object has failed.: $vResult = $oExcel.Transpose($vRange.Value) $vResult = $oExcel.Transpose($vRange.Value)^ ERROR When i change the $bForceFunc into 1 $aArray2 = _Excel_RangeRead($oExcel_Matrix,Default,Default,1,1) i become this Error: C:Program Files (x86)AutoIt3IncludeExcel.au3 (1228) : ==> Unknown function name.: If $iCellCount > 1 Then _ArrayTranspose($vResult) If $iCellCount > 1 Then ^ ERROR What should i do, so the file can be read correctly? Thanks PS: The entire Script looks like this: #include <newExcel.au3> #include <Array.au3> Global $oAppl = _Excel_Open(0) $oExcel_Matrix = _Excel_BookOpen($oAppl, "I:\Matrix\Matrix.xlsx") $aArray2 = _Excel_RangeRead($oExcel_Matrix,default,default,1,1) _ArrayDisplay($aArray2) if (IsDeclared ("oExcel_Matrix")) Then _Excel_Close($oAppl) EndIf Exit Edited May 16, 2014 by illostos Link to comment Share on other sites More sharing options...
water Posted May 16, 2014 Author Share Posted May 16, 2014 Which version of AutoIt do you run? Which version of Excel do you use? My UDFs and Tutorials: Spoiler UDFs: Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki Link to comment Share on other sites More sharing options...
Recommended Posts