GUI ComboBox dropdowns based on Excel columns

[Nivol]

Hello,

I have a gui with multiple comboboxes.

I want each dropdown of those comboboxes to be based a corresponding column of an excel file. This would allow people to just add to the excel file instead of opening the script.

My current code basically looks like this

Code: Select all

F11:
Gui, Show , w1000 h30, The Gui ;its long on purpose
Gui, font, cBlack s11 Bold, Calibri

Gui, Add, Text,x5 y0, Item:
Gui, Add, ComboBox,vitem x5 y15 h20 w120, spiders|ants|grasshoppers|

Gui, Add, Text,x126 y0, Quantity:
Gui, Add, ComboBox,vQuantity x126 y15 h20 w32, 5|10|15|30|50|

Gui, Add, Text,x159 y0, ID:
Gui, Add, ComboBox, vID x159 y15 h20 w105, 123|456|789|

Gui, Add, Button, Default gRUN x1090 y0 h18 w55, The Gui

return

RUN:
Gui, submit, NoHide
(The rest of the scipt does some data validation and put whats inputted in the GUI into fields of a webpage)
return

[Mod edit: [code][/code] tags added.]

I searched online and found this post:

https://autohotkey.com/board/topic/114241-dropdown-list-from-excel-cells/

I figure that after the F11 hotkey i am using I will add:

Code: Select all

file2open := "C:\Users\xxxx\Desktop\yyyy.xlsx"
xl := ComObjCreate("Excel.Application")                                               ;create a handle to a new excel application
Wrkbk := xl.Workbooks.Open(file2open)                                                     ;handle to specific workbook
xl.ActiveSheet.UsedRange.copy
data := clipboard
clipboard =
Wrkbk.Close(0)                                                ;closes and does not save
xl.Quit()
StringTrimRight, data, data, 2                                                                      ;this removes the final `r`n

However the rest is a bit too complex for me to understand and try to adapt into my current gui. Help on how to do this would be great.

[mikeyww]

Code: Select all

Gui, Font, s10
file := A_ScriptDir "\test.xlsx", cb := [], Clipboard := "", cols := 0, 
xl := ComObjCreate("Excel.Application"), Wrkbk := xl.Workbooks.Open(file), xl.ActiveSheet.UsedRange.copy
ClipWait, 0
If ErrorLevel {
 MsgBox, 48, Error, An error occurred while waiting for the clipboard. Aborting.
 Return
}
For row, line in StrSplit(Trim(Clipboard, "`r`n"), "`n")
 For col, item in StrSplit(line, "`t")
  cb[col] .= item (row = 1 ? "||" : "|"), cols += row = 1
Loop, %cols%
 Gui, Add, ComboBox, w275 vitem%A_Index%, % cb[A_Index]
Wrkbk.Close(0), xl.Quit()
GuiControl, Focus, item1
Gosub, F11

F11::Gui, Show, w300, Items

GuiEscape:
GuiClose:
Gui, Hide
Return