SkysLastChance

_ExcelReadSheetToArray into _Excel_RangeRead

9 posts in this topic

I am a bit confused on how to convert this old code into the new excel code.

 

$aArray= _ExcelReadSheetToArray($oExcel,2,1,$number,3,True)

With this I could select the start row, start column, the row count, and the column count am I unable to do this anymore or should I be using something other than _Excel_RangeRead

 

 


Sky

Share this post


Link to post
Share on other sites



_Excel_RangeRead is fine. But you have to use A1 notation. Means: B1:x1. The Excel UDF provides a function to translate a number to a column letter (the x above). 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2016-08-18 - Version 1.4.6.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2016-12-04 - Version 1.2.2.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
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

#include <Array.Au3>
#include <Excel.au3>

$number = inputbox( "Temp Account", "How many Accounts")

$aArray= _ExcelReadSheetToArray($oExcel,2,1,$number,3,True) ; $iStartRow = 1, $iStartColumn = 1, $iRowCnt = 10, $iColCnt = 3, $iColShift = True as excel
$rows = 1
Opt("WinTitleMatchMode", 1)
Local $loop = 0
Run("notepad.exe")

Do
For $i =  1 to $aArray[0][0]  ; if you change 1 it will start reading from that.
$sR1 = $aArray[$i][0] ; Entry Number
$sR2 = $aArray[$i][1] ; Weight
$sR3 = $aArray[$i][2] ; Value

WinWaitActive("Untitled - Notepad")
send($sR1)
send("{ENTER}")
send($sR2)
send("{ENTER}")
send($sR3)
send("{ENTER}")
Next

    $loop = $loop + 1
Until $loop = $rows

This is the code I am trying to rewrite. I just feel like I am not heading in the wrong direction.

Does any body know any good Youtube Vidoes or Turtorials on _Excel_RangeRead and Arrays other than the helpfile?

I need to start from the ground up I believe.

Edited by SkysLastChance
Typo

Sky

Share this post


Link to post
Share on other sites

I had to write your code from scratch and make a couple of assumptions as what you have posted is not runnable (always helps to post runnable code when requesting assistance). But this works for me:

#include <Excel.au3>

Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel, @DesktopDir & "\Test.xls")
Local $aArray = _Excel_RangeRead($oWorkbook, Default, Default)

    For $i = 1 To UBound($aArray) - 1
        $sR1 = $aArray[$i][0] ; Entry Number
        $sR2 = $aArray[$i][1] ; Weight
        $sR3 = $aArray[$i][2] ; Value
        ControlSend("[CLASS:Notepad]", "", "Edit1", $sR1 & @CR)
        ControlSend("[CLASS:Notepad]", "", "Edit1", $sR2 & @CR)
        ControlSend("[CLASS:Notepad]", "", "Edit1", $sR3 & @CR)
    Next

 

1 person likes this

When you're dead, you don't know you're dead - it's only difficult for those that know you. It's the same way when you're stupid...

My Scripts: SCCM UDFInclude Source with Compiled Script, Windows Firewall UDF

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

#include <Excel.au3>
#include <Array.Au3>


;-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
;Functions
;-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
;Live ()
Excel ()

;-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------




;-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
;Opens Live and Gets to the Edit Mangage Users Screen
;-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Func Live ()
ShellExecute ("C:\Program Files\MEDITECH\SSDWEB.Universe\SSDWEB.LIVEF.Ring\Client.mtad")
WinWaitActive ("MEDITECH Signon")
Send ("{F12}")
Sleep ("1000")
Send ("{Enter}{Down}{Enter 3}")
WinWaitActive ("Enter/Edit Users")
WinMove("Enter/Edit Users", "", 0, 0)
WinWaitActive ("Enter/Edit Users")
Send ("{F8 2}")
Send ("E")
EndFunc

;-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

;-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
;Let's you choice a excel or make a new one
;-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Func Excel ()

While ProcessExists("EXCEL.EXE")
$ms = MsgBox(5,"","Process error.  You have an Excel sheet open.  You must close it in order to let this program work.  Please close it now.")
    If $ms=2 Then Exit
Sleep(250)
WEnd
   $sExcelFile = FileOpenDialog("Choose/Create Excel File", @ScriptDir, "(*.xlsx)")

If FileExists($sExcelFile) Then
   $oExcel = _Excel_Open ()
   $oExcel = _Excel_BookOpen($oExcel,$sExcelFile) ;this will open the chosen xls file.

Else
   $oExcel = _Excel_Open()
   $oWorkbook = _Excel_BookNew($oExcel, 2);this is here to create the xls file if it does not exist.
EndIf
EndFunc

;-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Local $aArray = _Excel_RangeRead($oWorkbook, Default, Default)

    For $i = 1 To UBound($aArray) - 1
        $sR1 = $aArray[$i][0] ; Entry Number
        $sR2 = $aArray[$i][1] ; Weight
        $sR3 = $aArray[$i][2] ; Value
        ControlSend("[CLASS:Notepad]", "", "Edit1", $sR1 & @CR)
        ControlSend("[CLASS:Notepad]", "", "Edit1", $sR2 & @CR)
        ControlSend("[CLASS:Notepad]", "", "Edit1", $sR3 & @CR)
    Next

