GUI Labels Export to Excel

[ct253704]

Hey all,

I am trying to do a simple export from labels within a GUI to the respective columns in an Excel worksheet. Nothing fancy. If they enter "Name" in the name input on the GUI I want it to export to the "Name" column in Excel. I'm not sure where to start with the actual export function. I would be grateful if someone could maybe give me an example or point me to some appropriate functions. Haven't had much like with the help or search features.

The GUI code:

expandcollapse popup

#include <ButtonConstants.au3>
#include <DateTimeConstants.au3>
#include <EditConstants.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>

Global Const $GUI_EVENT_CLOSE = -3

$sDataFilePath = "\\fileserver01\Budget Meetings.xlsx"

#Region ### START Koda GUI section ### Form=C:\Users\admin\BudgetMeeting.kxf
$BudgetMeeting = GUICreate("New Budget Meeting", 580, 304, 193, 134)
$Avatar = GUICtrlCreatePic("Z:\Avatar.jpg", 488, 216, 76, 76)
$Title = GUICtrlCreateLabel("New Budget Meeting Request", 176, 8, 249, 24, BitOR($SS_CENTER,$SS_CENTERIMAGE))
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
GUICtrlSetBkColor(-1, 0x008080)
$TeamMember = GUICtrlCreateLabel("Executive Team Member:", 10, 50, 179, 17)
$Budgeter = GUICtrlCreateLabel("Budgeter:", 10, 106, 202, 17)
$Topic = GUICtrlCreateLabel("To Discuss:", 10, 164, 183, 17)
$Department = GUICtrlCreateLabel("Department(s)", 312, 50, 161, 20)
$Date = GUICtrlCreateLabel("Select Date", 312, 104, 154, 20)
$Time = GUICtrlCreateLabel("Time", 312, 162, 163, 20)
$TeamMemberValue = GUICtrlCreateInput("", 10, 70, 270, 24)
$BudgeterValue = GUICtrlCreateInput("", 10, 126, 270, 24)
$TopicValue = GUICtrlCreateInput("", 10, 184, 270, 24)
$DepartmentValue = GUICtrlCreateInput("", 312, 70, 257, 24)
$DateValue = GUICtrlCreateDate("2014/12/19 08:20:46", 312, 126, 258, 24)
$TimeValue = GUICtrlCreateInput("", 312, 182, 257, 24)
$CreateMeeting = GUICtrlCreateButton("Create Meeting", 242, 262, 120, 30)
GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###

While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit
        Case $CreateMeeting
            Upload()
            MsgBox(64, @ScriptName, "Meeting Saved.")
    EndSwitch
WEnd

Func Upload()
   
   ;Need help here
   
EndFunc

[MikahS]

Have you tried anything with the Excel functions yet? If you haven't already look at the wiki for the Excel UDF.

[ct253704]

Hey MikahS, Thanks for the response.

I looked through that initially and nothing caught my eye as to something I could use that would export in the appropriate format. Most of those functions seemed to do very basic things such as open a workbook or change fonts of a single cell. Mine is slightly more complicated in that I need it to start a new row with each export and delimit into columns. I also tried combining it with commands such as FileWrite and doing a range ($i = $Teammember to $Time) to see if I could get the info to work. It would export to the excel sheet, but unfortunately I couldn't get it to separate out by columns. 

Edited December 19, 2014 by ct253704

[JLogan3o13]

@ct253704, the Excel UDF is far from limited to the basics. For your needs, you can find the column you want to write to with _Excel_RangeFind, then use Excel_RangeWrite to fill it in. Here is a quick and dirty example to start you on the path. This takes into account the columns are worded the same as your labels:

Func Upload()
   Local $oExcel = _Excel_Open()
   Local $oWorkbook = _Excel_BookOpen($oExcel, @DesktopDir & "\Test.xls") ;Open the workbook
   $sMember = GUICtrlRead($TeamMemberValue) ;Get the value in the TeamMember Input Box
      $aTemp = _Excel_RangeFind($oWorkbook, GUICtrlRead($TeamMember)) ;Returns a 2D Array, with index [0][2] showing the range you want
     _Excel_RangeWrite($oWorkbook, Default, $sMember, $aTemp[0][2])
EndFunc

Edited December 19, 2014 by JLogan3o13

[ct253704]

This is just the kind of thing I needed to get me started. Thanks, JLogan. I will play with it and report back here.

[ct253704]

Alright, so I played with it a bit and changed some of the variables and it's working. However, there's one more slight problem. Instead of filling in the first empty cell underneath the heading, it instead replaces the heading of the column unless I fill in every row below it with the same text. For instance, if A1 is Executive Team Member and then A2:A:1000 are all blank, it will replace the heading with the input value. However, if A2: also reads Executive Team Member, then it will bypass the heading and only replace the A2 value (ideal). I just want it to enter in the first empty row.

I tried this (and many variations of) with _Excel_RangeFind, but it still won't work:

Func Upload()

   Local $CreateExcel = _Excel_Open()
   Local $OpenWorkbook = _Excel_BookOpen($CreateExcel, "\\fileserver01\Budget Meetings.xlsx")
   $TeamMemberValue = GUICtrlRead($TeamMemberInput)
   $MemberArray = _Excel_RangeFind($OpenWorkbook, GUICtrlRead($TeamMember))
   $MemberRange = _Excel_RangeRead($OpenWorkbook, Default, Default, 1)
   If $MemberRange = "" Then _Excel_RangeWrite($OpenWorkbook, Default, $TeamMemberValue, $MemberArray[0][2])

