Jump to content
Sign in to follow this  
Jfish

Secret Santa Automation

Recommended Posts

Jfish

***

EDIT 11/29/14: I made a huge mistake in the original as it only created pairs (i.e. if I get you - you also get me).  What I really needed was for each person to get anyone but themselves - but not necessarily create a pair.  The issue then became that if there was one person left at the end they could not get themselves.

This new version (1.1).  Does several things:

1. It creates a unique match for everyone on the list without reciprocal pairs

2. Added: deletion of blank rows before processing

3. Added checking for the word "name" in the first cell to see if headers are included

4. Sample names file included

5. GUI slightly modified

***

 

Its that time of year again.  The holidays are upon us.  Therefore, I decided to automate the creation a "secret santa" list.   These lists are popular around the holidays but usually require at least some manual labor to pair people up.  This generator takes the work out of it.  Just feed it a list of names via an Excel spreadsheet with the names populated in the first column (no blanks or headers please).  It will then generate a new file with the pairings for each name on the list.  I even paid for a graphic with usage right that is included in the zip file along with the source and Koda GUI (modified for relative file location of the image).  Enjoy.

Just the code for those that don't want the whole file:

; Secret Santa Pairing Genertaor
; Use this application to randonmly pair people for secret Santa
; All names must be entered on first col of a spreadhsheet
; no headers - no blank rows
; JFish v 1.1
;********************************

#include<Excel.au3>
#include<Array.au3>
#include <ButtonConstants.au3>
#include <GUIConstantsEx.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>
#Region ### START Koda GUI section ### Form=C:\Users\Jayme\Documents\Code\secret santa\secretSnataGUI2.kxf
$Form1 = GUICreate("Secret Santa List Generator v 1.1", 533, 331, 201, 124)
GUISetBkColor(0xFFFFFF)
$Button1 = GUICtrlCreateButton("Select Excel File With Names", 144, 200, 233, 49)
GUICtrlSetFont(-1, 8, 800, 0, "MS Sans Serif")
GUICtrlSetBkColor(-1, 0xA6CAF0)
$Pic1 = GUICtrlCreatePic(@ScriptDir&"\400pxSecretSanata.bmp", 64, 8, 404, 172)
$Label1 = GUICtrlCreateLabel("Press the above button to select an Excel file containing names in Col A", 16, 264, 507, 24)
GUICtrlSetFont(-1, 12, 400, 0, "MS Sans Serif")
$Label2 = GUICtrlCreateLabel("The software will randomly produce Secret Santa pairs  and display then in a new file", 16, 293, 502, 20)
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###


