Extract data from an Excel file into a combobox.

[chngrcn]

Hi.

From the attached excel file,

I want to pull data from Column A to combobox in gui.
If I choose which data from the Combobox, I would place the column "B" in the Excel file and the corresponding data in the "C" column in the editbox.

thank you for your help.

https://ufile.io/k1z6a

Code: Select all

#NoEnv
#SingleInstance Force
SetBatchLines -1
#Include ExcelToArray.ahk 	; <--- This file has to be in same folder as your script
							; or your Lib-directory in AutoHotKey install-path (C:\Program Files\AutoHotkey\Lib)
							; otherwise, you have to determine whole path

Gui, Font, s9

Gui, Add, ListView, grid -Multi +hscroll LV0x1 x22 y30 w680 h750 vLV1 gLV1 HWNDhLV, Fruits|Colour|Serial Number

Gui, Add, ComboBox, x+10 w110 h20 vFruits,
Gui, Add, Edit, y+10 w110 h20 vColour, 
Gui, Add, Edit, y+10 w110 h20 vSerialNumber,

Gui, Add, Button, y+30 w110 h40 gSelector, Select File
Gui, Add, Button, y+10 w110 h40 gGetImportData, Get File
Gui, Add, Button, y+10 w110 h40 gDeleteAllData, Delete List
Gui, Add, Button, y+10 w110 h40 gDelete, Delete
Gui, Add, Button, y+10 w110 h40 gRLoad, Reload


LvCtrl_SetWindowTheme(hLV)
LvCtrl_SetRowHeight(10)
;LvCtrl_SetRowHeight(80)
							
Loop, 7
	LV_ModifyCol(A_Index, 100)

gosub GetImportData

endItem := LV_GetCount() 
Gui, Font, cRed 
Gui, Add, Text, x20 y10 w210 h20, Active entries : %endItem%

Gui, Show,, GUİ

Return




LV1:
	


return




ImportData:
	SplashTextOn, , , Loading...
		arr := ExcelToArray(ExcelFile,,, 7)
		for i, dat in arr
			LV_Add("", dat*)
		arr := ""
	SplashTextOff
	gosub GuiSize
Return

GetImportData:
SplashTextOn, , , Loading...
	arr := ExcelToArray("test.xlsm")
    
	for i, dat in arr
		LV_Add("", dat*)
    	arr := ""
	SplashTextOff
	gosub GuiSize
Return

DeleteAllData:
MsgBox, 4 , Checking , Are you sure delete all data ?
IfMsgBox Yes
{
	LV_DELETE()
}
Return

Delete:
 RowNumber := LV_GetNext()
 If !RowNumber
  return
 Gui, +OwnDialogs
 Msgbox,4, DELETE, Are you sure delete data ?
 IfMsgBox, No
  return
 LV_GetText(ID,RowNumber)
 LV_Delete(RowNumber)
return

ButtonEdit:
Gui, Submit, NoHide
LV_Modify(RowNumber,"",Fruits,Colour,SerialNumber)
 GuiControl,     , Fruits, %Fruits%
 GuiControl,     , Colour, %Colour%
 GuiControl,     , SerialNumber, %SerialNumber%
 
return

RLoad:
Reload
return

MinimizeTray:  
   Critical
   Gui, Hide
   Menu, Tray, Icon
Return
   
RestoreTray: 
   Critical
   Menu, Tray, NoIcon
   Gui, Show
Return


GuiClose:
ExitApp

Selector:
	FileSelectFile, ExcelFile , , , , Excel Files (*.xls; *.xlsx)
	if ExcelFile
	{
		Gosub ImportData
		LV_MODIFY(1, "SELECT") ; preselect first row
	}
return


GuiSize:
LV_ModifyCol(2,0)
Loop,7
LV_ModifyCol(A_Index+1,"AutoHdr")
Return



LvCtrl_SetRowHeight(Height) {
	LV_SetImageList( DllCall( "ImageList_Create", Int,2, Int, Height, Int,0x18, Int,1, Int,1 ), 1 )
}

LvCtrl_SetWindowTheme(hLV) {
	DllCall("UxTheme.dll\SetWindowTheme", "Ptr", hLV, "WStr", "Explorer", "Ptr", 0)
	GuiControl, +LV0x14000, %hLV%
}

[divanebaba]

Hi chngrcn

Sorry my english is not the best yet, maybe I will give you now complete wrong direction.
Intention is counting, people say
Insert following code

Code: Select all

...
Gui, Add, ListView, AltSubmit grid -Multi +hscroll LV0x1 x22 y30 w680 h750 vLV1 gLV1 HWNDhLV, Fruits|Colour|Serial Number
...
LV1:
	LV1_lineNumber := LV_GetNext()	
	LV_GetText(Rama1, LV1_lineNumber, 1)
	LV_GetText(Rama2, LV1_lineNumber, 2)
	LV_GetText(Rama3, LV1_lineNumber, 3)
	GuiControl,, Fruits, %Rama1%||
	GuiControl,, Colour, %Rama2%
	GuiControl,, SerialNumber, %Rama3%
return
...

Nice greetings from fake-germany.