Read excel file, get variables in memory Topic is solved

[euras]

There is an Excel file where is information in A column as a key and information in B column as a meaning of key in A column.
I want that autohotkey reads that Excel file and gets every rows as a variable in a memory.
If I put the key from A column in a Gui and click submit, the code will give me a meaning of that key from Excels file.
After I'm done and Close the script, the code will erase that variables from memory.
Is it possible to do that?

Code: Select all

Path := "C:\Users\Desktop\read.xlsx"
XL := ComObjCreate("Excel.Application")
XL_Workbook := XL.Workbooks.Open(Path)
XL.Visible := false

!x::
Gui, Add, Edit, w100 vcontent,
Gui, Add, Button, gsubmit, Submit
Gui, Show, autosize
return

submit:
Gui, submit,
msgbox, the meaning of %content% is %value%
return

[Nightwolf85]

You could do something like this:

Code: Select all

Path := "C:\Users\Desktop\read.xlsx"
XL := ComObjCreate("Excel.Application")
XL.Visible := False
XL_Workbook := XL.Workbooks.Open(Path)
XL_WorkSheet := XL_Workbook.Worksheets(1) ; I'm assuming worksheet 1 here because of the test file

LookUp := {} ; Object to hold lookups

Loop
{
	IF (XL_WorkSheet.Range("A" . A_Index).Value)
		LookUp[XL_WorkSheet.Range("A" . A_Index).Text] := XL_WorkSheet.Range("B" . A_Index).Text
	Else
		Break
}

XL.Quit()

Gui, Add, Edit, w100 vcontent,
Gui, Add, Button, gsubmit Default, Submit
!x::Gui, Show, autosize

submit:
Gui, submit,
msgbox, % "the meaning of " . content . " is " . LookUp[content]
Return

** Edited slightly to clean up code a bit.

[euras]

You could do something like this:

Code: Select all

Path := "C:\Users\Desktop\read.xlsx"
XL := ComObjCreate("Excel.Application")
XL.Visible := False
XL_Workbook := XL.Workbooks.Open(Path)
XL_WorkSheet := XL_Workbook.Worksheets(1) ; I'm assuming worksheet 1 here because of the test file

LookUp := {} ; Object to hold lookups

Loop
{
	IF (XL_WorkSheet.Range("A" . A_Index).Value)
		LookUp[XL_WorkSheet.Range("A" . A_Index).Text] := XL_WorkSheet.Range("B" . A_Index).Text
	Else
		Break
}

XL.Quit()

Gui, Add, Edit, w100 vcontent,
Gui, Add, Button, gsubmit Default, Submit
!x::Gui, Show, autosize

submit:
Gui, submit,
msgbox, % "the meaning of " . content . " is " . LookUp[content]
Return

** Edited slightly to clean up code a bit.

I have a problem With this one. first - the code doesn't Close the Excel file, I still can find it in taskbar. Second - gui, show, autosize doesn't show me the gui table. Loop function looks Nice, but it doesn't work properly :/
Edit: this one Works fine

Code: Select all

Path := "C:\Users\Desktop\read.xlsx"
XL := ComObjCreate("Excel.Application")
XL.Visible := False
XL_Workbook := XL.Workbooks.Open(Path)
XL_WorkSheet := XL_Workbook.Worksheets(1) ; I'm assuming worksheet 1 here because of the test file

LookUp := {} ; Object to hold lookups

Loop
{
	IF (XL_WorkSheet.Range("A" . A_Index).Value)
		LookUp[XL_WorkSheet.Range("A" . A_Index).Text] := XL_WorkSheet.Range("B" . A_Index).Text
	Else
		Break
}
Sleep 1000
XL.Quit

!x::
Gui, new
Gui, Add, Edit, w100 vcontent,
Gui, Add, Button, gsubmit Default, Submit
Gui, Show, autosize
return

submit:
Gui, submit,
msgbox, % "the meaning of " . content . " is " . LookUp[content]
Return

[Xeo786]

have you tired ComObjGet, in following example text transfer to active location from specified location.

Code: Select all

Path := A_Desktop "\read.xlsx"
XL := ComObjGet(Path)
XlApp := ComObjActive("Excel.Application")
XlApp.ActiveCell.Offset(0,0).value := XL.Worksheets(1).Range("a1").text 
return

[Nightwolf85]

It didn't close an already open file because it opened its own version hidden in the background, meaning you don't ever have to manually open the file.

Second - gui, show, autosize doesn't show me the gui table

I don't know what you mean by this, with my posted code the GUI shows up every time I pressed the HotKey.

Anyway, it sounds like you got it working, glad to here.

[euras]

It didn't close an already open file because it opened its own version hidden in the background, meaning you don't ever have to manually open the file.

Second - gui, show, autosize doesn't show me the gui table

I don't know what you mean by this, with my posted code the GUI shows up every time I pressed the HotKey.

Anyway, it sounds like you got it working, glad to here.

yes, I changed it a little bit and it Works fine now, many thanks Nightwolf85