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!
Excel 2 GUI Topic is solved
-
- Posts: 1472
- Joined: 05 May 2018, 12:23
Re: Excel 2 GUI
COM works on closed files too
Re: Excel 2 GUI
Hi, yeah, it does.
I am using this:
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.
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
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.
Re: Excel 2 GUI Topic is solved
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
Windows 10 Pro 64 bit - Autohotkey v1.1.30.01 64-bit Unicode
Re: Excel 2 GUI
Hi Rinaldo
That is perfect!! Thanks so much. I could never gotten there
Only one question, after i ran the code, now workbook says it is locked, even after closing GUI.
That is perfect!! Thanks so much. I could never gotten there
Only one question, after i ran the code, now workbook says it is locked, even after closing GUI.
Re: Excel 2 GUI
Try adding xl.Quit() after Wrkbk.Close(0)
____________________________________________________________________________
Windows 10 Pro 64 bit - Autohotkey v1.1.30.01 64-bit Unicode
Windows 10 Pro 64 bit - Autohotkey v1.1.30.01 64-bit Unicode
Re: Excel 2 GUI
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
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:
And i wanted to change the background color only on these rows (days)
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
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:
And i wanted to change the background color only on these rows (days)
Re: Excel 2 GUI
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
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
Windows 10 Pro 64 bit - Autohotkey v1.1.30.01 64-bit Unicode
Re: Excel 2 GUI
Hi! Sorry, was away on work.
Thank, i will try it now
Will give feedback as soon as possible
Thank, i will try it now
Will give feedback as soon as possible
Re: Excel 2 GUI
Adapted and working!!
Thanks mate!
Thanks mate!
-
- Posts: 62
- Joined: 01 May 2022, 03:26
Re: Excel 2 GUI
how could you extend the script with conditional formatting so that only certain cells are colored?
Who is online
Users browsing this forum: anogoya, Descolada, Google [Bot], Mannaia666, skeerrt and 152 guests