Jump to content

_ExcelReadSheetToArray to _Excel_RangeRead


Go to solution Solved by water,

Recommended Posts

Hello,

I am having trouble upgrading AutoIT and utilizing the new Excel UDF.

Below is my old code which works great (slow, but correct), but _Excel_RangeRead is not working dynamically.

$oExcelM = _ExcelBookOpen($sFilePath_list_master)
    $aArrayM = _ExcelReadSheetToArray($oExcelM, 2)
    For $resultsM = 0 To UBound($aArrayM) - 1
        $chainM = $aArrayM[$resultsM][3]
        $storeM = $aArrayM[$resultsM][4]
        $queueM = $aArrayM[$resultsM][9]
        $descriptionM = $aArrayM[$resultsM][5]
        $categoryM = $aArrayM[$resultsM][6]
        If Not ($chainM == "") Then
            FileWrite($sFilePath_temp_master, $chainM & "-" & $storeM & @CRLF)
        EndIf
        If ($queueM == "US_L1_Call Center System Alerts") Then
            FileWrite($sFilePath_temp_master, $descriptionM & @CRLF)
        EndIf
        If ($categoryM = "Zero Coups") Then
            If ($chainM == "") Then
                FileWrite($sFilePath_temp_master_zerocoups, $descriptionM & @CRLF)
            ElseIf Not ($chainM == "") Then
                FileWrite($sFilePath_temp_master_zerocoups, $chainM & "-" & $storeM & @CRLF)
            EndIf
        EndIf
    Next
    _ExcelBookClose($oExcelM)

Here is the new code with _Excel_RangeRead, but the results of my array are "0"

Local $oApplM = _Excel_Open()
    Local $oExcelM = _Excel_BookOpen($oApplM, $sFilePath_list_master)
    $aArrayM = _Excel_RangeRead($oExcelM, $oExcelM.ActiveSheet.Usedrange.Columns("A:Z"), 1)
    For $resultsM = 0 To UBound($aArrayM) - 1
        $chainM = $aArrayM[$resultsM][3]
        $storeM = $aArrayM[$resultsM][4]
        $queueM = $aArrayM[$resultsM][9]
        $descriptionM = $aArrayM[$resultsM][5]
        $categoryM = $aArrayM[$resultsM][6]
        If Not ($chainM == "") Then
            FileWrite($sFilePath_temp_master, $chainM & "-" & $storeM & @CRLF)
        EndIf
        If ($queueM == "US_L1_Call Center System Alerts") Then
            FileWrite($sFilePath_temp_master, $descriptionM & @CRLF)
        EndIf
        If ($categoryM = "Zero Coups") Then
            If ($chainM == "") Then
                FileWrite($sFilePath_temp_master_zerocoups, $descriptionM & @CRLF)
            ElseIf Not ($chainM == "") Then
                FileWrite($sFilePath_temp_master_zerocoups, $chainM & "-" & $storeM & @CRLF)
            EndIf
        EndIf
    Next
    _Excel_BookClose($oExcelM)

How can I get my values from excel with the new UDF?

 

Link to post
Share on other sites

Parameter 2 is missing. Needs to be:

$aArrayM = _Excel_RangeRead($oExcelM, $oExcelM.ActiveSheet, $oExcelM.ActiveSheet.Usedrange.Columns("A:Z"), 1)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.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 (NEW 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 post
Share on other sites

Good call, I just noticed that too. I used Default instead, which should be fine for my 1 worksheet. However, the range A:Z is not working.

With only 1 column at a time I suppose I could get rid of the translation variables and just make

$chainM = _Excel_RangeRead($oExcelM, $oExcelM.ActiveSheet, $oExcelM.ActiveSheet.Usedrange.Columns("C:C"), 1)   

(instead of $chainM = $aArrayM[$resultsM][3])

... this may work.

Unless anyone knows how to get all cells through A:Z working i'm going to change my entire program for the new structure; it just does not seem very dynamic.

Link to post
Share on other sites

maybe usedrange is the issue, what about just

$oExcelM.Activesheet.Columns("a:z")

 

,-. .--. ________ .-. .-. ,---. ,-. .-. .-. .-.
|(| / /\ \ |\ /| |__ __||| | | || .-' | |/ / \ \_/ )/
(_) / /__\ \ |(\ / | )| | | `-' | | `-. | | / __ \ (_)
| | | __ | (_)\/ | (_) | | .-. | | .-' | | \ |__| ) (
| | | | |)| | \ / | | | | | |)| | `--. | |) \ | |
`-' |_| (_) | |\/| | `-' /( (_)/( __.' |((_)-' /(_|
'-' '-' (__) (__) (_) (__)

Link to post
Share on other sites

What is the value of @error and @extended after _Excel_RangeRead?

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.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 (NEW 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 post
Share on other sites

When I use A:Z the errors are @5 and extended@-2147352571

I tried without Usedrange but that had the same error.

I'm changing up all my code structure but getting rid of the For loop that cycled through the complete array with the old UDF means I need a For loop for each column now?

 

 

EDIT: errors are @5 and extended@-2147352571

Edited by DrewSS
Link to post
Share on other sites
  • Solution

Did you have a look at the the help file?

@error = 5 means: Error occurred when reading data using the transpose method. @extended is set to the COM error code

Could you try:

$aArrayM = _Excel_RangeRead($oExcelM, $oExcelM.ActiveSheet, $oExcelM.ActiveSheet.Usedrange.Columns("A:Z"), 1, True)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.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 (NEW 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 post
Share on other sites

The transpose method can not handle cells with more than 255 characters. That might have caused the problem.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.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 (NEW 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 post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...