SkysLastChance Posted November 3, 2016 Share Posted November 3, 2016 (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 April 26, 2017 by SkysLastChance You miss 100% of the shots you don't take. -Wayne Gretzky -Michael Scott Link to comment Share on other sites More sharing options...
water Posted November 3, 2016 Share Posted November 3, 2016 _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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
SkysLastChance Posted November 3, 2016 Author Share Posted November 3, 2016 (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 November 3, 2016 by SkysLastChance Typo You miss 100% of the shots you don't take. -Wayne Gretzky -Michael Scott Link to comment Share on other sites More sharing options...
Moderators JLogan3o13 Posted November 3, 2016 Moderators Share Posted November 3, 2016 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 1 "Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball How to get your question answered on this forum! Link to comment Share on other sites More sharing options...
SkysLastChance Posted November 3, 2016 Author Share Posted November 3, 2016 (edited) 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 You miss 100% of the shots you don't take. -Wayne Gretzky -Michael Scott Link to comment Share on other sites More sharing options...
SkysLastChance Posted November 3, 2016 Author Share Posted November 3, 2016 (edited) >"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 You miss 100% of the shots you don't take. -Wayne Gretzky -Michael Scott Link to comment Share on other sites More sharing options...
SkysLastChance Posted November 3, 2016 Author Share Posted November 3, 2016 (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 November 3, 2016 by SkysLastChance You miss 100% of the shots you don't take. -Wayne Gretzky -Michael Scott Link to comment Share on other sites More sharing options...
Moderators JLogan3o13 Posted November 3, 2016 Moderators Share Posted November 3, 2016 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 1 "Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball How to get your question answered on this forum! Link to comment Share on other sites More sharing options...
SkysLastChance Posted November 3, 2016 Author Share Posted November 3, 2016 Holy cow. I knew it was going to be something simple. Thank you again friend I was pulling my hair out. You miss 100% of the shots you don't take. -Wayne Gretzky -Michael Scott Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now