Thank you JLogan3o13 You dident have to do that. I really appreicate it this gives me a better idea on how it works. 

Here is the code I am working with I stopped the live function.

I am going to have the code enter in items from the excel to meditech

I am just piecing my old code back together.

Temp Accounts.xlsx

Edited by SkysLastChance

Sky

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

>"C:\Users\vmitchell\Desktop\A

#include <Excel.au3>
#include <Array.Au3>

;-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
;Functions
;-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Live ()
Excel ()
;-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------




;-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
;Opens Live and Gets to the Edit Mangage Users Screen
;-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Func Live ()
ShellExecute ("C:\Program Files\MEDITECH\SSDWEB.Universe\SSDWEB.LIVEF.Ring\Client.mtad")
WinWaitActive ("MEDITECH Signon")
Send ("{F12}")
Sleep ("1000")
Send ("{Enter}{Down}{Enter 3}")
WinWaitActive ("Enter/Edit Users")
WinMove("Enter/Edit Users", "", 0, 0)
WinWaitActive ("Enter/Edit Users")
Send ("{F8 2}")
Send ("E")
EndFunc

;-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

;-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
;Let's you choice a excel or make a new one
;-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Func Excel ()

While ProcessExists("EXCEL.EXE")
$ms = MsgBox(5,"","Process error.  You have an Excel sheet open.  You must close it in order to let this program work.  Please close it now.")
    If $ms=2 Then Exit
Sleep(250)
WEnd
   $sExcelFile = FileOpenDialog("Choose/Create Excel File", @ScriptDir, "(*.xlsx)")

If FileExists($sExcelFile) Then
   $oExcel = _Excel_Open ()
   $oExcel = _Excel_BookOpen($oExcel,$sExcelFile) ;this will open the chosen xls file.

Else
   $oExcel = _Excel_Open()
   $oWorkbook = _Excel_BookNew($oExcel, 2);this is here to create the xls file if it does not exist.
EndIf


;-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


Local $aArray = _Excel_RangeRead($oExcel, Default, Default)

    For $i = 0 To UBound($aArray) - 1      ;$i =0 Start from row A1
        $sR1 = $aArray[$i][0]              ; Account Number
        WinActivate ("Enter/Edit Users")
        ControlSend("[Title:Enter/Edit Users]", "", "", $sR1 & @CR)

    Next

AutoIt\SciTe\..\autoit3.exe" /ErrorStdOut "C:\Users\vmitchell\Desktop\Temp Accounts Bot.au3"    
"C:\Users\vmitchell\Desktop\Temp Accounts Bot.au3" (61) : ==> Array variable has incorrect number of subscripts or subscript dimension range exceeded.:
$sR1 = $aArray[$i][0]
$sR1 = ^ ERROR
>Exit code: 1    Time: 15.3

 

I know this is because I took away two of the lines but I am not sure where I fix it at.

Is it in the $aArray or the $i

I did this because I want to get a better idea of how the arrays work whether I have 1 collumn or 10

Edited by SkysLastChance

Sky

Share this post


Link to post
Share on other sites

#7 ·  Posted (edited)

I guess to simplify my question why does this code not work. with this excel

#include <Excel.au3>
#include <Array.Au3>

Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel, @DesktopDir & "\Test.xlsx")
Local $aArray = _Excel_RangeRead($oWorkbook, Default, Default)

    For $i = 0 To UBound($aArray) - 1
        $sR1 = $aArray[$i][0] ; Entry Number
        ControlSend("[CLASS:Notepad]", "", "Edit1", $sR1 & @CR)

    Next

>"C:\Users\vmitchell\Desktop\AutoIt\SciTe\..\autoit3.exe" /ErrorStdOut "C:\Users\vmitchell\Desktop\AutoIt Scripts\Test3.au3"    
"C:\Users\vmitchell\Desktop\AutoIt Scripts\Test3.au3" (9) : ==> Array variable has incorrect number of subscripts or subscript dimension range exceeded.:
$sR1 = $aArray[$i][0]
$sR1 = ^ ERROR
>Exit code: 1    Time: 0.4983

 

Which help file should I be looking at.

Test.xlsx

Edited by SkysLastChance

Sky

Share this post


Link to post
Share on other sites

You are creating a 1D array (use _ArrayDisplay to look at it), but referencing a 2nd dimension in your For loop. This would work just fine.

#include <Excel.au3>

Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel, @DesktopDir & "\Test.xlsx")
Local $aArray = _Excel_RangeRead($oWorkbook, Default, Default)


    For $i = 0 To UBound($aArray) - 1
        $sR1 = $aArray[$i]; Entry Number
        ControlSend("[CLASS:Notepad]", "", "Edit1", $sR1 & @CR)
    Next

 

1 person likes this

When you're dead, you don't know you're dead - it's only difficult for those that know you. It's the same way when you're stupid...

My Scripts: SCCM UDFInclude Source with Compiled Script, Windows Firewall UDF

Share this post


Link to post
Share on other sites

Holy cow. I knew it was going to be something simple. Thank you again friend I was pulling my hair out. 


Sky

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