[Solved] Why do I keep losing the count on my variable?

SkysLastChance · February 22, 2017

I keep losing the count of my $r varable when I go into the dropdown () function and call the same function.

#include <Excel.au3>
#include <AutoItConstants.au3>
#include<GUIConstantsEx.au3>
#include<EditConstants.au3>
#include<GUIConstants.au3>


Global $iBox, $oExcel, $oWorkbook, $r = 1, $x = 3


HowMany()
Excel ()
Dropdown ()

Sleep (500)

Func Dropdown ()

   $list = GUICreate("Chart", 225, 80)
   $combobox = GUICtrlCreateCombo("Clinic 1", 10, 10, 120, 20)
      GUICtrlSetData(-1, "Clinic 2|Clinic 3|Clinic 4")
   $button = GUICtrlCreateButton("Select", 10, 40, 60, 20)
         GUISetState(@SW_SHOW)

While 1
    $guibox = GUIGetMsg()
    Select
        Case $guibox = $button
            Select
            Case GUICtrlRead($combobox) = "Clinic 1"
                    GUIDelete($list)
                    Auto ()



            EndSelect
        Case $guibox = $GUI_EVENT_CLOSE

    EndSelect

WEnd

EndFunc



Func HowMany()
    Local $iMsg
    While 1
        ;~ Turn input into a number
        $iBox = Number(InputBox ("Regestration", "How Many Patients are there total?"))
        ;~ If user enters a string or cancels then get them to try again
        If $iBox = 0 Then
            $iMsg = MsgBox(1,'Regestration', 'Please enter a valid number')
            If $iMsg = 2 Then Exit
        Else
            ExitLoop
        EndIf
    WEnd
EndFunc



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
    ;~ Doesn't require sleep since the script is paused by the MsgBox above
    ;~ Sleep(250)
    WEnd
    ;~ Shouldn't use Global Scope inside functions moved to top of script
    Local $sExcelFile = FileOpenDialog("Choose/Create Excel File", @ScriptDir, "(*.xlsx)")

    If FileExists($sExcelFile) Then
        ;~ Shouldn't use Global Scope inside functions moved to top of script
        $oExcel = _Excel_Open ()
        $oWorkbook = _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


Func Auto()


    Local $aArray1 = _Excel_RangeRead($oWorkbook)
    ;~ If $iBox is greater than no. of rows in $aArray then $iBox equals the number of rows in $aArray
    If $iBox > (UBound($aArray1) - 1) Then $iBox = UBound($aArray1) - 1
    For $i = 2 To UBound($aArray1) - 1   ;$i =0 Start from row A
    If $aArray1[$i][1] = "" Then Continueloop
        $sR0  = $aArray1[$i][0]    ;status
        $sR1  = $aArray1[$i][1]    ;Last Name
        $sR2  = $aArray1[$i][2]    ;First Name
        $sR3  = $aArray1[$i][3]    ;DOB
        $sR4  = $aArray1[$i][4]    ;Sex
        $sR5  = $aArray1[$i][5]    ;Mailling Address
        $sR6  = $aArray1[$i][6]    ;Zip
        $sR7  = $aArray1[$i][7]    ;Phone #
        $sR8  = $aArray1[$i][8]    ;Visit Reason
        $sR9  = $aArray1[$i][9]    ;Insurance
        $sR10 = $aArray1[$i][10]   ;Clinic
        $sR11 = $aArray1[$i][11]   ;Provider
        $sR12 = $aArray1[$i][12]   ;Appt Time
        $sR13 = $aArray1[$i][13]   ;Appt Date


       WinWaitActive ("Untitled - Notepad")

       ControlSend("Untitled - Notepad", "", "", $sR1 & ',' & $sR2 & @CR)

      $r += 1
      If  $r > $iBox Then Exit

Dropdown ()

Next
EndFunc

so the second time I choose clinic 1 I want it to go to the next row which would be Champ brett in my example excel.

Test.xlsx

Example Format.xlsx

Edited February 24, 2017 by SkysLastChance
Script is working with Test.xlsx

water · February 22, 2017

I can only guess: Maybe one of the UDFs uses $r.
I would replace $r with a meaningful name like $iNumberOf Rows and try again.

SkysLastChance · February 22, 2017

I did not even think about the UDF. It did not work though.

JohnOne · February 22, 2017

What does "losing the count" mean exactly?

There might be a weird recursion issue in your code somewhere.

water · February 22, 2017

I think the problem is caused by the way your script calls functions: You call DropDown which calls Auto which calls DropDown which calls Auto ... You get the message. If you do this too often (3900 limit for x64 and 1900 limit for x86) you will get a recursion error.
I think you need to reorganize your script

Subz · February 22, 2017

Will try again, maybe?

Func Dropdown ()
    $r = 1

Edited February 22, 2017 by Subz

SkysLastChance · February 23, 2017

I gave it a go @Subz it did not work unfortunately. Even if it did work I feel like I am only going to run into more problems when I start adding more CASE’s . L @water the best thing I can think of is to have a separate script for each case and keep track of the number of rows with a separate input box. I was wondering if you might have any better ideas then that? What would be awesome if there was a way I could keep the row count between two or more scripts is that possible?

Edit: I also wanted to mention the script is running with the test.xlsx

Edited February 23, 2017 by SkysLastChance

water · February 23, 2017

Can you please describe what you want to achieve?
Should every clinic read from a separate workbook or a separate sheet of the same workbook?
How would this work if the user selected an empty workbook?

SkysLastChance · February 23, 2017

Sure no problem. Thank you for taking the time once again to help me out.

I will have a list of patients in groups of 3’s or 6’s, usually a total of around 30.

It is either a list of 3 patients to a group or 6. (Never mixed)

The groups of 3 and 6 patients will always go into the same clinic.

