Incorporating an Excel table/range into a Gui

Post your working scripts, libraries and tools for AHK v1.1 and older
User avatar
flyingDman
Posts: 2846
Joined: 29 Sep 2013, 19:01

Incorporating an Excel table/range into a Gui

11 May 2022, 15:42

Just like I did here: viewtopic.php?f=6&t=103410&hilit=wrkbk.PublishObjects, you can incorporate one or more Excel tables or range(s) into a gui using a HTML representation of these ranges. Formatting (fonts, colors, borders) is not affected.

Code: Select all

P := 1, T := "", styles := "", ranges := ""  
xl            := ComObjActive("excel.application")
sht           := xl.Activesheet, wrkbk := xl.ActiveWorkbook
rng1 		  := "A1:N158", rng2 := "A158:I158"
for x,y in [rng1,rng2]													   		  	; include one or more ranges
	{
	wrkbk.PublishObjects.Add(4, A_Temp "\xl2html.htm", sht.name, y, 0).publish(1) 	; xlSourceRange=4 xlHtmlStatic=0
	fileread, html, % A_temp "\xl2html.htm"
	htmlarr   := strsplit(html,["<body>","</body>"])
	styles    .= htmlarr.1, ranges .= htmlarr.2 "<br>"
	}
While V       := xStr(styles,,"<style id","</style>",P,,,,0,0)  				  	; H,,B,E,BO,,,,BT,ET 
    T         .= V "`n"
style         := xStr(styles,,"<html","<![endif]-->",,,,,0,0) . T
html          := style "</head>`n`n<body>`n" . ranges . "</body></html>"
filedelete, % A_Temp "\xl2html.htm"

gui, margin, 0,0
gui, add, ActiveX, x0 y0 w1600 h600 vWB, HTMLfile
WB.write(html)
Gui, Show, , xl2gui
Return

xStr(ByRef H, C:=0, B:="", E:="",ByRef BO:=1, EO:="", BI:=1, EI:=1, BT:="", ET:="") {                           
Local L, LB, LE, P1, P2, Q, N:="", F:=0                 ; xStr v0.97 by SKAN on D1AL/D343 @ tiny.cc/xstr  
Return SubStr(H,!(ErrorLevel:=!((P1:=(L:=StrLen(H))?(LB:=StrLen(B))?(F:=InStr(H,B,C&1,BO,BI))?F+(BT=N?LB
:BT):0:(Q:=(BO=1&&BT>0?BT+1:BO>0?BO:L+BO))>1?Q:1:0)&&(P2:=P1?(LE:=StrLen(E))?(F:=InStr(H,E,C>>1,EO=N?(F
?F+LB:P1):EO,EI))?F+LE-(ET=N?LE:ET):0:EO=N?(ET>0?L-ET+1:L+1):P1+EO:0)>=P1))?P1:L+1,(BO:=Min(P2,L+1))-P1)  
}
This can be further developed. For instance, you an use this technique to make customized reports that can be printed to PDF using headless chrome (https://chromedevtools.github.io/devtools-protocol/tot/Page/#method-printToPDF).
14.3 & 1.3.7
burque505
Posts: 1736
Joined: 22 Jan 2017, 19:37

Re: Incorporating an Excel table/range into a Gui

11 May 2022, 17:13

@flyingDman, thank you, works great.
Regards,
burque505

Return to “Scripts and Functions (v1)”

Who is online

Users browsing this forum: No registered users and 97 guests