Show excel data in a GUI Topic is solved

Get help with using AutoHotkey (v1.1 and older) and its commands and hotkeys
Fulminare
Posts: 369
Joined: 26 Jun 2021, 20:15

Show excel data in a GUI

13 Oct 2021, 04:24

Hello!

I want the last row of every sheet to be shown in a gui.
I have managed to do this

Code: Select all

xl := ComObjCreate("excel.application")
wrkbk := xl.workbooks.open("C:\Users\user\Desktop\.xlsm")
XL.Sheets("Sheet1").Activate
wrkbk1.Sheets("Sheet1").Select


lstrw := xl.Range("B" xl.Rows.Count).End(-4162).Row
lstrw1 := xl.Range("A" xl.Rows.Count).End(-4162).Row
runs :=xl.Range("B" lstrw).text
name :=xl.Range("A" lstrw).text

Gui, Add, Progress, x50 y10 w280 h70
Gui, Add, Text, x1 y10 wp , %name%       %runs%
Gui Show
But I was able to do it only for one sheet. There are like 3 other sheets.

In the GUI I want the data shown in lines, one after the other.

1st sheet data in the 1st line
2nd sheet data in the 2nd line
e.t.c

Can someone please help by telling how to complete this code ?

Regards.
User avatar
AlphaBravo
Posts: 586
Joined: 29 Sep 2013, 22:59

Re: Show excel data in a GUI

13 Oct 2021, 14:44

I hope it helps

Code: Select all

xl := ComObjCreate("excel.application")
xl.workbooks.open("C:\Users\user\Desktop\.xlsm")
Gui, Add, Progress, x50 y10 w280 h70
for sh in xl.sheets
{
	lstrw := sh.Range("B" xl.Rows.Count).End(-4162).Row
	lstrw1 := sh.Range("A" xl.Rows.Count).End(-4162).Row
	runs :=sh.Range("B" lstrw).text
	name :=sh.Range("A" lstrw).text
	Gui, Add, Text, x1 y+10 wp , %name%       %runs%
}
Gui Show
sh := ""
xl.quit()
xl := ""
Fulminare
Posts: 369
Joined: 26 Jun 2021, 20:15

Re: Show excel data in a GUI

13 Oct 2021, 19:38

Hi ! Thank you :)

Yes it is working. But how to select the sheets ?

It is currently showing all the sheets.
User avatar
AlphaBravo
Posts: 586
Joined: 29 Sep 2013, 22:59

Re: Show excel data in a GUI

14 Oct 2021, 20:56

that's what you asked for!
Fulminare wrote:
13 Oct 2021, 04:24
1st sheet data in the 1st line
2nd sheet data in the 2nd line
e.t.c
but to answer your question
xl.sheets(1).activate activates 1st sheet
xl.sheets(2).activate activates 2nd sheet and so on
Fulminare
Posts: 369
Joined: 26 Jun 2021, 20:15

Re: Show excel data in a GUI

14 Oct 2021, 21:39

I know. But I realised it later

Thank you :)
Fulminare
Posts: 369
Joined: 26 Jun 2021, 20:15

Re: Show excel data in a GUI

14 Oct 2021, 21:43

A doubt :

why is y coordinate expressed as ( y+10 ) and not (y10) ?

Code: Select all

Gui, Add, Text, x1 y+10 wp , %name%       %runs%
User avatar
AlphaBravo
Posts: 586
Joined: 29 Sep 2013, 22:59

Re: Show excel data in a GUI

15 Oct 2021, 08:31

Fulminare wrote:
14 Oct 2021, 21:43
A doubt :

why is y coordinate expressed as ( y+10 ) and not (y10) ?

Code: Select all

Gui, Add, Text, x1 y+10 wp , %name%       %runs%
y+10 because it's in a "for" loop, it means add 10 pixels relative to previously added control if any, if you would to just use y10 every iteration will stack newly added text on top of each other at position y10
Fulminare
Posts: 369
Joined: 26 Jun 2021, 20:15

Re: Show excel data in a GUI

15 Oct 2021, 20:36

if you would to just use y10 every iteration will stack newly added text on top of each other at position y10
I see. So that's why it currently displays the data somewhat at the bottom of the GUI. When I tried using y10 It showed the data on the top, but only one sheet's entry was shown.(ref black rectangles in img)

Can you please tell me what must be done to get the data displayed at the red rectangles position ?
Attachments
Screenshot 2021-10-16 065600.png
Screenshot 2021-10-16 065600.png (4.88 KiB) Viewed 1459 times
User avatar
AlphaBravo
Posts: 586
Joined: 29 Sep 2013, 22:59

Re: Show excel data in a GUI

15 Oct 2021, 21:40

it is because you added a progress bar prior to adding text controls with a height of 70.

change Gui, Add, Text, x1 y+10 wp , %name% %runs% to:

Code: Select all

	if A_Index = 1
		Gui, Add, Text, x1 y10 wp , %name%       %runs%		; first iteration start at y10
	else
		Gui, Add, Text, x1 y+10 wp , %name%       %runs%	; subsequent iterations at y+10 relative to previously added control
Fulminare
Posts: 369
Joined: 26 Jun 2021, 20:15

Re: Show excel data in a GUI

15 Oct 2021, 22:59

Code: Select all

if A_Index = 1
		Gui, Add, Text, x1 y10 wp , %name%       %runs%	
That determines where the first entry should be displayed. the x and y pos

Code: Select all

else
		Gui, Add, Text, x1 y+10 wp , %name%       %runs%	; subsequent iterations at y+10 relative to previously added control
This determines where the next entries should be displayed with respect to the previous entry. I.e it adjusts the gap between entries

Did I understand it correctly ?
Last edited by Fulminare on 15 Oct 2021, 23:05, edited 1 time in total.
Fulminare
Posts: 369
Joined: 26 Jun 2021, 20:15

Re: Show excel data in a GUI

15 Oct 2021, 23:04

This is how my the code looks now

Code: Select all

xl := ComObjCreate("excel.application")
wrkbk := xl.workbooks.open("C:\Users\user\Desktop\Hi.xlsm")
Gui, Add, Progress, x50 y10 w280 h70
for sh in xl.sheets
	if (a_index > 2)
		;msgbox % sh.name
{
	lstrw := sh.Range("B" xl.Rows.Count).End(-4162).Row
	lstrw1 := sh.Range("A" xl.Rows.Count).End(-4162).Row
	runs :=sh.Range("B" lstrw).text
	name :=sh.Range("A" lstrw).text
		if a_Index = =1
		Gui, Add, Text, x1 y1 wp , %name%       %runs%		; first iteration start at y10
	else
		Gui, Add, Text, x1 y+10 wp , %name%       %runs%	; subsequent iterations at y+10 relative to previously added control
}

Gui Show
sh := ""
xl.quit()
xl := ""


1.
For selecting the sheets I have used

Code: Select all

for sh in xl.sheets
	if (a_index > 2)
That should not affect the text position in the GUI right ?

2.

Even after changing

Code: Select all

Gui, Add, Text, x1 y+10 wp , %name% %runs%
to

Code: Select all

if A_Index = 1
		Gui, Add, Text, x1 y10 wp , %name%       %runs%		; first iteration start at y10
	else
		Gui, Add, Text, x1 y+10 wp , %name%       %runs%	; subsequent iterations at y+10 relative to previously added control
I am still unable to get the text to be shown at the top part of the GUI. (it's still being shown like in the above image)
Fulminare
Posts: 369
Joined: 26 Jun 2021, 20:15

Re: Show excel data in a GUI

16 Oct 2021, 03:28

It's fine I think I understood

after I made the following changes, it is working as I want

Code: Select all

Gui, Add, Progress, x0 y0 w280 h0

Gui, Color, EEAA99
for sh in xl.sheets
	if (a_index > 2)
		;msgbox % sh.name
{
		if a_Index = =1
		Gui, Add, Text, x10 y1 wp , %name%       %runs%		; first iteration start at y10
	else
		Gui, Add, Text, x2 y+10 wp , %name%       %runs%	; subsequent iterations at y+10 relative to previously added control

}


Gui Show, h100

sh := ""
xl.quit()
xl := ""
User avatar
AlphaBravo
Posts: 586
Joined: 29 Sep 2013, 22:59

Re: Show excel data in a GUI  Topic is solved

16 Oct 2021, 08:59

see my comment below:

Code: Select all

xl := ComObjCreate("excel.application")
wrkbk := xl.workbooks.open("C:\Users\user\Desktop\Hi.xlsm")
Gui, Add, Progress, x50 y10 w280 h70
for sh in xl.sheets
	if (a_index > 2)
	{
		lstrw := sh.Range("B" xl.Rows.Count).End(-4162).Row
		lstrw1 := sh.Range("A" xl.Rows.Count).End(-4162).Row
		runs :=sh.Range("B" lstrw).text
		name :=sh.Range("A" lstrw).text
		if a_Index = 3  ; the first possible value after meeting the condition "if (a_index > 2)"
			Gui, Add, Text, x1 y1 wp , %name%       %runs%		; first iteration start at y10
		else
			Gui, Add, Text, x1 y+10 wp , %name%       %runs%	; subsequent iterations at y+10 relative to previously added control
	}

Gui Show
sh := ""
xl.quit()
xl := ""
Fulminare
Posts: 369
Joined: 26 Jun 2021, 20:15

Re: Show excel data in a GUI

16 Oct 2021, 09:13

Oh! I see

I tried a_index = 2 (like for testing)

It makes sense now :)

Thanks a lot !

Return to “Ask for Help (v1)”

Who is online

Users browsing this forum: Google [Bot], hiahkforum and 144 guests