I might however have one group of 3 patients that goes in clinic 1 but another group of 3 on that same list that go into clinic 2

Ideally I would want to select a clinic after each group of 3 or 6 patients.

So I want to select from the drop down box. (probably going to have about 30-40 different clinics.) then have the script enter in the patient details (group details if possible) and then pop up dropdown again asking me for which clinic so I can get to the right place on my program and then select the correct clinic again. Rinse repeat. (Eventually, I am going to make it go to the right clinic on its own)

I probably should mention that each CASE of the 30-40 clinics will be quite lengthy.

If the user did not select the correct workbook the script would fail. (I need to put in some type of error)

I have also attached a copy of the excel format that I get so you can get a better idea of what it is doing.

(example format)

Edited February 23, 2017 by SkysLastChance

benners · February 23, 2017

23 hours ago, SkysLastChance said:

I keep losing the count of my $r varable when I go into the dropdown () function and call the same function.

You aren't losing the value (count) in $r, you just aren't doing anything with the variable. In The auto function you're always starting from 2 when you re-enter the function.

For $i = 2 To UBound($aArray1) - 1

This will always read the same row. If you use $r as the row it reads the next row, but only if you use a static variable to keep the row count.

Using the code below I selected 3 and 6 for the patient number and got 3 and 6 patient names entered into notepad. Is this what you wanted to do?

#include <Excel.au3>
#include <AutoItConstants.au3>
#include<GUIConstantsEx.au3>
#include<EditConstants.au3>
#include<GUIConstants.au3>


Global $iBox, $oExcel, $oWorkbook, $x = 3 ; #### $r removed ####


HowMany()
Excel()
Dropdown()

Sleep(500)

Func Dropdown()

    $list = GUICreate("Chart", 225, 80)
    $combobox = GUICtrlCreateCombo("Clinic 1", 10, 10, 120, 20)
    GUICtrlSetData(-1, "Clinic 2|Clinic 3|Clinic 4")
    $button = GUICtrlCreateButton("Select", 10, 40, 60, 20)
    GUISetState(@SW_SHOW)

    While 1
        $guibox = GUIGetMsg()
        Select
            Case $guibox = $button
                Select
                    Case GUICtrlRead($combobox) = "Clinic 1"
                        GUIDelete($list)
                        Auto()



                EndSelect
            Case $guibox = $GUI_EVENT_CLOSE

        EndSelect

    WEnd

EndFunc   ;==>Dropdown



Func HowMany()
    Local $iMsg
    While 1
;~ Turn input into a number
        $iBox = Number(InputBox("Registration", "How Many Patients are there total?")) ; #### changed to Registration ####
;~ If user enters a string or cancels then get them to try again
        If $iBox = 0 Then
            $iMsg = MsgBox(1, 'Registration', 'Please enter a valid number') ; #### changed to Registration ####
            If $iMsg = 2 Then Exit
        Else
            ExitLoop
        EndIf
    WEnd
EndFunc   ;==>HowMany



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
;~ Doesn't require sleep since the script is paused by the MsgBox above
;~ Sleep(250)
    WEnd
;~ Shouldn't use Global Scope inside functions moved to top of script
    Local $sExcelFile = FileOpenDialog("Choose/Create Excel File", @ScriptDir, "(*.xlsx)")

    If FileExists($sExcelFile) Then
;~ Shouldn't use Global Scope inside functions moved to top of script
        $oExcel = _Excel_Open()
        $oWorkbook = _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   ;==>Excel


Func Auto()
local static $r = 2 ; ## keeps count when leaving function. Will need to reset at some point for future records ##

    Local $aArray1 = _Excel_RangeRead($oWorkbook)
;~ If $iBox is greater than no. of rows in $aArray then $iBox equals the number of rows in $aArray
    If $iBox > (UBound($aArray1) - 1) Then $iBox = UBound($aArray1) - 1
    For $i = $r To UBound($aArray1) - 1 ;$i =0 Start from row A
        If $aArray1[$i][1] = "" Then ContinueLoop
        $sR0 = $aArray1[$i][0] ;status
        $sR1 = $aArray1[$i][1] ;Last Name
        $sR2 = $aArray1[$i][2] ;First Name
        $sR3 = $aArray1[$i][3] ;DOB
        $sR4 = $aArray1[$i][4] ;Sex
        $sR5 = $aArray1[$i][5] ;Mailling Address
        $sR6 = $aArray1[$i][6] ;Zip
        $sR7 = $aArray1[$i][7] ;Phone #
        $sR8 = $aArray1[$i][8] ;Visit Reason
        $sR9 = $aArray1[$i][9] ;Insurance
        $sR10 = $aArray1[$i][10] ;Clinic
        $sR11 = $aArray1[$i][11] ;Provider
        $sR12 = $aArray1[$i][12] ;Appt Time
        $sR13 = $aArray1[$i][13] ;Appt Date

        run('notepad.exe') ; ### added as infinite wait for window.It wasn't in the original script in post 1 ###
        WinWaitActive("Untitled - Notepad")

        ControlSend("Untitled - Notepad", "", "", $sR1 & ',' & $sR2 & @CR)

;~      $r += 1
        If $r > $iBox Then Exit
        $r += 1 ; ### added increase here ###
        Dropdown()

    Next
EndFunc   ;==>Auto

As mentioned by Water, give the variables meaningful names, pass parameters to functions to reduce global vars and check if the global vars you do have need to be global.

$oExcel for example could be local to Excel(), in this script example anyway.

Hope this helps

SkysLastChance · February 24, 2017

Thank you @benners this is exaclty what I was asking for however, I don't think running a loop inside of a loop is going to be a great idea. I was wondering if this would be any better?

The code I came up with below It is doing what I need it to do. (Keep in mind I only have 1 case) I am just worried I still might run into some recursion issues because it is still inside the function. IDK though I was hoping someone would have a better idea.

