Jump to content

Excel UDF - Help & Support


 Share

Recommended Posts

  • Replies 54
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

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

  • 3 weeks later...

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

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

 

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

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

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

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

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

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

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

:)

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

  • 2 weeks later...

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 by illostos
Link to comment
Share on other sites

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

Guest
This topic is now closed to further replies.
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...