Gladitren Posted January 30, 2016 Posted January 30, 2016 Hello everyone, I am trying to display a GUI with a couple questions where the user will answer , then i need those exported to an excel sheet. any help here is appreciated. Thank you.
Jfish Posted January 30, 2016 Posted January 30, 2016 (edited) Hello and welcome. What have you tried so far? I would look at the help file in the UDF section. There is a very well documented Excel UDF with examples about writing data to a spreadsheet (and many other things). There are also detailed GUI creation examples. Also, not sure if you have seen Koda but it is a drag and drop GUI creation tool that supports AutoIt syntax. If you take a shot and post your code (try using the code tags <>) then you will usually get help pretty quickly about any snags you may encounter. Edited January 30, 2016 by Jfish Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt
Gladitren Posted January 30, 2016 Author Posted January 30, 2016 i gathered this from both the fourm and the help files / i think/ it writes numbers to excel instead of input. expandcollapse popup#include <Array.au3> #include <Excel.au3> #include <MsgBoxConstants.au3> Global Const $GUI_EVENT_CLOSE = -3 $sDataFilePath = @ScriptDir & "\Records.csv" Local $oExcel = _Excel_Open() If @error Then Exit MsgBox(16, "Excel UDF: _Excel_BookOpen Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Local $sWorkbook = @ScriptDir & "\Extras\_Excel1.xls" Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook) Local $excelobj =@ScriptDir & "\Extras\_Excel1.xls" #region ### START Koda GUI section ### Form= $Form1 = GUICreate("Demo1: New Record", 580, 580) $Input1 = GUICtrlCreateInput("", 130, 10,40) $Input2 = GUICtrlCreateInput("", 130, 30,40) $Input3 = GUICtrlCreateInput("", 130, 50,40) $Input4 = GUICtrlCreateInput("", 130, 70,40) $Input5 = GUICtrlCreateInput("", 130, 90,40) $Input6 = GUICtrlCreateInput("", 130, 110,40) $Input7 = GUICtrlCreateInput("", 130, 130,40) $Input8 = GUICtrlCreateInput("", 130, 150,40) $Input9 = GUICtrlCreateInput("", 130, 170,40) $Input10 = GUICtrlCreateInput("", 130, 190,40) $Input11 = GUICtrlCreateInput("", 130, 210,40) $Input12 = GUICtrlCreateInput("", 130, 230,40) $Input13 = GUICtrlCreateInput("", 130, 250,40) $Input14 = GUICtrlCreateInput("", 130, 270,40) $Input15 = GUICtrlCreateInput("", 130, 290,40) $Input16 = GUICtrlCreateInput("", 130, 310,40) $Input17 = GUICtrlCreateInput("", 130, 330,40) $Input18 = GUICtrlCreateInput("", 130, 350,40) $Input19 = GUICtrlCreateInput("", 320, 10,300) $Input20 = GUICtrlCreateInput("", 320, 30,300) $Label1 = GUICtrlCreateLabel("Item1 :", 10, 10, 100, 17) $Label2 = GUICtrlCreateLabel("Item2 :", 10, 30, 100, 17) $Label3 = GUICtrlCreateLabel("Item3 :", 10, 50, 100, 17) $Label4 = GUICtrlCreateLabel("Item4 :", 10, 70, 100, 17) $Label5 = GUICtrlCreateLabel("Item5 :", 10, 90, 100, 17) $Label6 = GUICtrlCreateLabel("Item6 :", 10, 110, 100, 17) $Label7 = GUICtrlCreateLabel("Item7 :", 10, 130, 100, 17) $Label8 = GUICtrlCreateLabel("Item8 :", 10, 150, 100, 17) $Label9 = GUICtrlCreateLabel("Item9 :", 10, 170, 100, 17) $Label10 = GUICtrlCreateLabel("Item10 :", 10, 190, 100, 17) $Label11 = GUICtrlCreateLabel("Item11 :", 10, 210, 100, 17) $Label12 = GUICtrlCreateLabel("Item12 :", 10, 230, 100, 17) $Label13 = GUICtrlCreateLabel("Item13 :", 10, 250, 100, 17) $Label14 = GUICtrlCreateLabel("Item14 :", 10, 270, 100, 17) $Label15 = GUICtrlCreateLabel("Item15 :", 10, 290, 100, 17) $Label16 = GUICtrlCreateLabel("Item16 :", 10, 310, 100, 17) $Label17 = GUICtrlCreateLabel("Item17 :", 10, 330, 100, 17) $Label18 = GUICtrlCreateLabel("Item18 :", 10, 350, 100, 17) $Label19 = GUICtrlCreateLabel("Item19 :" , 210, 10, 100, 17) $Label20 = GUICtrlCreateLabel("Item20" , 210, 30, 100, 17) $Button1 = GUICtrlCreateButton("Save to CSV", 450, 70, 120, 30) GUISetState(@SW_SHOW) #endregion ### END Koda GUI section ### While 1 $nMsg = GUIGetMsg() Switch $nMsg Case $GUI_EVENT_CLOSE Exit Case $Button1 _ExportData() MsgBox(64, @ScriptName, "Record Saved.") EndSwitch WEnd Func _ExportData() Local $1 = $Input1 local $k=1 Local $aArray1D[90] = [$Input1,$Input2,$Input3,$Input4,$Input5,$Input6,$Input7,$Input8,$Input9,$Input10,$Input11,$Input12,$Input13,$Input14,$Input15,$Input16,$Input17,$Input18,$Input19,$Input20] _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aArray1D, "A3") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet,$Input1 , "A2") For $i = 1 To UBound($Input1) - 1 Next _Excel_RangeWrite($excelobj, $Input19[$i],$k,2) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 2", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended) MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 2", "1D array successfully written.") If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 3", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended) MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 3", "2D array successfully written.") ;If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookOpen Example 1", "Error opening '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended) ;MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookOpen Example 1", "Workbook '" & $sWorkbook & "' has been opened successfully." & @CRLF & @CRLF & "Creation Date: " & $oWorkbook.BuiltinDocumentProperties("Creation Date").Value) ;If Not FileExists($sDataFilePath) Then ; FileWriteLine($sDataFilePath, "LengthOfFence;ChainLinkHight;Posts&PipesHight;") ;EndIf ;For $i = $Input1 To $Input20 ;FileWrite($sDataFilePath, GUICtrlRead($i) & ";") ;Next ;FileWriteLine($sDataFilePath, "") EndFunc ;==>_ExportData
Jfish Posted January 30, 2016 Posted January 30, 2016 Okay, now can you explain what is working or not working about it for you? Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt
Gladitren Posted January 30, 2016 Author Posted January 30, 2016 attached is the result in excel, so instead of inserting what the user inputs, it inserts numbers.
Jfish Posted January 30, 2016 Posted January 30, 2016 Your array holds the names of the controls (the handle returned when they are created). You want to read the data in those controls. To do that you could do something like this (note: I don't start with an existing workbook - also see the loop I inserted toward the bottom to read the data into a new array that is fed into the sheet): expandcollapse popup#include <Array.au3> #include <Excel.au3> #include <MsgBoxConstants.au3> Global Const $GUI_EVENT_CLOSE = -3 $sDataFilePath = @ScriptDir & "\Records.csv" ; Create application object and create a new workbook Local $oAppl = _Excel_Open() If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Local $oWorkbook = _Excel_BookNew($oAppl) If @error Then MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example", "Error creating the new workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended) _Excel_Close($oAppl) Exit EndIf #region ### START Koda GUI section ### Form= $Form1 = GUICreate("Demo1: New Record", 580, 580) $Input1 = GUICtrlCreateInput("", 130, 10,40) $Input2 = GUICtrlCreateInput("", 130, 30,40) $Input3 = GUICtrlCreateInput("", 130, 50,40) $Input4 = GUICtrlCreateInput("", 130, 70,40) $Input5 = GUICtrlCreateInput("", 130, 90,40) $Input6 = GUICtrlCreateInput("", 130, 110,40) $Input7 = GUICtrlCreateInput("", 130, 130,40) $Input8 = GUICtrlCreateInput("", 130, 150,40) $Input9 = GUICtrlCreateInput("", 130, 170,40) $Input10 = GUICtrlCreateInput("", 130, 190,40) $Input11 = GUICtrlCreateInput("", 130, 210,40) $Input12 = GUICtrlCreateInput("", 130, 230,40) $Input13 = GUICtrlCreateInput("", 130, 250,40) $Input14 = GUICtrlCreateInput("", 130, 270,40) $Input15 = GUICtrlCreateInput("", 130, 290,40) $Input16 = GUICtrlCreateInput("", 130, 310,40) $Input17 = GUICtrlCreateInput("", 130, 330,40) $Input18 = GUICtrlCreateInput("", 130, 350,40) $Input19 = GUICtrlCreateInput("", 320, 10,300) $Input20 = GUICtrlCreateInput("", 320, 30,300) $Label1 = GUICtrlCreateLabel("Item1 :", 10, 10, 100, 17) $Label2 = GUICtrlCreateLabel("Item2 :", 10, 30, 100, 17) $Label3 = GUICtrlCreateLabel("Item3 :", 10, 50, 100, 17) $Label4 = GUICtrlCreateLabel("Item4 :", 10, 70, 100, 17) $Label5 = GUICtrlCreateLabel("Item5 :", 10, 90, 100, 17) $Label6 = GUICtrlCreateLabel("Item6 :", 10, 110, 100, 17) $Label7 = GUICtrlCreateLabel("Item7 :", 10, 130, 100, 17) $Label8 = GUICtrlCreateLabel("Item8 :", 10, 150, 100, 17) $Label9 = GUICtrlCreateLabel("Item9 :", 10, 170, 100, 17) $Label10 = GUICtrlCreateLabel("Item10 :", 10, 190, 100, 17) $Label11 = GUICtrlCreateLabel("Item11 :", 10, 210, 100, 17) $Label12 = GUICtrlCreateLabel("Item12 :", 10, 230, 100, 17) $Label13 = GUICtrlCreateLabel("Item13 :", 10, 250, 100, 17) $Label14 = GUICtrlCreateLabel("Item14 :", 10, 270, 100, 17) $Label15 = GUICtrlCreateLabel("Item15 :", 10, 290, 100, 17) $Label16 = GUICtrlCreateLabel("Item16 :", 10, 310, 100, 17) $Label17 = GUICtrlCreateLabel("Item17 :", 10, 330, 100, 17) $Label18 = GUICtrlCreateLabel("Item18 :", 10, 350, 100, 17) $Label19 = GUICtrlCreateLabel("Item19 :" , 210, 10, 100, 17) $Label20 = GUICtrlCreateLabel("Item20" , 210, 30, 100, 17) $Button1 = GUICtrlCreateButton("Save to CSV", 450, 70, 120, 30) GUISetState(@SW_SHOW) #endregion ### END Koda GUI section ### While 1 $nMsg = GUIGetMsg() Switch $nMsg Case $GUI_EVENT_CLOSE Exit Case $Button1 _ExportData() MsgBox(64, @ScriptName, "Record Saved.") EndSwitch WEnd Func _ExportData() Local $1 = $Input1 local $k=1 Local $aArray1D[90] = [$Input1,$Input2,$Input3,$Input4,$Input5,$Input6,$Input7,$Input8,$Input9,$Input10,$Input11,$Input12,$Input13,$Input14,$Input15,$Input16,$Input17,$Input18,$Input19,$Input20] _ArrayDisplay($aArray1D) local $dataArray[20] for $a=0 to ubound($dataArray)-1 $dataArray[$a]=guictrlread($aArray1D[$a]) Next _ArrayDisplay($dataArray) _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $dataArray) EndFunc ;==>_ExportData Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt
Gladitren Posted January 30, 2016 Author Posted January 30, 2016 Thank you for replying, I am not sure i should use array here, i am trying to get the user input into excel, much of this code is unrelated, i know, since i copied it from 2 or more examples and am using trial and error to get it to work, so , if you have a way of getting this done, can you please show it to me, i will clean all the code later cause it is doing more than it is supposed to be.
Gladitren Posted January 30, 2016 Author Posted January 30, 2016 Correction : Not doing ANY of what it is supposed to,
Jfish Posted January 30, 2016 Posted January 30, 2016 (edited) I just showed it to you. Have you tried my code? You were already using arrays in your code ... I just fixed them to use the actual data from the GUI instead of the control handles. The code I posted adjusted yours to write to a spreadsheet. That is what you wanted to do from your OP. Also, I have no idea what this means: Quote cause it is doing more than it is supposed to be Not doing ANY of what it is supposed to, Edited January 31, 2016 by Jfish Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt
Gladitren Posted January 31, 2016 Author Posted January 31, 2016 yes, thank you. can this be done simply by pressing the button on the GUI instead of copying and pasting the data into excel ?
Gladitren Posted January 31, 2016 Author Posted January 31, 2016 this will do it, thank you Func _ExportData() Local $1 = $Input1 local $k=1 Local $aArray1D[90] = [$Input1,$Input2,$Input3,$Input4,$Input5,$Input6,$Input7,$Input8,$Input9,$Input10,$Input11,$Input12,$Input13,$Input14,$Input15,$Input16,$Input17,$Input18,$Input19,$Input20] ;_ArrayDisplay($aArray1D) local $dataArray[20] for $a=0 to ubound($dataArray)-1 $dataArray[$a]=guictrlread($aArray1D[$a]) Next ;_ArrayDisplay($dataArray) _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $dataArray) EndFunc ;==>_ExportData
Gladitren Posted January 31, 2016 Author Posted January 31, 2016 ok, here is one more question , if i wanted $input1 to go to cell A4, and $input2 to go to cell D16, for example, how it that done ?
Jfish Posted January 31, 2016 Posted January 31, 2016 (edited) Quote can this be done simply by pressing the button on the GUI instead of copying and pasting the data into excel That is not what is happening - the data is being sent through the Excel Com API using the Excel UDF. I think you were confused by _arrayDsiplay - that was just in there to show you the contents of the data array before they were sent to Excel. When you close the array display box the program continues and sends the data to Excel. Quote if i wanted $input1 to go to cell A4, and $input2 to go to cell D16, for example, how it that done ? Use the $vRange parameter in the _Excel_RangeWrite function. Look at the help file examples to see how it is used. You have a data array for all the values from all the inputs. If the location of the cells you want to write to will always be the same you could add a range element to the array (make it two dimensional) so that when it loops you pick up the second element as the range. Alternatively, some folks find two dimensional arrays challenging so you could create another 1d array for ranges and loop it with the data values. This shows you how to use a 2d array for the first three elements (I am too lazy to map all 20 as an example). This way you can map the inputs to specific locations on the sheet. Try it out ... Local $aArray2D[3][3] = [[$Input1,"A4"],[$Input2,"D16"],[$Input3,"C6"]] _ArrayDisplay($aArray2D) local $dataArray[20] for $a=0 to ubound($dataArray)-1 $dataArray[$a]=guictrlread($aArray2D[$a][0]) _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $dataArray[$a],$aArray2D[$a][1]) Next Also, your array for the input controls is set to hold 90 elements $aArray1D[90] But you are only using 20 input controls. You can change 90 to 20 unless you need to add 70 more elements ... Lastly, not sure what these are for, you can probably delete them: Local $1 = $Input1 local $k=1 Edited January 31, 2016 by Jfish Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now