I was also was wondering if will autoit let me know when I have hit the maximum limit of lines?

#include <Excel.au3>
#include<EditConstants.au3>
#include<GUIConstants.au3>



Global $r = 1, $oExcel, $oWorkbook, $Row = 0,$iBox,$x = 1

Total ()
Excel ()
Dropdown ()





Func Total()
    Local $iMsg
    While 1
        ;~ Turn input into a number
        $iBox = Number(InputBox ("Registration", "How many patients are in each group?"))
        ;~ If user enters a string or cancels then get them to try again
        If $iBox = 0 Then
            $iMsg = MsgBox(1,'Vital Site', 'Please enter a valid number')
            If $iMsg = 2 Then Exit
        Else
            ExitLoop
        EndIf
    WEnd
EndFunc

Func Dropdown ()

   $list = GUICreate("Chart", 225, 80)
   $combobox = GUICtrlCreateCombo("Clinic 1", 10, 10, 120, 20)
      GUICtrlSetData(-1, "Clinic 2|Clinic 3|Clinic 4")
   $button = GUICtrlCreateButton("Select", 10, 40, 60, 20)
         GUISetState(@SW_SHOW)

While 1
    $guibox = GUIGetMsg()
    Select
        Case $guibox = $button
            Select
            Case GUICtrlRead($combobox) = "Clinic 1"
                    GUIDelete($list)
                    MyAuto ()



            EndSelect
         Case $guibox = $GUI_EVENT_CLOSE
                   Exit
      EndSelect
WEnd

EndFunc

Func Excel ()
   $oExcel = _Excel_Open()
   $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\_Excel1.xls")
EndFunc



Func MyAuto ()



Local $Result = _Excel_RangeRead($oWorkbook)

Do
    Ubound($Result)
    If $Result[$Row][1] = "" Then ContinueLoop
    $yx = $Result[$Row][1]           ; [1] starts read from Column B

    $sWrite = _Excel_RangeWrite($oWorkbook, Default, $yx, "F" & $r)

$r += 1
$Row += 1
$x += 1

Until $x > $iBox

$x = 1

Dropdown ()

EndFunc

Subz · February 24, 2017

Had an hour to spend this evening and may have gotten a little bit carried away and probably not exactly what you want but here it is:

#include <Excel.au3>
#include <ComboConstants.au3>
#include <DateTimeConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>

Global $iBox, $oExcel, $oWorkbook, $r = 1, $x = 3
Global $idAddress, $idApptDate, $idApptTime, $idClinic, $idDOB, $idFirstName, $idInsurance, $idLastName, $idNext, $idRef, $idPhone, $idPrevious, $idProvider, $idSave, $idSex, $idStatus, $idVisitReason, $idZip

Clients()

Func Clients()
    $sExcelFile = @ScriptDir & '\test.xlsx'
    $oExcel = _Excel_Open ()
    $oWorkbook = _Excel_BookOpen($oExcel,$sExcelFile) ;this will open the chosen xls file.
    $aArray1 = _Excel_RangeRead($oWorkbook)

    $Form = GUICreate("Form", 645, 310)
        GUICtrlCreateLabel("id#", 335, 5, 100, 21, $SS_CENTERIMAGE)
        $idRef = GUICtrlCreateLabel("0", 435, 5, 250, 21, $SS_CENTERIMAGE)
        GUICtrlCreateLabel("Status", 10, 5, 100, 21, $SS_CENTERIMAGE)
        $idStatus = GUICtrlCreateInput("", 115, 5, 100, 21)

        GUICtrlCreateLabel("First Name", 10, 30, 100, 21, $SS_CENTERIMAGE)
        $idFirstName = GUICtrlCreateInput("", 115, 30, 200, 21)
        GUICtrlCreateLabel("Last Name", 335, 30, 100, 21, $SS_CENTERIMAGE)
        $idLastName = GUICtrlCreateInput("", 435, 30, 200, 21)

        GUICtrlCreateLabel("Date Of Birth", 10, 55, 100, 21, $SS_CENTERIMAGE)
        $idDOB = GUICtrlCreateDate("", 115, 55, 200, 21)
        GUICtrlSendMsg($idDOB, $DTM_SETFORMATW, 0, "yyyy/MM/dd")
        GUICtrlCreateLabel("Sex", 335, 55, 100, 21, $SS_CENTERIMAGE)
        $idSex = GUICtrlCreateCombo("Select", 435, 55, 200, 21, BitOR($CBS_DROPDOWN,$CBS_AUTOHSCROLL))
        GUICtrlSetData($idSex, "M|F", "Select")

        GUICtrlCreateLabel("Address", 10, 80, 100, 21, $SS_CENTERIMAGE)
        $idAddress = GUICtrlCreateEdit("", 115, 80, 200, 45, BitOR($WS_VSCROLL, $ES_AUTOVSCROLL))
        GUICtrlCreateLabel("Zip", 335, 80, 100, 21, $SS_CENTERIMAGE)
        $idZip = GUICtrlCreateInput("", 435, 80, 200, 21)
        GUICtrlCreateLabel("Phone", 335, 105, 100, 21, $SS_CENTERIMAGE)
        $idPhone = GUICtrlCreateInput("", 435, 105, 200, 21)

        GUICtrlCreateLabel("Visit Reason", 10, 145, 100, 21, $SS_CENTERIMAGE)
        $idVisitReason = GUICtrlCreateEdit("", 115, 145, 200, 45, BitOR($WS_VSCROLL, $ES_AUTOVSCROLL))
        GUICtrlCreateLabel("Insurance", 335, 145, 100, 21, $SS_CENTERIMAGE)
        $idInsurance = GUICtrlCreateInput("", 435, 145, 200, 21)
        GUICtrlCreateLabel("Provider", 335, 170, 100, 21, $SS_CENTERIMAGE)
        $idProvider = GUICtrlCreateInput("", 435, 170, 200, 21)

        GUICtrlCreateLabel("Clinic", 10, 195, 100, 21, $SS_CENTERIMAGE)
        $idClinic = GUICtrlCreateCombo("Select", 115, 195, 200, 21, BitOR($CBS_DROPDOWN,$CBS_AUTOHSCROLL))
        GUICtrlSetData($idClinic, "Clinic 2|Clinic 3|Clinic 4", "Select")

        GUICtrlCreateLabel("Appointment Date", 10, 220, 100, 21, $SS_CENTERIMAGE)
        $idApptDate = GUICtrlCreateDate("", 115, 220, 200, 21)
        GUICtrlSendMsg($idApptDate, $DTM_SETFORMATW, 0, "yyyy/MM/dd")
        GUICtrlCreateLabel("Appointment Time", 335, 220, 100, 21, $SS_CENTERIMAGE)
        $idApptTime = GUICtrlCreateInput("", 435, 220, 200, 21)

        $idSave = GUICtrlCreateButton('Save Record', 535, 250, 100, 25)
        GUICtrlSetState($idSave, $GUI_DISABLE)
        $idPrevious = GUICtrlCreateButton('< Previous Record', 10, 280, 100, 25)
        $idNext = GUICtrlCreateButton('Next Record >', 535, 280, 100, 25)

        ClientArray($aArray1, 1)
    GUISetState(@SW_SHOW)

    While 1
        $nMsg = GUIGetMsg()
        Switch $nMsg
            Case $GUI_EVENT_CLOSE
                _Excel_Close($oExcel)
                Exit
            Case $idSave

            Case $idNext
                SaveArray($aArray1, Number(GUICtrlRead($idRef)))
                If Number(GUICtrlRead($idRef)) + 1 > UBound($aArray1) - 1 Then ContinueLoop
                ClientArray($aArray1, Number(GUICtrlRead($idRef)) + 1)
                If Number(GUICtrlRead($idRef)) = UBound($aArray1) - 1 Then GUICtrlSetState($idNext, $GUI_DISABLE)
                If Number(GUICtrlRead($idRef)) > 1 Then GUICtrlSetState($idPrevious, $GUI_ENABLE)
            Case $idPrevious
                SaveArray($aArray1, Number(GUICtrlRead($idRef)))
                If Number(GUICtrlRead($idRef)) - 1 < 1  Then ContinueLoop
                ClientArray($aArray1, Number(GUICtrlRead($idRef)) - 1)
                If Number(GUICtrlRead($idRef)) = 1 Then GUICtrlSetState($idPrevious, $GUI_DISABLE)
                If Number(GUICtrlRead($idRef)) < UBound($aArray1) - 1 Then GUICtrlSetState($idNext, $GUI_ENABLE)
        EndSwitch
    WEnd
