Sign in to follow this  
Followers 0
DrewSS

_ExcelReadSheetToArray to _Excel_RangeRead

9 posts in this topic

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?

 

Share this post


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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


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.

Share this post


Link to post
Share on other sites

maybe usedrange is the issue, what about just

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

 


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

Share this post


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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

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

Share this post


Link to post
Share on other sites

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)
1 person likes this

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

water!!! Your the best! Thank you!

Share this post


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.

1 person likes this

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


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
Sign in to follow this  
Followers 0