Excel 2 GUI Topic is solved

Get help with using AutoHotkey (v1.1 and older) and its commands and hotkeys
Portwolf
Posts: 161
Joined: 08 Oct 2018, 12:57

Excel 2 GUI

21 Nov 2019, 06:45

Hi guys,

I've looked around but haven't found any answers.. Only found this topic that partially answers my question:
https://www.autohotkey.com/boards/viewtopic.php?f=76&t=64969&p=278966#p278966


I needed to gather data from an Excel file, a range, and display it on a GUI.
I've seen that i can use ListView, but how do i gather the range from a closed file from a specific location (on "c:\ test\test.xlsx" as an example)?
The topic above only shows how to do it if the file is open i think.

Thanks in advance!
AHKStudent
Posts: 1472
Joined: 05 May 2018, 12:23

Re: Excel 2 GUI

21 Nov 2019, 07:50

COM works on closed files too
Portwolf
Posts: 161
Joined: 08 Oct 2018, 12:57

Re: Excel 2 GUI

21 Nov 2019, 09:16

Hi, yeah, it does.

I am using this:

Code: Select all

#NoEnv  ; Recommended for performance and compatibility with future AutoHotkey releases.
; #Warn  ; Enable warnings to assist with detecting common errors.
SendMode Input  ; Recommended for new scripts due to its superior speed and reliability.
SetWorkingDir %A_ScriptDir%  ; Ensures a consistent starting directory.
#SingleInstance Force
#Persistent





ExcelRef = %A_ScriptDir%\ScheduleDump.xlsx

oTable := UsedRange2Table(ExcelRef)	; specify your path here

C1_R3 := % oTable.1.3
C2_R3 := % oTable.2.3
C3_R3 := % oTable.3.3
C4_R3 := % oTable.4.3
C5_R3 := % oTable.5.3
C6_R3 := % oTable.6.3
C7_R3 := % oTable.7.3
C8_R3 := % oTable.8.3
C9_R3 := % oTable.9.3
C10_R3 := % oTable.10.3
C11_R3 := % oTable.11.3
C12_R3 := % oTable.12.3
C13_R3 := % oTable.13.3
C14_R3 := % oTable.14.3
C15_R3 := % oTable.15.3
C16_R3 := % oTable.16.3
C17_R3 := % oTable.17.3
C18_R3 := % oTable.18.3
C19_R3 := % oTable.19.3
C20_R3 := % oTable.20.3
C21_R3 := % oTable.21.3
C22_R3 := % oTable.22.3
C23_R3 := % oTable.23.3
C24_R3 := % oTable.24.3
C25_R3 := % oTable.25.3
C26_R3 := % oTable.26.3
C27_R3 := % oTable.27.3
C28_R3 := % oTable.28.3
C29_R3 := % oTable.29.3
C30_R3 := % oTable.30.3
C31_R3 := % oTable.31.3
C32_R3 := % oTable.32.3
C33_R3 := % oTable.33.3
C34_R3 := % oTable.34.3
C35_R3 := % oTable.35.3
C36_R3 := % oTable.36.3
C37_R3 := % oTable.37.3
C38_R3 := % oTable.38.3


Gui, Add, ListView, r20 w700 grid HWNDHLV, Name / Month|*|*|*|*|*|*|*|*|*|*|*|*|*|*|*|*|*|*|*|*|*|*|*|*|*|*|*|*|*|*|*|*|*|*|*|*|*|*  ; 38 fields on collumn for all types of schedule
LV_Add("", C1_R3 , C2_R3, C3_R3, C4_R3)
LV_Add("", "rock","mik")

gui,show



return



;===Functions===========================================================================
SafeArrayMod(oRange) {   ; by Learning one, modified, original here: http://www.autohotkey.com/forum/topic61509-90.html&p=415035#415035
	oTable := [], SafeArray := oRange.value
	if  (SafeArray.MaxIndex(1) = "")
		oTable.Insert([SafeArray])
	else
	{
		Loop % SafeArray.MaxIndex(2)
		{
			ColumnNum := A_Index
			%ColumnNum% := []
			Loop % SafeArray.MaxIndex(1)
			%ColumnNum%.Insert(SafeArray[A_Index, ColumnNum])
			oTable.Insert(%ColumnNum%)
		}
	}
	return oTable
}

UsedRange2Table(FilePath, Worksheet="Drop") {   ; by Learning one on azure's request...
	oWorkbook := ComObjGet(FilePath), UsedRange := oWorkbook.Worksheets(Worksheet).UsedRange.Address
	StringReplace, UsedRange, UsedRange, $,,All 
	return SafeArrayMod(oWorkbook.Worksheets(Worksheet).Range(UsedRange))
}


;===Hotkeys=============================================================================
Esc::ExitApp

Surelly there must exist an way to do it better..
In this code, i read manually each field of the table, and assign a var to it.
It's a 38 collumn, 16 row table, so it will be really painfull to do it this way.
Odlanir
Posts: 659
Joined: 20 Oct 2016, 08:20

Re: Excel 2 GUI  Topic is solved

21 Nov 2019, 09:43

You can try this:

Code: Select all

