Jump to content
SkysLastChance

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

Recommended Posts

SkysLastChance

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 by SkysLastChance
Script is working with Test.xlsx

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

Share this post


Link to post
Share on other sites
water

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.

  • Like 1

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
SkysLastChance

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


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

Share this post


Link to post
Share on other sites
water

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 ;)

  • Like 1

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
Subz

Will try again, maybe?

Func Dropdown ()
    $r = 1
Edited by Subz
  • Like 1

Share this post


Link to post
Share on other sites
SkysLastChance

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

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

Share this post


Link to post
Share on other sites
water

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?

  • Like 1

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
SkysLastChance

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

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

Share this post


Link to post
Share on other sites
benners
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

  • Like 1

Share this post


Link to post
Share on other sites
SkysLastChance

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

 


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

Share this post


Link to post
Share on other sites
Subz

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

  • Like 1

Share this post


Link to post
Share on other sites
benners
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.

  • Like 1

Share this post


Link to post
Share on other sites
SkysLastChance

@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

 


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

Share this post


Link to post
Share on other sites
SkysLastChance

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

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

Share this post


Link to post
Share on other sites
benners
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 by benners
  • Like 1

Share this post


Link to post
Share on other sites
Subz

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

  • Like 1

Share this post


Link to post
Share on other sites
SkysLastChance

@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?


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

Share this post


Link to post
Share on other sites
Subz

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

 

Share this post


Link to post
Share on other sites
SkysLastChance

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? 


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

  • Similar Content

    • Skeletor
      By Skeletor
      Hi Virtual People,
      My array works perfectly fine. However, what is the best practice if the line in the array doesn't have the correct amount of columns and if I can add a placeholder?

       
      For $count = 1 To _FileCountLines($FileRead1) Step 1 $string = FileReadLine($FileRead1, $count) $input = StringSplit($string, ",", 1) $value1 = $input[1] $value2 = $input[2] $value3 = $input[3] _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value2, "A1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value1, "B1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value3, "C1") Next  
    • MrCheese
      By MrCheese
      hi all,
      reviewing the forum, this thread is applicable: 
       
       
      I wanted to know if there is now a better way to do this?
      In essence, I load a tab delimited txt file into an array (works well). I used tab, as some fields in the original csv contains commas.
      However, I needed autoit to manipulate this array, and output it as a csv.
      IF my array contains items with a comma, without double quotes around the field, then how best do I get a csv out of this?
      My current workaround is to filewritefromarray tab delimited, then open it in excel and save as a csv. I will need to check this to see how the address fields behave that contain a comma.
       
      Any thoughts would be appreciated.
       
    • Skeletor
      By Skeletor
      Hi All,

      I would like to know how you would take a FileLineRead and insert it into an array which then inserts it into Excel?
      One thing to know is the files content is broken up, so I only use half of the content within $FileRead1.
      So its imperative that the $value1, $value2, etc variables be used. 
      Code below:
      $FileRead1 = FileReadLine("C:\temp\sample.txt",1) For $count = 1 To _FileCountLines($FileRead1) Step 1 $string = FileReadLine($FileRead1, $count) $input = StringSplit($string, ",", 1) $value1 = $input[1] $value2 = $input[2] $value3 = $input[3] $value4 = $input[4] _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value1, "A1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value2, "B1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value3, "C1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value4, "D1") Next  
    • Skeletor
      By Skeletor
      Hi Guys,
      Is it possible to get a variable on your For..Next loop? 
       
      Local $Lines1 = _FileCountLines(C:\temp\test.txt) Local $linesToCount2 = $Lines1 + 2 $var = Number($linesToCount2) For $count = 1 To _FileCountLines($FileRead2) Step 1 For $i = $var To $count Next ;Code does stuff here Next Somehow my code doesn't work even though I thought I could convert the variable to a Integer / Number.
      This code I posted above does not move to the next value.
      But the code below does... why is that?
      For $count = 1 To _FileCountLines($FileRead2) Step 1 For $i = 2 To $count Next ;Code does stuff here Next  
      Why is the For loop resetting itself?
      Is it because the program does not cache the variable and needs to keep on acquiring this variable each time?
      If so , how would you make this variable static?

       
×