Jump to content
iamtheky

_Excel_RangeRead feature request

Recommended Posts

iamtheky

I would like to be able to dictate the return, rather than have to accommodate all possibilities every time I run this against a spreadsheet of unknown contents.  Here is my effort to normalize all data to a 2D array, in what is surely a non-optimal fashion.  Any other effective workarounds are welcome.  I feel like I have asked this for a previous project, but I can not find that thread.

 

#include <Excel.au3>

$xlName = FileOpenDialog("Select Excel File" , @ScriptDir , "Excel(*.xls;*.xlsx)" , 1)

$oExcel = _Excel_Open(False)
$oWorkBook = _Excel_BookOpen ($oExcel, $xlName)
$XLResult = _Excel_RangeRead($oWorkBook)
_Excel_BookClose($oWorkBook)
_Excel_Close($oExcel)

_ArrayDisplay(_2DResult($XLResult))


Func _2DResult($Result)

If IsString($Result) = 1 Then
    local $aTemp[1][2]
    $aTemp[0][0] = $Result
ElseIf Ubound($Result , 2) = 0 Then
    local $aTemp[ubound($result)][2]
    for $i = 0 to ubound($result) - 1
        $aTemp[$i][0] = $Result[$i]
    next
Else
    $aTemp = $Result
EndIf

return $aTemp

EndFunc

 

 

Edited by boththose

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

Share this post


Link to post
Share on other sites
water

I remember this discussion and IIRC the _2DResult function was the result of this discussion.

The format of the returned data is defined by the Excel Transpose method.
So the enhanced _Excel_RangeRead function would (under the covers) do the same and translate the result.
 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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

I would enhance the function for less data movement:
 

Func _2DResult(ByRef $vResult)

    If IsString($vResult) = 1 Then
        Local $aTemp[1][2] = [[$vResult]]
        Return $aTemp
    ElseIf Ubound($vResult, 0) = 1 Then
        Local $aTemp[Ubound($vResult, 1)][1]
        For $i = 0 to Ubound($vResult) - 1
            $aTemp[$i][0] = $vResult[$i]
        Next
        Return $aTemp
    Else 
        Return $Result
    EndIf

EndFunc

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
iamtheky

and another feature request I may have made in the past:

If the cell occupied is not A1, return all cells A1 - the single item?  Or throw the cell it came from in @extended?


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

Share this post


Link to post
Share on other sites
water

I'm sorry, I'm not 100% sure I understand what you mean.
Could you please give an example?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
iamtheky

Sure, and the problem exists for returned arrays as well, when $aArray[0] (or $aArray[0][0]) does not coincide with A1.   This script will populate Column B IP addresses for all the names in column A.  However, if the first item found exists in any row below 1, I need to know which row the data started on, so I can start my rangewrite in that same location.

So create a spreadsheet, and put one name in A13, the result it is that it writes that name to A1 and the corresponding IP to B1 (and leaves that name in A13), because there is no way to determine that the item found was at A13.

#include <Constants.au3>
#include <Excel.au3>

$xlName = FileOpenDialog("Select Excel File" , @ScriptDir , "Excel(*.xls;*.xlsx)" , 1)

$oExcel = _Excel_Open(False)
$oWorkBook = _Excel_BookOpen ($oExcel, $xlName)
$XLResult = _Excel_RangeRead($oWorkBook)

$aResult = _2DResult($XLResult)

For $i = 0 to ubound($aResult) - 1
    tooltip($aResult[$i][0] , 0 , 0)
    If $aResult[$i][1] = "" Then $aResult[$i][1] = NsKnowsIp($aResult[$i][0])
Next

_Excel_RangeWrite($oWorkBook , Default , $aResult)
_Excel_BookClose($oWorkBook)
_Excel_Close($oExcel)


Func NsKnowsIp($string)

    $Timer = TimerInit()
    $foo = Run("nslookup "& $string, @SystemDir, @SW_HIDE, $STDERR_CHILD + $STDOUT_CHILD)
    $output = ""
    $sOut = ""

    While 1
        $output &= StdoutRead($foo)
        If @error Then ExitLoop
        If TimerDiff($Timer) > 5000 Then
            run("taskkill /F /PID " & $foo, @SystemDir, @SW_HIDE)
            ExitLoop
        EndIf
    Wend

$aOut = stringsplit($output, @LF , 2)
If @Error > 0 Then Return $sOut

If ubound($aOut) > 4 Then $sOut = stringtrimleft($aOut[4] , 8)

run("taskkill /F /PID " & $foo, @SystemDir, @SW_HIDE)

   return $sOut

endfunc

Func _2DResult(ByRef $vResult)

    If IsString($vResult) = 1 Then
        Local $aTemp[1][2] = [[$vResult]]
        Return $aTemp
    ElseIf Ubound($vResult, 0) = 1 Then
        Local $aTemp[Ubound($vResult, 1)][2]
        For $i = 0 to Ubound($vResult) - 1
            $aTemp[$i][0] = $vResult[$i]
        Next
        Return $aTemp
    Else
        Return $vResult
    EndIf

EndFunc

 

Edited by boththose

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

Share this post


Link to post
Share on other sites
water

I see.
When you do not use the range parameter to specify the range to be read the UsedRange is being returned.
When the usedrange does not start with A1 then you get an "offset".
In the wiki (https://www.autoitscript.com/wiki/Excel_Range) I describe how to return the UsedRange plus all cells to the left and top.

$oWorkbook.ActiveSheet.UsedRange.Cells(1, 1).Address

returns the address of the upper left corner of a range.
Does this help?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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

I see.
When you do not use the range parameter to specify the range to be read the UsedRange is being returned.
When the usedrange does not start with A1 then you get an "offset".
In the wiki (https://www.autoitscript.com/wiki/Excel_Range) I describe how to return the UsedRange plus all cells to the left and top.

$oWorkbook.ActiveSheet.UsedRange.Cells(1, 1).Address

returns the address of the upper left corner of a range.
Does this help?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
iamtheky

That does it, thank you sir:

$xlName = FileOpenDialog("Select Excel File" , @ScriptDir , "Excel(*.xls;*.xlsx)" , 1)

$oExcel = _Excel_Open(False)
$oWorkBook = _Excel_BookOpen ($oExcel, $xlName)
$XLResult = _Excel_RangeRead($oWorkBook)

$sRange = stringreplace($oWorkbook.ActiveSheet.UsedRange.Cells(1, 1).Address , "$" , "")

$aResult = _2DResult($XLResult)

For $i = 0 to ubound($aResult) - 1
    tooltip($aResult[$i][0] , 0 , 0)
    If $aResult[$i][1] = "" Then $aResult[$i][1] = NsKnowsIp($aResult[$i][0])
Next

_Excel_RangeWrite($oWorkBook , Default , $aResult , $sRange)
_Excel_BookClose($oWorkBook)
_Excel_Close($oExcel)
exit

 


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

Share this post


Link to post
Share on other sites
water

I just added this "trick" to the mentioned wiki article.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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

Another trick:

To convert formulas/cell references from R1C1 to A1 or vice versa or from absolute (e.g. $B$2) to relative (e.g. "B2") you could use:

_Excel_ConvertFormula($oAppl, "$B$2", $xlA1, $xlA1, $xlRelative)

Returns: B2.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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

Double post.

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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

×