Excel UDF - Help & Support

[cag8f]

Yay!

[cag8f]

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

[water]

Thanks. Has been fixed.

[KLM]

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 ?

[reb]

KLM

REB

[Geir1983]

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

[KLM]

Thanks reb.

Problem Solved.

[Geir1983]

 

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

[water]

Why change the active sheet?

Replace "ActiveSheet" with the object of the sheet you wish to process as returned by function _Excel_SheetList.

[water]

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.

[Geir1983]

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

[water]

You already have variables inserted her:

$oWorkbook.$aSheets[$SheetIdx][0].Usedrange.Columns("D:D")

So where is the problem?

[Geir1983]

It gives "error: syntax error" on that line when i tried to insert a variable instead of ActiveSheet

[water]

Can you please post the exact line you use?

[Geir1983]

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)

[water]

Try this:

Global $aResult = _Excel_RangeRead($oWorkbook, Default, $aSheets[$SheetIdx][1].Usedrange.Columns("D:D"))

[Geir1983]

Perfect, thanks

[water]

[illostos]

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

[water]

Which version of AutoIt do you run?

Which version of Excel do you use?