[Solved] _ExcelReadSheetToArray into _Excel_RangeRead

[SkysLastChance]

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

 

 

Edited April 26, 2017 by SkysLastChance

[water]

_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). 

[SkysLastChance]

#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 November 3, 2016 by SkysLastChance
Typo

[JLogan3o13]

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

 

[SkysLastChance]

expandcollapse popup

#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 November 3, 2016 by SkysLastChance

[SkysLastChance]

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

expandcollapse popup

#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 November 3, 2016 by SkysLastChance

[SkysLastChance]

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 November 3, 2016 by SkysLastChance

[JLogan3o13]

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

 

[SkysLastChance]

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