EndFunc

Func SaveArray(ByRef $aArray, $iRef)

    $aArray[$iRef][0] = GuiCtrlRead($idStatus)
    $aArray[$iRef][1] = GuiCtrlRead($idLastName)
    $aArray[$iRef][2] = GuiCtrlRead($idFirstName)
    If GUICtrlRead($idDOB) = "" Then
        $aArray[$iRef][3] = ""
    Else
        $aArray[$iRef][3] = GuiCtrlRead($idDOB)
    EndIf
    $aArray[$iRef][4] = GuiCtrlRead($idSex)
    $aArray[$iRef][5] = GuiCtrlRead($idAddress)
    $aArray[$iRef][6] = GuiCtrlRead($idZip)
    $aArray[$iRef][7] = GuiCtrlRead($idPhone)
    $aArray[$iRef][8] = GuiCtrlRead($idVisitReason)
    $aArray[$iRef][9] = GuiCtrlRead($idInsurance)
    $aArray[$iRef][10] = GuiCtrlRead($idClinic)
    $aArray[$iRef][11] = GuiCtrlRead($idProvider)
    $aArray[$iRef][12] = GuiCtrlRead($idApptTime)
    If GUICtrlRead($idApptDate) = "" Then
        $aArray[$iRef][13] = ""
    Else
        GuiCtrlRead($idApptDate)
    EndIf
EndFunc

Func ClientArray(ByRef $aArray, $iRef)
    If UBound($aArray) - 1 < $iRef Then Return
    GuiCtrlSetData($idRef, $iRef)
    GuiCtrlSetData($idStatus, $aArray[$iRef][0])
    GuiCtrlSetData($idLastName, $aArray[$iRef][1])
    GuiCtrlSetData($idFirstName, $aArray[$iRef][2])
    If StringLen($aArray[$iRef][3]) = 14 Then
        GUICtrlSendMsg($idDOB, $DTM_SETFORMATW, 0, "yyyy/MM/dd")
        GuiCtrlSetData($idDOB, StringFormat("%04i\%02i\%02i" ,StringLeft($aArray[$iRef][3], 4), StringMid($aArray[$iRef][3], 5, 2), StringMid($aArray[$iRef][3], 7, 2)))
    ElseIf StringLen($aArray[$iRef][3]) = 0 Then
        GUICtrlSendMsg($idDOB, $DTM_SETFORMATW, 0, " ")
    Else
        GUICtrlSendMsg($idApptDate, $DTM_SETFORMATW, 0, "yyyy/MM/dd")
        GuiCtrlSetData($idDOB, $aArray[$iRef][3])
    EndIf
    GuiCtrlSetData($idSex, $aArray[$iRef][4])
    GuiCtrlSetData($idAddress, $aArray[$iRef][5])
    GuiCtrlSetData($idZip, $aArray[$iRef][6])
    GuiCtrlSetData($idPhone, $aArray[$iRef][7])
    GuiCtrlSetData($idVisitReason, $aArray[$iRef][8])
    GuiCtrlSetData($idInsurance, $aArray[$iRef][9])
    If $aArray[$iRef][10] = "" Then
        GuiCtrlSetData($idClinic, "Select")
    Else
        GuiCtrlSetData($idClinic, $aArray[$iRef][10])
    EndIf
    GuiCtrlSetData($idProvider, $aArray[$iRef][11])
    GuiCtrlSetData($idApptTime, $aArray[$iRef][12])
    If StringLen($aArray[$iRef][13]) = 14 Then
        GUICtrlSendMsg($idApptDate, $DTM_SETFORMATW, 0, "yyyy/MM/dd")
        GuiCtrlSetData($idApptDate, StringFormat("%04i\%02i\%02i" ,StringLeft($aArray[$iRef][13], 4), StringMid($aArray[$iRef][13], 5, 2), StringMid($aArray[$iRef][13], 7, 2)))
    ElseIf StringLen($aArray[$iRef][13]) = 0 Then
        GUICtrlSendMsg($idApptDate, $DTM_SETFORMATW, 0, " ")
    Else
        GUICtrlSendMsg($idApptDate, $DTM_SETFORMATW, 0, "yyyy/MM/dd")
        GuiCtrlSetData($idApptDate, $aArray[$iRef][13])
    EndIf