func _createList()
; will be used to control the while loop - counts the number of pairs created
dim $pairs=0
; find a an Excel sheet containing names in Col 1
$path=FileOpenDialog("Select File With Secret Santa Names",@ScriptDir,"Excel (*.xl*)" )
    If @error Then
            ; Display the error message.
            MsgBox($MB_SYSTEMMODAL, "", "No file(s) were selected.")
    else
        ; open excel file from FileOpenDialog
        GLOBAL $excel=_Excel_Open()
        Local $oWorkbook = _Excel_BookOpen($excel, $path)
        ;delete all blank rows in Col A
        $oWorkbook.ActiveSheet.Columns("A:A").SpecialCells($xlCellTypeBlanks).EntireRow.Delete
        ; find last non blank row
        $LastRow = $oWorkbook.ActiveSheet.Range("A1").SpecialCells($xlCellTypeLastCell).Row
        ;find text in cell A1 to make sure it is not a header
        Local $aResult = _Excel_RangeRead($oWorkbook, Default, "A1")
        ;non-comprehensive data check to make sure the number of names is even for pairing and A1 does not contain header
        if mod($LastRow,2)<>0 or StringInStr($aResult,"name")<>0 then
            MsgBox("","Data Error","You need to have an even number of names and the file should not contain headers")
        else
            $namesArray = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange.Columns("A:A"))
            ; close that workbook
            _Excel_BookClose($oWorkbook)

            ; create new 2D array to store the pairings
            dim $pairingArray[ubound($namesArray)][3]

            ; populate first row of new array
            For $a=0 to UBound($pairingArray)-1
                $pairingArray[$a][0]=$namesArray[$a]
            Next

            ; this starts a loop that will run until everyone has a pairing
            while $pairs<(UBound($pairingArray)-1)

                ;this loop looks for a partner for each person on the list sequesntially
                for $names=0 to UBound($pairingArray)-1
                    ;does the name have anyone assigned to it? If not then ...
                    if $pairingArray[$names][1]=="" Then
                        ;generate random number
                        local $randomNumber=round(Random(0,ubound($pairingArray)-1),0)
                            ; creates to VARs for name on list and randomly generated name used for comparison
                            $originalName=$pairingArray[$pairs][0]
                            $randomName=$pairingArray[$randomNumber][0]
                            ;checks to ensure that if only one name is left on the list without a partner that you need
                            ;to erase everything and do it again ... this one drove me CRAZY
                            if $pairs==UBound($pairingArray)-2 AND $pairingArray[UBound($pairingArray)-1][1]=="" AND $pairingArray[UBound($pairingArray)-1][2]=="" Then
                                $pairs=0
                                for $deleteNames=0 to ubound($pairingArray)-1
                                    $pairingArray[$deleteNames][1]=""
                                    $pairingArray[$deleteNames][2]=""
                                next
                                ;ConsoleWrite(@crlf&"!!!DEBUG: NEW LOOP TRIGGEED !!!")
                                ExitLoop
                            EndIf
                            ; if the name random name is the same as the person you are matching OR the person you are
                            ; attempting to match has already been assigned then keep trying ...
                            if ($originalName==$randomName) OR ($pairingArray[$randomNumber][2]=="matched") then
                                do
                                $randomNumber=round(Random(0,ubound($pairingArray)-1),0)
                                until ($pairingArray[$pairs][0]<>$pairingArray[$randomNumber][0]) AND ($pairingArray[$randomNumber][2]<>"matched")
                            Endif
                            ; random person looks good so write it to the array
                            $pairingArray[$names][1]=$pairingArray[$randomNumber][0]
                            ; mark that person as matched so we don't pick them again
                            $pairingArray[$randomNumber][2]="matched"
                            ; increment the condition for the outer while loop
                            ; in this case keep going until we have the whole list
                            $pairs+=1
                    EndIf
                next
            WEnd
            ; paste it to a new results Excel spreadsheet
            GLOBAL $resultsWorkbook=_Excel_BookNew($excel)
            _Excel_RangeWrite($resultsWorkbook,$resultsWorkbook.Activesheet,$pairingArray,"A1")
        EndIf; this one is for the even number check
    EndIf
EndFunc


While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        case $Button1
            _createList()
        Case $GUI_EVENT_CLOSE
            Exit

    EndSwitch
WEnd

secretsanta1.1.zip