header          := false, hdr := ""
saveClipboard   := ClipboardAll
file2open       := A_ScriptDir "\xxxxxxxxxxxxxxxxxxxx.xlsx" ; put here the path of your excel file
xl              := ComObjCreate("Excel.Application")
Wrkbk           := xl.Workbooks.Open(file2open) 	

Loop % xl.ActiveSheet.UsedRange.Columns.count {
    hdr .= hdr ? "|" a_index : a_index
}
xl.ActiveSheet.UsedRange.copy
ClipWait, 1
data            := SubStr(clipboard,1,-2)
clipboard       := saveClipboard
Wrkbk.Close(0)
Gui,-DPIScale
loop,parse,data, `r,`n  
{
    If (A_Index = 1 ) {
        If (header)
            Gui, Add, ListView, x10 y10 r20 w300 vLV1 hdr grid hwndLV, % RegExReplace(a_loopfield, "`t", "|")
        else {
            Gui, Add, ListView, x10 y10 r20 w300 vLV1 hdr grid hwndLV, % hdr
            LV_Add("", StrSplit(a_loopfield, a_tab)*)
        }
    }
    else			
        LV_Add("", StrSplit(a_loopfield, a_tab)*)
}
LV_ModifyCol(,"AutoHdr")
Gui, +Resize
VarSetCapacity(RECT, 16, 0)
SendMessage, LVM_GETITEMRECT := 0x100E, 0, &RECT, , ahk_id %LV%
GuiControl, Move, LV1, % "w"  NumGet(RECT, 8, "Int")+22*(A_ScreenDPI/96) ; SM_CXBORDER (1*2) + SM_CXVSCROLL (17) + SM_CXFIXEDFRAME(3)
Gui,show,AutoSize
return

GuiEscape:
GuiClose:
Esc::
  ExitApp
return
____________________________________________________________________________
Windows 10 Pro 64 bit - Autohotkey v1.1.30.01 64-bit Unicode
Portwolf
Posts: 161
Joined: 08 Oct 2018, 12:57

Re: Excel 2 GUI

21 Nov 2019, 10:09

Hi Rinaldo :D

That is perfect!! Thanks so much. I could never gotten there :D
Only one question, after i ran the code, now workbook says it is locked, even after closing GUI.
Odlanir
Posts: 659
Joined: 20 Oct 2016, 08:20

Re: Excel 2 GUI

21 Nov 2019, 10:43

Try adding xl.Quit() after Wrkbk.Close(0)
____________________________________________________________________________
Windows 10 Pro 64 bit - Autohotkey v1.1.30.01 64-bit Unicode
Portwolf
Posts: 161
Joined: 08 Oct 2018, 12:57

Re: Excel 2 GUI

22 Nov 2019, 04:08

Thanks for the update, i was checking it again and the object close is on the code you provided earlier.
Apparently i tried to open the Excel while AHK was still open, and that was the issue..
As we call it at work, it was a "layer 8 mistake" lololol :D

Thanks mate!

Also, sorry for spamming you with questions, but:
1) Is there a way to highlight the column instead of the row when clicked?
2) Is there a way to change BG color on specific row?
Example:

I am using this Excel:
Image


And i wanted to change the background color only on these rows (days)
Image
Odlanir
Posts: 659
Joined: 20 Oct 2016, 08:20

Re: Excel 2 GUI

23 Nov 2019, 12:33

Portwolf wrote:
22 Nov 2019, 04:08
...
1) Is there a way to highlight the column instead of the row when clicked?
2) Is there a way to change BG color on specific row?
...
1) too much work ( at least for my knowledge )
2) you can use the Class_LV_Colors by justme.
To use the class with my script you must put this in top of the script:

Code: Select all

#Include Class_LV_Colors.ahk ; or put the class inside the script
and put this between the Gui,show,AutoSize and the return

Code: Select all

red := 0xFF0000, white := 0xFFFFFF 
CLV := New LV_Colors(LV)
If !IsObject(CLV) {
   MsgBox, 0, ERROR, Couldn't create a new LV_Colors object!
   ExitApp
}
Loop, % LV_GetCount()
{
   LV_GetText(RetrievedText, A_Index)
   if RegExMatch(RetrievedText, "Name/Date")
      CLV.Row(A_Index, red, white)	; red is background, white is foreground 
}
____________________________________________________________________________
Windows 10 Pro 64 bit - Autohotkey v1.1.30.01 64-bit Unicode
Portwolf
Posts: 161
Joined: 08 Oct 2018, 12:57

Re: Excel 2 GUI

04 Dec 2019, 14:28

Hi! Sorry, was away on work.

Thank, i will try it now :)
Will give feedback as soon as possible :)
Portwolf
Posts: 161
Joined: 08 Oct 2018, 12:57

Re: Excel 2 GUI

04 Dec 2019, 15:44

Adapted and working!!
Thanks mate!

Image
nik_the_giant
Posts: 62
Joined: 01 May 2022, 03:26

Re: Excel 2 GUI

10 May 2022, 06:40

how could you extend the script with conditional formatting so that only certain cells are colored? :roll:

Return to “Ask for Help (v1)”

Who is online

Users browsing this forum: anogoya, Descolada, Google [Bot], Mannaia666, skeerrt and 152 guests