EndFunc

I tried using 

_Excel_RangeRead($OpenWorkbook, Default, Default, 3)

as well thinking maybe it would find the cell in the Team Member column that had nothing for text, but that didn't work either.Is there an easy way to do this i'm overlooking?

[JLogan3o13]

Something like this will write to the first empty row:

Func Upload()
   Local $oExcel = _Excel_Open()
   Local $oWorkbook = _Excel_BookOpen($oExcel, @DesktopDir & "\Test.xls")
   Local $oSheet = $oWorkbook.ActiveSheet

   $sMember = GUICtrlRead($TeamMemberValue)
      $aTemp = _Excel_RangeFind($oWorkbook, GUICtrlRead($TeamMember))
        $sColumn = StringMid($aTemp[0][2], 2, 1)
            $oSheet = $oWorkbook.Worksheets(1)

    $oRange = $oSheet.UsedRange
    $oRange.SpecialCells($xlCellTypeLastCell).Activate
    $newRow = $oExcel.ActiveCell.Row + 1

    _Excel_RangeWrite($oWorkbook, Default, $sMember, $sColumn & $newRow)

EndFunc

[ct253704]

Once again, thank you @JLogan3o13

I had to modify it a bit as each column it would move down a row giving me a "stair" effect, haha, but alas it's working. I know some of the code is fluff and I will tidy it up later after Christmas, but I really would not have figured that out on my own. Even after looking through the UDF some of that still confused me (Mostly the Activate, SpecialCells, etc). Thanks for all your help!

Working code at the moment:

expandcollapse popup

Func Export()

   Local $CreateExcel = _Excel_Open()
   Local $OpenWorkbook = _Excel_BookOpen($CreateExcel, "\\fileserver01\Budget Meetings.xlsx")
   Local $OpenSheet = $OpenWorkbook.ActiveSheet

   $TeamMemberValue = GUICtrlRead($TeamMemberInput) ;Get the value in the TeamMember Input Box
   $MemberArray = _Excel_RangeFind($OpenWorkbook, GUICtrlRead($TeamMember)) ;Returns a 2D Array, with index [0][2] showing the range you want
   $sColumn = StringMid($MemberArray[0][2], 2, 1)
   $OpenSheet = $OpenWorkbook.Worksheets(1)
   $Range = $OpenSheet.UsedRange
   $Range.SpecialCells($xlCellTypeLastCell).Activate
   $newRow = $CreateExcel.ActiveCell.Row + 1
   _Excel_RangeWrite($OpenWorkbook, Default, $TeamMemberValue, $scolumn & $newRow)

   $BudgeterValue = GUICtrlRead($BudgeterInput)
   $BudgeterArray = _Excel_RangeFind($OpenWorkbook, GUICtrlRead($Budgeter))
   $sColumn = StringMid($BudgeterArray[0][2], 2, 1)
   $OpenSheet = $OpenWorkbook.Worksheets(1)
   $Range = $OpenSheet.UsedRange
   $Range.SpecialCells($xlCellTypeLastCell).Activate
   $samerow = $CreateExcel.ActiveCell.Row
   _Excel_RangeWrite($OpenWorkbook, Default, $BudgeterValue, $scolumn & $sameRow)

   $TopicValue = GUICtrlRead($TopicInput)
   $TopicArray = _Excel_RangeFind($OpenWorkbook, GUICtrlRead($Topic))
   $sColumn = StringMid($TopicArray[0][2], 2, 1)
   $OpenSheet = $OpenWorkbook.Worksheets(1)
   $Range = $OpenSheet.UsedRange
   $Range.SpecialCells($xlCellTypeLastCell).Activate
   _Excel_RangeWrite($OpenWorkbook, Default, $TopicValue, $scolumn & $sameRow)

   $DepartmentValue = GUICtrlRead($DepartmentInput)
   $DepartmentArray = _Excel_RangeFind($OpenWorkbook, GUICtrlRead($Department))
   $sColumn = StringMid($DepartmentArray[0][2], 2, 1)
   $OpenSheet = $OpenWorkbook.Worksheets(1)
   $Range = $OpenSheet.UsedRange
   $Range.SpecialCells($xlCellTypeLastCell).Activate
   _Excel_RangeWrite($OpenWorkbook, Default, $DepartmentValue, $scolumn & $sameRow)

   $DateValue = GUICtrlRead($DateInput)
   $DateArray = _Excel_RangeFind($OpenWorkbook, GUICtrlRead($Date))
   $sColumn = StringMid($DateArray[0][2], 2, 1)
   $OpenSheet = $OpenWorkbook.Worksheets(1)
   $Range = $OpenSheet.UsedRange
   $Range.SpecialCells($xlCellTypeLastCell).Activate
   _Excel_RangeWrite($OpenWorkbook, Default, $DateValue, $scolumn & $sameRow)

   $TimeValue = GUICtrlRead($TimeInput)
   $TimeArray = _Excel_RangeFind($OpenWorkbook, GUICtrlRead($Time))
   $sColumn = StringMid($TimeArray[0][2], 2, 1)
   $OpenSheet = $OpenWorkbook.Worksheets(1)
   $Range = $OpenSheet.UsedRange
   $Range.SpecialCells($xlCellTypeLastCell).Activate
   _Excel_RangeWrite($OpenWorkbook, Default, $TimeValue, $scolumn & $sameRow)

EndFunc