Sign in to follow this  
Followers 0
ct253704

GUI Labels Export to Excel

8 posts in this topic

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:

#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

Share this post


Link to post
Share on other sites



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


Snips & Scripts


My Snips: graphCPUTemp ~ getENVvars
My Scripts: Short-Order Encrypter - message and file encryption V1.6.1 ~ AuPad - Notepad written entirely in AutoIt V1.9.4

Feel free to use any of my code for your own use.                                                                                                                                                           Forum FAQ

 

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

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

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

@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 by JLogan3o13

√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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

√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites

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:

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

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
Sign in to follow this  
Followers 0