SkysLastChance

[Solved] _ExcelReadSheetToArray into _Excel_RangeRead

9 posts in this topic

#1 ·  Posted (edited)

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 by SkysLastChance

Life's simple. You make choices and you don't look back.

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 (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

#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

Life's simple. You make choices and you don't look back.

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

√-1 2^3 ∑ π, and it was delicious!

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

Life's simple. You make choices and you don't look back.

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

Life's simple. You make choices and you don't look back.

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

Life's simple. You make choices and you don't look back.

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

√-1 2^3 ∑ π, and it was delicious!

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. 


Life's simple. You make choices and you don't look back.

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