EndFunc

Test.xlsx

benners · February 24, 2017

9 hours ago, SkysLastChance said:

Thank you @benners this is exaclty what I was asking for however, I don't think running a loop inside of a loop is going to be a great idea. I was wondering if this would be any better?

The code I came up with below It is doing what I need it to do. (Keep in mind I only have 1 case) I am just worried I still might run into some recursion issues because it is still inside the function. IDK though I was hoping someone would have a better idea.

I was also was wondering if will autoit let me know when I have hit the maximum limit of lines?

I tried the code using the Example Format.xlsx in the first post and it didn't work for me. Once this line is triggered,

If $Result[$Row][1] = "" Then ContinueLoop

$Row is not incremented so the returned value will always be blank. You need to alter to something like below.

If $Result[$Row][1] <> "" Then
    $yx = $Result[$Row][1] ; [1] starts read from Column B
    $sWrite = _Excel_RangeWrite($oWorkbook, Default, $yx, "F" & $r)
EndIf

Then it runs but keeps on going until it errors. not much time to play at the moment, but I can have a look. Maybe see if Subz's code is better for you. If you want to do things the same way as your original post we can have a look at improving it.

SkysLastChance · February 24, 2017

@Subz I wish I could write something like this in a day let alone an hour. My code may have been a little misleading. I will be honest a lot of the GUI code is over my head at the moment. However I was able to figure out how to use it all besides how to save a record. From your example I would want to select the entire champ group instead of 1 patient at a time. (so those 6 patients) select a clinic for them all and insert them one after another using a function like the one below. I know I need to spend more time with GUI I have a feeling I am going to learn a lot from your code.

@benners I see what you mean. So I am back to figuring out how to skip a blank rows again. I will see what I can come up with. @Subz originally helped me with the

If $Result[$Row][1] = "" Then ContinueLoop

SkysLastChance · February 24, 2017

This seems to work.

Edit: The one thing I am worried about still is the Dropdown () function right before the EndFunc. Is there a way I can end the "Do.. Until" function without exiting the script?

#include <Excel.au3>
#include<EditConstants.au3>
#include<GUIConstants.au3>



Global $r = 1, $oExcel, $oWorkbook, $Row = 0,$iBox,$x = 1

Total ()
Excel ()
Dropdown ()





Func Total()
    Local $iMsg
    While 1
        ;~ Turn input into a number
        $iBox = Number(InputBox ("Registration", "How many patients are in each group?"))
        ;~ If user enters a string or cancels then get them to try again
        If $iBox = 0 Then
            $iMsg = MsgBox(1,'Vital Site', 'Please enter a valid number')
            If $iMsg = 2 Then Exit
        Else
            ExitLoop
        EndIf
    WEnd
EndFunc

Func Dropdown ()

   $list = GUICreate("Chart", 225, 80)
   $combobox = GUICtrlCreateCombo("Clinic 1", 10, 10, 120, 20)
      GUICtrlSetData(-1, "Clinic 2|Clinic 3|Clinic 4")
   $button = GUICtrlCreateButton("Select", 10, 40, 60, 20)
         GUISetState(@SW_SHOW)

While 1
    $guibox = GUIGetMsg()
    Select
        Case $guibox = $button
            Select
            Case GUICtrlRead($combobox) = "Clinic 1"
                    GUIDelete($list)
                    MyAuto ()



            EndSelect
         Case $guibox = $GUI_EVENT_CLOSE
                   Exit
      EndSelect
WEnd

EndFunc

Func Excel ()
   $oExcel = _Excel_Open()
   $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\_Excel1.xls")
EndFunc



Func MyAuto ()



Local $Result = _Excel_RangeRead($oWorkbook)




Do
    Ubound($Result)
    If $Result[$Row][1] = "" Then
       $Row += 1
    Endif

    $yx = $Result[$Row][1]


    $sWrite = _Excel_RangeWrite($oWorkbook, Default, $yx, "F" & $r)

$r += 1
$Row += 1
$x += 1

Until $x > $iBox

$x = 1

Dropdown ()

EndFunc

_Excel1.xls

Edited February 24, 2017 by SkysLastChance
Added question

benners · February 24, 2017

19 minutes ago, SkysLastChance said:

@benners I see what you mean. So I am back to figuring out how to skip a blank rows again. I will see what I can come up with. @Subz originally helped me with the

If $Result[$Row][1] = "" Then ContinueLoop

Skip the blanks by using the code in my post above. If the value isn't blank then write the data. When blank it skips the write but would still run the rest of the code after.