Edited by Jfish

Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt

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  

  • Similar Content

    • nooneclose
      By nooneclose
      I need to perform a subtotal in excel and I would like to automate this process using Autoit if possible like always any and all help will be greatly appreciated. 
      I can not find a good example but the two from Microsoft. Here is one of the two from msdn.microsoft.com/en-us/vba/excel-vba/articles/range-subtotal-method-excel
      I do not really understand how to translate this into AutoIt, but I gave it a try and here is what I have.
      $OpenRange      = "A1:E200" $xlSum          = -4157 $Added_Array[2] = [2, 3] $OpenRange.Subtotal("B1", $xlSum, $Added_Array, True, False, True) I just need to perform a subtotal on a range based on a header called department, and then perform a sum on the results.
    • nooneclose
      By nooneclose
      How to use _Excel_RangeSort to sort my excel file by three different headers Column A1, B1, and C1 have headers on which I want to sort by. The headers on which I want to sort are department, employee type, and name.
      I still really new to AutoIt so I do not actually know how to properly start this line or lines of code, to be honest. The example code is the best I can do.
      _Excel_RangeSort($OpenWorkbook, Default, "A1:C1", "1:1", $xlDescending, Default, $xlYes, Default, $xlSortRows) I just need to sort by those three headers in that order of department, employee type, and name, plus in descending order.
       
      any and all help would be greatly appreciated.  Thank you!
    • Daniza
      By Daniza
      It works fine on my Excel 2007, but after I Emailed My Area Manager he told me after he Enable Macro Security nothing happen's, can someone test this to run on Excel 2016? My AM can't provide me more information 

      Thank You in Advance,
      Please see attachment.
      <snip>
    • tuffgong
      By tuffgong
      Good morning. I have a system I am trying to automate that works like this: user fills a column in an Excel spreadsheet with values they would like printed and saves it to a folder on their desktop, they start the script and it formats their data into a text file (adding a prefix) and sends the text file as a .bch file where it needs to go. This is working:
      #include <Array.au3> #include <Excel.au3> #include <File.au3> #include <MsgBoxConstants.au3> Global $sSTCArray Global $sFilename = @DesktopDir & "\Labels\print.txt" Global $sWorkbook = @DesktopDir & "\Labels\Labels.xlsx" Global $oExcel = _Excel_Open(False,False,False,False,True) barcodePrint() Func barcodePrint() $Read = _Excel_BookOpen($oExcel, $sWorkbook, True, False, Default, Default, Default) FileOpen($sFilename, $FO_OVERWRITE) ;Global $oWorkbook = _Excel_BookAttach($oExcel) Global $sSTCArray = _Excel_RangeRead($Read) For $i = 0 to UBound($sSTCArray, 1) - 1 FileWriteLine($sFilename, "!StaticShelving1x3_ZPL," & $sSTCArray[$i]) Next _Excel_Close($oExcel) FileMove($sFilename, "***file path***\print.bch") EndFunc However, it only works if the user first formats the spreadsheet to text. I want to automate that. From what I have read it appears AutoIt does not like formatting cells that already have values. True? Can I pull the values from an un-formatted (default GENERAL format) spreadsheet and go straight to my text file? I have also considered opening a second spreadsheet, formatting it, and copying the values over. Like this:
        
      Global $sPrefix Global $oPath Global $sSTCArray Global $sFilename = @DesktopDir & "\Labels\print.txt" Global $sWorkbook = @DesktopDir & "\Labels\Labels.xlsx" Global $oExcel = _Excel_Open(False,False,False,False,True) Global $aArray Global $bExcel _Excel_BookNew($bExcel) While 1 $nMsg = GUIGetMsg() Switch $nMsg Case $GUI_EVENT_CLOSE Exit Case $Button1 $aArray = IniReadSection("***File path***\barcode.ini", "stc/rvt/mgm") $sPrefix = $aArray[1][1] $oPath = $aArray[2][1] barcodePrint() EndSwitch WEnd Func barcodePrint() $oWorkbook = _Excel_BookOpen($bExcel, @DesktopDir & "\Labels\print.xlsx") $oWorkbook.ActiveSheet.Columns("A").NumberFormat = "@" Local $Read = _Excel_BookOpen($oExcel, $sWorkbook, True, False, Default, Default, Default) $oCopy = _Excel_RangeRead($sWorkbook) _Excel_RangeWrite($oWorkbook, Default, $oCopy) FileOpen($sFilename, $FO_OVERWRITE) $sSTCArray = _Excel_RangeRead($Read,"Default","Default",3) ;_ArrayDisplay($sSTCArray) For $i = 0 to UBound($sSTCArray, 1) - 1 FileWriteLine($sFilename, $sPrefix & $sSTCArray[$i]) Next _Excel_Close($oExcel) ;FileMove($sFilename, $oPath) Exit EndFunc This does not like the formatting of $oWorkbook: "Variable must be of type 'Object'".  Do I need this second sheet? If so, how can I format it? Is there a better way to get the Excel values into a .txt file? Any ideas would be appreciated. Thanks!
    • Moonscarlet
      By Moonscarlet
      Hello,
      I am trying to keep only the filtered rows in an excel sheet but couldn't find a fast way to do it for a 1k+ rows file.
      Tried going through each row to see if it's hidden or not and if it is, delete it but it's not fast:
      Local $oExcel = _Excel_Open() Local $File = _Excel_BookOpen($oExcel, $Filepath) Local $LastRow = $File.Activesheet.Range("A1000000").End(-4162).Row _Excel_FilterSet($File, Default, Default, 1, "Test") For $j=1 To $LastRow If $File.Activesheet.Range("A"&$j).EntireRow.Hidden Then _Excel_RangeDelete($File.Activesheet,$j&":"&$j) Next I found a VBA macro that works great but I am having a hard time converting it to be able to use it in my script:
      Sub RemoveHiddenRows() Dim oRow As Range, rng As Range Dim myRows As Range Set myRows = Intersect(Sheets("Sheet1").Range("A:A").EntireRow, Sheets("Sheet1").UsedRange) For Each oRow In myRows.Columns(1).Cells If oRow.EntireRow.Hidden Then If rng Is Nothing Then Set rng = oRow Else Set rng = Union(rng, oRow) End If End If Next If Not rng Is Nothing Then rng.EntireRow.Delete End Sub I would really appreciate it if anyone can help me so I can use this part in my autoit script.
      Thanks.
×