get user input and export to excel sheet

Gladitren · 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 · January 30, 2016

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

Gladitren · January 30, 2016

i gathered this from both the fourm and the help files / i think/ it writes numbers to excel instead of input.

#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 · January 30, 2016

Okay, now can you explain what is working or not working about it for you?

Gladitren · January 30, 2016

attached is the result in excel, so instead of inserting what the user inputs, it inserts numbers.

Jfish · 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):

#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

Gladitren · 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 · January 30, 2016

Correction : Not doing ANY of what it is supposed to,

Jfish · January 30, 2016

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

Gladitren · 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 · 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 · 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 · January 31, 2016

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

Gladitren · January 31, 2016

Thank you dear, you are great.

get user input and export to excel sheet

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members