I had a play with your code but wasn't sure what you wanted to do really. Now the excel sheet is edited and notepad isn't used. Using your code I would do it like below

#include <Excel.au3>
#include <EditConstants.au3>
#include <MsgBoxConstants.au3>
#include <GUIConstants.au3>

Opt("GUIOnEventMode", 1)

Global $g_h_GUIChart, $g_id_Clinic_cbo, $g_id_Select_btn ; control globals
Global $g_o_Workbook = 0 ; global for excel workbook object
Global $g_i_PatientTotal = Patient_GetTotal() ; # of patients

Excel_OpenWorkbook()
GUI_DrawClinic()

While 1
    Sleep(50)
WEnd

Func Clinic_SetSelected()
    Switch GUICtrlRead($g_id_Clinic_cbo)
        Case 'Clinic 1'
            ; do something here
        Case 'Clinic 2'
            ; do something else here maybe pass parameters to Patient_SetAppointment ?
    EndSwitch

    GUISetState(@SW_HIDE, $g_h_GUIChart) ; hide the gui not delete so no need to recreate each time
    Patient_SetAppointment()
EndFunc   ;==>Clinic_SetSelected

Func Excel_OpenWorkbook()
    Local $s_Workbook = 'D:\Downloads\Example Format.xlsx'

    ; simple error check
    If Not FileExists($s_Workbook) Then
        MsgBox($MB_ICONERROR, 'Workbook', 'The specified workbook was not found' & @CRLF & $s_Workbook)
        Program_Exit()
    EndIf

    Local $o_Excel = _Excel_Open()

    ; simple error check
    If @error Then
        MsgBox($MB_ICONERROR, 'Excel', 'Unable to open an excel instance')
        Program_Exit()
    EndIf

    $g_o_Workbook = _Excel_BookOpen($o_Excel, $s_Workbook)

    ; simple error check
    If @error Then
        MsgBox($MB_ICONERROR, 'Excel', 'Unable to open the workbook' & @CRLF & 'Error: ' & @error)
        Program_Exit()
    EndIf
EndFunc   ;==>Excel_OpenWorkbook

Func GUI_DrawClinic()
    $g_h_GUIChart = GUICreate("Chart", 225, 80)
    GUISetOnEvent($GUI_EVENT_CLOSE, "Program_Exit")

    $g_id_Clinic_cbo = GUICtrlCreateCombo('', 10, 10, 120, 20)
    GUICtrlSetData(-1, "|Clinic 1|Clinic 2|Clinic 3|Clinic 4", 'Clinic 1')

    $g_id_Select_btn = GUICtrlCreateButton("Select", 10, 40, 60, 20)
    GUICtrlSetOnEvent(-1, 'Clinic_SetSelected')
    GUISetState()
EndFunc   ;==>GUI_DrawClinic

Func Patient_GetTotal()
    Do
        $g_i_PatientTotal = InputBox("Registration", "How Many Patients are there in total?") ; prompt for patient #
    Until @error = 1 Or Number($g_i_PatientTotal) ; end if cancelled or number entered

    If @error Then Program_Exit() ; user cancelled
    Return Floor($g_i_PatientTotal) ; round down to closest integer
EndFunc   ;==>Patient_GetTotal

Func Patient_SetAppointment()
    Local $av_RangeRead = _Excel_RangeRead($g_o_Workbook)

    ; simple error check
    If Not IsArray($av_RangeRead) Then
        MsgBox($MB_ICONERROR, 'Excel', 'Unable read the specified range' & @CRLF & 'Error: ' & @error)
        Program_Exit()
    EndIf

    Local $i_TotalRows = UBound($av_RangeRead) - 1
    Local $i_RowCount = 0
    Local Static $i_RunCount = 1

    ; loop here to do what you want
    Do
        ; get patient details from array and do something with them
        MsgBox(0, 'Patient ' & $i_RunCount, 'patient details something, something')
        $i_RowCount += 1
    Until $i_RowCount > 3 ; probably last row with patient details in


    If $i_RunCount >= $g_i_PatientTotal Then
        MsgBox(0, 'End', 'All patients processed'); all patient processed, exit the program
        Program_Exit()
    Else
        $i_RunCount += 1
        GUISetState(@SW_SHOW, $g_h_GUIChart) ; show the gui to select a clinic
    EndIf

    ; ending function so no recursion
EndFunc   ;==>Patient_SetAppointment

Func Program_Exit()
    GUIDelete($g_h_GUIChart) ; delete the gui, no required but nice to do
    ; maybe close excel
    ; any other clean up duties
    Exit ; bye
EndFunc   ;==>Program_Exit

I would also specify the worksheet when you use the RangeRead. If the workbook has been saved with another active sheet the read will fail.

Edited February 24, 2017 by benners

Subz · February 24, 2017

Couldn't you just do something like:

#include <Excel.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>

$sExcelFile = @ScriptDir & '\test.xlsx'
$oExcel = _Excel_Open ()
$oWorkbook = _Excel_BookOpen($oExcel,$sExcelFile) ;this will open the chosen xls file.
$aArray = _Excel_RangeRead($oWorkbook)
Global $iPatients = HowMany()
Dropdown()

Func HowMany()
    Local $iMsg
    While 1
;~ Turn input into a number
        $iPatients = Number(InputBox("Registration", "How Many Patients are there total?")) ; #### changed to Registration ####
;~ If user enters a string or cancels then get them to try again
        If $iPatients = 0 Then
            $iMsg = MsgBox(1, 'Registration', 'Please enter a valid number') ; #### changed to Registration ####
            If $iMsg = 2 Then Exit
        Else
            Return $iPatients - 1
        EndIf
    WEnd
EndFunc   ;==>HowMany

