Leaderboard

I decided to enhance the functionality of the OOo COM UDF found >here and >here. Thanks to Leagnus and Andy G for providing the initial functions and framework. This UDF includes the following working functions: _OOoCalc_BookNew _OOoCalc_BookOpen _OOoCalc_BookAttach _OOoCalc_BookSave _OOoCalc_BookSaveAs _OOoCalc_BookClose _OOoCalc_WriteCell _OOoCalc_WriteFormula _OOoCalc_WriteFromArray _OOoCalc_HyperlinkInsert _OOoCalc_RangeMoveOrCopy _OOoCalc_RangeSort _OOoCalc_RangeClearContents _OOoCalc_CreateBorders _OOoCalc_NumberFormat _OOoCalc_ReadCell _OOoCalc_ReadSheetToArray _OOoCalc_RowDelete _OOoCalc_ColumnDelete _OOoCalc_RowInsert _OOoCalc_ColumnInsert _OOoCalc_SheetAddNew _OOoCalc_SheetDelete _OOoCalc_SheetNameGet _OOoCalc_SheetNameSet _OOoCalc_SheetList _OOoCalc_SheetActivate _OOoCalc_SheetSetVisibility _OOoCalc_SheetMove _OOoCalc_SheetPrint _OOoCalc_HorizontalAlignSet _OOoCalc_FontSetProperties _OOoCalc_CellSetColors _OOoCalc_RowSetColors _OOoCalc_ColumnSetColors _OOoCalc_RowSetProperties _OOoCalc_ColumnSetProperties _OOoCalc_FindInRange _OOoCalc_ReplaceInRange Please help me test this and let me know of any problems, questions, suggestions or comments you may have. Edit (August 5, 2016): Latest files have been uploaded. Script-breaking changes include renaming the functions to start with _OOoCalc_. Edit (November 14, 2016): New version, including fixed sort function! Edit (November 15, 2016): Lots of minor changes including tightening up the error checking. Edit (November 16, 2016): Added ByRef to object parameters. Edited demo. OOoCalcConstants.au3 OOoCalc.au3 OOoCalc_Demo.au3

Program Operation 1. Select news group 2. Double-click on any article to read Colors in Listview Light Green = New Topic Light Red = Last Article from previous session - Read Only (does not support post) - Please let me know if you have any troubles with the script. (Tested on WinXP and Win7) March 01, 2017 - Version 1.00 NNTP_NewsReader_v1.00.au3 -

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

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?

I typically use cmdkey for this: Local $site = "www.msufcu.com" ShellExecute("cmdkey", "/delete:" & $site)

Echbiahn, Use GUISwitch to set the GUI in which you want the controls will be created. M23

You can also transpose your array for example: #include <Array.au3> #include <Excel.au3> Local $aArray[26] = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'] $oExcel = _Excel_Open() $oWorkbook = _Excel_BookNew($oExcel) ;~ Write Rows _Excel_RangeWrite($oWorkbook, $oWorkbook.ActiveSheet, $aArray, 'A1') _ArrayTranspose($aArray) ;~ Write Columns _Excel_RangeWrite($oWorkbook, $oWorkbook.ActiveSheet, $aArray, 'A1')

Vex, Using the "==" operator forces both sides of the comparison to strings - as you are comparing numeric values, you should replace it with the standard "=" operator. M23

Will try again, maybe? Func Dropdown () $r = 1

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

... that is: For $a = 0 * 10 To 100 * 10 Step 0.1 * 10 ConsoleWrite($a / 10 & @CRLF) Next

Hey @BetaLeaf , @meoit and @Danyfirex , do you have any further comments, maybe some concepts ? btw. I was working in the background on this UDF and I have plan to update it next week.