Func Dropdown()
    Local $iCount = 1, $iCurrent = 1
    GUICreate("Chart", 225, 80)
    $idClinic = GUICtrlCreateCombo("Clinic 1", 10, 10, 120, 20)
    GUICtrlSetData(-1, "Clinic 2|Clinic 3|Clinic 4")
    $idSelect = GUICtrlCreateButton("Select", 10, 40, 60, 20)
    GUISetState(@SW_SHOW)

    While 1
        $nMsg = GUIGetMsg()
        Switch $nMsg
            Case $idSelect
                $sClinic = GUICtrlRead($idClinic)
                For $i = $iCurrent To UBound($aArray) - 1
                    If $aArray[$i][1] = "" Or $aArray[$i][2] = "" Or $aArray[$i][10] <> "" Then
                        $iCount = $iCount + 1
                        ContinueLoop
                    EndIf
                    _Excel_RangeWrite($oWorkbook, Default, $sClinic, "K" & $i + 1)
                    $aArray[$i][10] = $sClinic
                    $iCurrent += 1
                    If $i = $iCount + $iPatients Then
                        $iCount = $i
                        $iCurrent = $i
                        ExitLoop
                    EndIf
                Next
            Case $GUI_EVENT_CLOSE
                _Excel_Close($oExcel)
                Exit
        EndSwitch
    WEnd
EndFunc

Test.xlsx

SkysLastChance · February 24, 2017

@Banners this is awesome. I believe I will be able to use your code. Let me give you a better idea of what I want the code to do, with the bad code that I made. XD I just want to give you a better idea. I think I can get yours to do what I want after I figure it all out.

@Subz I feel like I am misleading you. I think this code might give you a better idea of what I am trying to accomplish

I really appreciate all the help from everyone. It is my fault for not being clear.

So using my code below…

Think of notepad as my program. (KINDA)

The “Example Format.xlsx” has 6 patients to a group. So I will select 6

The Champ group all need to go into Clinic 1

The Miller all need to go into Clinic2

The Prater all need to go into Clinic 2

Etc.

When dropdown pops up on my screen I am going to manually go to the right clinic in my program and have my cursor in the right place. I will pick clinic1 and then I will hit “select”

Let it run for those 6 patients and then the dropdown will pop up again asking me witch clinic.

I am then going to manually go to the right clinic in my program and have my cursor in the right place. I will pick clinic2 this time because Miller would be the next group and then I will hit “select”

Eventually I would like auto it to be fully automated but for now I just want to select the clinics as I go.

But for now the Clinic is not actually in the spreadsheet I am hoping to get it added at some point.

So pretend column k does not exist in the “Example Format.xlsx”

I am looking at about 30 diffrent clinics

#include <Excel.au3>
#include<EditConstants.au3>
#include<GUIConstants.au3>



Global $r = 1, $oExcel, $oWorkbook, $Row = 2,$iBox,$x = 1

Total ()
Excel ()
Dropdown ()





Func Total()
    Local $iMsg
    While 1
        ;~ Turn input into a number
        $iBox = Number(InputBox ("Registration", "How many patients are in each group?"))
        ;~ If user enters a string or cancels then get them to try again
        If $iBox = 0 Then
            $iMsg = MsgBox(1,'Vital Site', 'Please enter a valid number')
            If $iMsg = 2 Then Exit
        Else
            ExitLoop
        EndIf
    WEnd
EndFunc

Func Dropdown ()

   $list = GUICreate("Chart", 225, 80)
   $combobox = GUICtrlCreateCombo("Clinic 1", 10, 10, 120, 20)
      GUICtrlSetData(-1, "Clinic 2|Clinic 3")
   $button = GUICtrlCreateButton("Select", 10, 40, 60, 20)
         GUISetState(@SW_SHOW)

While 1
    $guibox = GUIGetMsg()
    Select
        Case $guibox = $button
            Select
            Case GUICtrlRead($combobox) = "Clinic 1"
                    GUIDelete($list)
                    Clinic1 ()
            Case GUICtrlRead($combobox) = "Clinic 2"
                    GUIDelete($list)
                    Clinic2 ()


            EndSelect
         Case $guibox = $GUI_EVENT_CLOSE
                   Exit
      EndSelect
WEnd

EndFunc

Func Excel ()
   $oExcel = _Excel_Open()
   $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\Example Format.xlsx")
EndFunc



Func Clinic1 ()



Local $Result = _Excel_RangeRead($oWorkbook)




Do
    Ubound($Result)
    If $Result[$Row][1] = "" Then
       $Row += 1
    Endif

    $yx0 = $Result[$Row][0] ;Status
    $yx1 = $Result[$Row][1] ;Last Name
    $yx2 = $Result[$Row][2] ;First Name
    $yx3 = $Result[$Row][3] ;DOB
    $yx4 = $Result[$Row][4] ;Sex
    $yx5 = $Result[$Row][5] ;Address
    $yx6 = $Result[$Row][6] ;Zip
    $yx7 = $Result[$Row][7] ;Phone#
    $yx8 = $Result[$Row][8] ;Visit Reason
    $yx9 = $Result[$Row][9] ;Insurance
    $yx10 = $Result[$Row][10] ;Clinic
    $yx11 = $Result[$Row][11] ;Prov
    $yx12 = $Result[$Row][12] ;Appt Time




    WinWaitActive("Untitled - Notepad") ;I want to manually open notepad so all 6 patients will go through the same loop
    ControlSend("[CLASS:Notepad]", "", "Edit1", $yx1 & ',' & $yx2 & @CR)
    ControlSend("[CLASS:Notepad]", "", "Edit1", ("{TAB 2}"))
    ControlSend("[CLASS:Notepad]", "", "Edit1", ("{RIGHT 3}"))
    ControlSend("[CLASS:Notepad]", "", "Edit1", $yx4 & @CR)

$r += 1
$Row += 1
$x += 1

Until $x > $iBox

WinClose("Untitled - Notepad")
    ControlClick ("[CLASS:#32770]", "", "Button2")

$x = 1

Dropdown ()

EndFunc

Func Clinic2 ()



Local $Result = _Excel_RangeRead($oWorkbook)




Do
    Ubound($Result)
    If $Result[$Row][1] = "" Then
       $Row += 1
    Endif

    $yx0 = $Result[$Row][0] ;Status
    $yx1 = $Result[$Row][1] ;Last Name
    $yx2 = $Result[$Row][2] ;First Name
    $yx3 = $Result[$Row][3] ;DOB
    $yx4 = $Result[$Row][4] ;Sex
    $yx5 = $Result[$Row][5] ;Address
    $yx6 = $Result[$Row][6] ;Zip
    $yx7 = $Result[$Row][7] ;Phone#
    $yx8 = $Result[$Row][8] ;Visit Reason
    $yx9 = $Result[$Row][9] ;Insurance
    $yx10 = $Result[$Row][10] ;Clinic
    $yx11 = $Result[$Row][11] ;Prov
    $yx12 = $Result[$Row][12] ;Appt Time




    WinWaitActive("Untitled - Notepad") ;I want to manually open notepad so all 6 patients will go through the same loop
    ControlSend("[CLASS:Notepad]", "", "Edit1", $yx1 & ',' & $yx2 & @CR)
    ControlSend("[CLASS:Notepad]", "", "Edit1", ("{ENTER 8}"))
    ControlSend("[CLASS:Notepad]", "", "Edit1", ("{RIGHT 7}"))
    ControlSend("[CLASS:Notepad]", "", "Edit1", ("{TAB}"))
    ControlSend("[CLASS:Notepad]", "", "Edit1", $yx4 & @CR)


$r += 1
$Row += 1
$x += 1

Until $x > $iBox

WinClose("Untitled - Notepad")
    ControlClick ("[CLASS:#32770]", "", "Button2")

$x = 1

Dropdown ()

EndFunc

I assume I need to make my $yx's global because I will be using the same spread sheet for each clinic.

@Banners I should be able to use your code though right with a few tweaks?

Subz · February 24, 2017

The code above also works on Example Format.xlsx, just need to replace the Excel Range Write with Notepad i.e.

#include <Excel.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>

$sExcelFile = @ScriptDir & '\test.xlsx'
$oExcel = _Excel_Open ()
$oWorkbook = _Excel_BookOpen($oExcel,$sExcelFile) ;this will open the chosen xls file.
$aArray = _Excel_RangeRead($oWorkbook)
Global $iPatients = HowMany()
Dropdown()

Func HowMany()
    Local $iMsg
    While 1
;~ Turn input into a number
        $iPatients = Number(InputBox("Registration", "How Many Patients are there total?")) ; #### changed to Registration ####
;~ If user enters a string or cancels then get them to try again
        If $iPatients = 0 Then
            $iMsg = MsgBox(1, 'Registration', 'Please enter a valid number') ; #### changed to Registration ####
            If $iMsg = 2 Then Exit
        Else
            Return $iPatients - 1
        EndIf
    WEnd
EndFunc   ;==>HowMany

Func Dropdown()
    Local $iCount = 1, $iCurrent = 1
    GUICreate("Chart", 225, 80)
    $idClinic = GUICtrlCreateCombo("Clinic 1", 10, 10, 120, 20)
    GUICtrlSetData(-1, "Clinic 2|Clinic 3|Clinic 4")
    $idSelect = GUICtrlCreateButton("Select", 10, 40, 60, 20)
    GUISetState(@SW_SHOW)

    While 1
        $nMsg = GUIGetMsg()
        Switch $nMsg
            Case $idSelect
                $sClinic = GUICtrlRead($idClinic)
                For $i = $iCurrent To UBound($aArray) - 1
                    If $aArray[$i][1] = "" Or $aArray[$i][2] = "" Or $aArray[$i][10] <> "" Then
                        $iCount = $iCount + 1
                        ContinueLoop
                    EndIf
                    WinWaitActive("Untitled - Notepad") ;I want to manually open notepad so all 6 patients will go through the same loop
                    ControlSend("[CLASS:Notepad]", "", "Edit1", $aArray[$i][1] & ',' & $aArray[$i][2] & @CR)
                    ControlSend("[CLASS:Notepad]", "", "Edit1", ("{TAB 2}"))
                    ControlSend("[CLASS:Notepad]", "", "Edit1", ("{RIGHT 3}"))
                    ControlSend("[CLASS:Notepad]", "", "Edit1", $aArray[$i][4] & @CR)
                    $iCurrent += 1
                    If $i = $iCount + $iPatients Then
                        $iCount = $i
                        $iCurrent = $i
                        ExitLoop
                    EndIf
                Next
            Case $GUI_EVENT_CLOSE
                _Excel_Close($oExcel)
                Exit
        EndSwitch
    WEnd
EndFunc

SkysLastChance · February 24, 2017

Hey @Subz I am not having any luck running your script. It opens the excel I select the clinic and opent notepad and nothing happens? Am I missing somthing?

Sign In

[Solved] Why do I keep losing the count on my variable?

Recommended Posts

SkysLastChance

water

SkysLastChance

JohnOne

water

Subz

SkysLastChance

water

SkysLastChance

benners

SkysLastChance

Subz

benners

SkysLastChance

SkysLastChance

benners

Subz

SkysLastChance

Subz

SkysLastChance

Create an account or sign in to comment

Create an account

Sign in

Similar Content

_ArrayCopyRange

Rearrange array columns based on first row's values

Get the duplicates value of Array

Function not working with array

_ArraySort is Not working correctly ?

Browse

AutoIt Resources

Release

Beta