Birthday Excel to GUI Topic is solved

[nik_the_giant]

My plan is to display Dates from an Excel file in Gui.
Means I want to filter the months individually and display them in a GUI.
It should show the whole month
And also from today until a week from now...

I can currently display the months individually in MSG and where they are but only need the 1st and last value


This is the code I've made with the help of the forum so far:

Code: Select all

XL := ComObjActive("Excel.Application")



Today := A_now
Week  += 7, d
Month += 30, d
FormatTime, Datum ,%Today%, dd.MM
FormatTime, 7Days ,%Week%, dd.MM
FormatTime, 30Days ,%Month%, dd.mm

;msgbox %Datum%



for cell in xlFindAll("05")
	;MsgBox % cell.address
	MsgBox % cell.Value

xlFindAll(needle, rng := "", xl := "")
{
	If !IsObject(xl)
		xl := ComObjActive("Excel.Application")
	if !IsObject(rng)
		if (rng = "")
			rng := xl.ActiveSheet.UsedRange.Cells
		else
			rng := xl.Range(rng)
	if (found := rng.Find(needle, rng.Cells(rng.Cells.Count)))
	{
		result := found, address := found.Address
		Loop
		{
			found := rng.FindNext(found)
			if (found.Address = address)
				break
			result := xl.Union(result, found)
		}
	}
	return result
}]

[Mod edit: Fixed code tags.]

[mikeyww]

Code: Select all

For cell in xlFindAll("05") {
 part := StrSplit(cell.Value, "/"), (part.3 < 1000) && part.3 += 2000
 If ("MM/dd/yyyy" = dateFormat := StrReplace(cell.NumberFormat, "mm", "MM")) ; Adjust for other formats
      date := Format("{}{:02}{:02}", part.3, part.1, part.2)
 Else date := Format("{}{:02}{:02}", part.3, part.2, part.1)
 (date < first || !first) && first := date
 (date > last           ) && last  := date
}
FormatTime, first, %first%, %dateFormat%
FormatTime, last , %last% , %dateFormat%
MsgBox, 64, Values, First = %first%`n`nLast = %last%

[nik_the_giant]

thanks for the fast reply.
i tried it but get an error message---------------------------

---------------------------
Error: Call to nonexistent function.

Specifically: xlFindAll("05")

Line#
---> 002: For cell, in xlFindAll("05")

[mikeyww]

I provided the part that you would replace in your original script.

Code: Select all

For cell in xlFindAll("05") {
 part := StrSplit(cell.Value, "/"), (part.3 < 1000) && part.3 += 2000
 If ("MM/dd/yyyy" = dateFormat := StrReplace(cell.NumberFormat, "mm", "MM")) ; Adjust for other formats
      date := Format("{}{:02}{:02}", part.3, part.1, part.2)
 Else date := Format("{}{:02}{:02}", part.3, part.2, part.1)
 (date < first || !first) && first := date
 (date > last           ) && last  := date
}
FormatTime, first, %first%, %dateFormat%
FormatTime, last , %last% , %dateFormat%
MsgBox, 64, Values, First = %first%`n`nLast = %last%

xlFindAll(needle, rng := "", xl := "") {
 (!IsObject(xl))  && xl  := ComObjActive("Excel.Application")
 (!IsObject(rng)) && rng := rng = "" ? xl.ActiveSheet.UsedRange.Cells : xl.Range(rng)
 If !found := rng.Find(needle, rng.Cells(rng.Cells.Count))
  Return
 result := found, address := found.Address
 While (address != (found := rng.FindNext(found)).Address)
  result := xl.Union(result, found)
 Return result
}

[nik_the_giant]

I've done it but i get only Empty values back.

That's what i do but i geht only an MGSBOX with First= and Last=

This is in my Excel where i prove it in Column A
17.05.2022
18.05.2022
19.05.2022
20.05.2022
21.05.2022
22.05.2022
23.05.2022
24.05.2022
25.05.2022
26.05.2022
27.05.2022
28.05.2022
29.05.2022
30.05.2022
31.05.2022
01.06.2022
02.06.2022
03.06.2022
04.06.2022
05.06.2022
06.06.2022
07.06.2022
08.06.2022
09.06.2022
10.06.2022
11.06.2022
12.06.2022
13.06.2022
14.06.2022
15.06.2022
16.06.2022
17.06.2022

Code: Select all

XL := ComObjActive("Excel.Application")



Today := A_now
Week  += 7, d
Month += 30, d
FormatTime, Datum ,%Today%, dd.MM
FormatTime, 7Days ,%Week%, dd.MM
FormatTime, 30Days ,%Month%, dd.MM

;msgbox %Datum%



For cell in xlFindAll("05") {
 part := StrSplit(cell.Value, "/"), (part.3 < 1000) && part.3 += 2000
 If ("dd.MM.yyyy" = dateFormat := StrReplace(cell.NumberFormat, "mm", "MM")) ; Adjust for other formats
      date := Format("{}{:02}{:02}", part.3, part.1, part.2)
 Else date := Format("{}{:02}{:02}", part.3, part.2, part.1)
 (date < first || !first) && first := date
 (date > last           ) && last  := date
}
FormatTime, first, %first%, %dateFormat%
FormatTime, last , %last% , %dateFormat%
MsgBox, 64, Values, First = %first%`n`nLast = %last%

xlFindAll(needle, rng := "", xl := "") {
 (!IsObject(xl))  && xl  := ComObjActive("Excel.Application")
 (!IsObject(rng)) && rng := rng = "" ? xl.ActiveSheet.UsedRange.Cells : xl.Range(rng)
 If !found := rng.Find(needle, rng.Cells(rng.Cells.Count))
  Return
 result := found, address := found.Address
 Loop {
  found := rng.FindNext(found)
  If (found.Address = address)
   Break
  result := xl.Union(result, found)
 }
 Return result
}

[mikeyww]

Without posting your Excel file, you simply leave the reader guessing here.

Code: Select all

For cell in xlFindAll("05") {
 part := StrSplit(cell.Value, "."), date := part.3 part.2 part.1
 (date < first || !first) && first := date
 (date > last           ) && last  := date
}
FormatTime, first, %first%, % dateFormat := "dd.MM.yyyy"
FormatTime, last , %last% , % dateFormat
MsgBox, 64, Values, First = %first%`n`nLast = %last%

xlFindAll(needle, rng := "", xl := "") {
 (!IsObject(xl))  && xl  := ComObjActive("Excel.Application")
 (!IsObject(rng)) && rng := rng = "" ? xl.ActiveSheet.UsedRange.Cells : xl.Range(rng)
 If !found := rng.Find(needle, rng.Cells(rng.Cells.Count))
  Return
 result := found, address := found.Address
 While (address != (found := rng.FindNext(found)).Address)
  result := xl.Union(result, found)
 Return result
}

[nik_the_giant]

I describe it here

I've done it but i get only Empty values back.

That's what i do but i geht only an MGSBOX with First= and Last=

This is in my Excel where i prove it in Column A
17.05.2022
18.05.2022
19.05.2022
20.05.2022
21.05.2022
22.05.2022
23.05.2022
24.05.2022
25.05.2022
26.05.2022
27.05.2022
28.05.2022
29.05.2022
30.05.2022
31.05.2022
01.06.2022
02.06.2022
03.06.2022
04.06.2022
05.06.2022
06.06.2022
07.06.2022
08.06.2022
09.06.2022
10.06.2022
11.06.2022
12.06.2022
13.06.2022
14.06.2022
15.06.2022
16.06.2022
17.06.2022

Code: Select all

XL := ComObjActive("Excel.Application")



Today := A_now
Week  += 7, d
Month += 30, d
FormatTime, Datum ,%Today%, dd.MM
FormatTime, 7Days ,%Week%, dd.MM
FormatTime, 30Days ,%Month%, dd.MM

;msgbox %Datum%



For cell in xlFindAll("05") {
 part := StrSplit(cell.Value, "/"), (part.3 < 1000) && part.3 += 2000
 If ("dd.MM.yyyy" = dateFormat := StrReplace(cell.NumberFormat, "mm", "MM")) ; Adjust for other formats
      date := Format("{}{:02}{:02}", part.3, part.1, part.2)
 Else date := Format("{}{:02}{:02}", part.3, part.2, part.1)
 (date < first || !first) && first := date
 (date > last           ) && last  := date
}
FormatTime, first, %first%, %dateFormat%
FormatTime, last , %last% , %dateFormat%
MsgBox, 64, Values, First = %first%`n`nLast = %last%

xlFindAll(needle, rng := "", xl := "") {
 (!IsObject(xl))  && xl  := ComObjActive("Excel.Application")
 (!IsObject(rng)) && rng := rng = "" ? xl.ActiveSheet.UsedRange.Cells : xl.Range(rng)
 If !found := rng.Find(needle, rng.Cells(rng.Cells.Count))
  Return
 result := found, address := found.Address
 Loop {
  found := rng.FindNext(found)
  If (found.Address = address)
   Break
  result := xl.Union(result, found)
 }
 Return result
}

[flyingDman]

@mikeyww script works as intended though the use of xlFindAll("05") is flawed here as it will get the dates with month = 05 but also days = 05 (05.06.2022) and years with 05 (2005). That function looks anywhere in the cell content.

[mikeyww]

Yep, that's how someone wrote it! I was not trying to guess the purpose.... Thanks.

[flyingDman]

Good function. Wrong choice of function...
That said, findnext is a dinosaur

[nik_the_giant]

Yeah, it works. THANKS and sorry
what would be an alternative for the "05" problem?

Edit//

I made a point before 05 and that works for now

[flyingDman]

That might be an easy fix. The other way would be to toss the function and check if part.2 = "05"

[nik_the_giant]

Okay, i try

how can i change the code that i can see the place where the date is located?

I'm an absolute beginner on the subject

[flyingDman]

I'm an absolute beginner on the subject

At one point we all are...
Try this just to return a list of the dates in May:

Code: Select all

xl := ComObjActive("excel.application")
for c in xl.activesheet.usedrange.columns(1).cells                    ; assumes date list is in col A
	if (strsplit(c.value,".").2 = "05")
		lst .= c.value "`n"
msgbox % lst

[nik_the_giant]

Perfect!
I changed "Value" to "address" in line 4 and now I also have the position.

Thank you!

[flyingDman]

[nik_the_giant]

It works so far everything I want
I get the lines displayed in which the date is
The date is in column C and I would like when the date is displayed to me that rows A and B are also displayed

A | B | C
Name| Name |Date


I would like to see the names in front of it too

how is it possible...?

Code: Select all

;===============================================================================================	Libary
#Include C:\Users\****\OneDrive\Dokumente\AUTOHOTKEY\lib\Gdip_All.ahk
Xl := ComObjCreate("excel.application")
Path := "test.xlsx"
wrkbk := xl.Workbooks.Open(path) 
;===============================================================================================	Filter
For cell in xlFindAll(".01") {
 part := StrSplit(cell.Address, "."), date := part.3 part.2 part.1
 (date < first || !first) && first := date
 (date > last           ) && last  := date
}

;MsgBox, 64, Values, First = %first%
;MsgBox, 64, Values, Last = %last%

xlFindAll(needle, rng := "C:C", xl := "") {	;Suchbereich angeben
 (!IsObject(xl))  && xl  := ComObjActive("Excel.Application")
 (!IsObject(rng)) && rng := rng = "" ? xl.ActiveSheet.UsedRange.Cells : xl.Range(rng)
 If !found := rng.Find(needle, rng.Cells(rng.Cells.Count))
  Return
 result := found, address := found.Address
 While (address != (found := rng.FindNext(found)).Address)
  result := xl.Union(result, found)
 Return result
}

;msgbox %First%
;===============================================================================================	Position festlegen
DP			:=	":"
Erster_Tag	:=	first
Letzter_Tag:=	last
Geburtstage	:=	Erster_Tag DP Letzter_Tag


;===============================================================================================	Excel darstellen


xl.activesheet.Range(Geburtstage).CopyPicture(1,2)                  ;xlScreen = 1 ,  xlBitmap = 2
pToken 		:= Gdip_Startup()
pBitmap 	:= Gdip_CreateBitmapFromClipboard()
hBitmap 	:= Gdip_CreatehBitmapFromBitmap(pBitmap)
Gdip_GetImageDimensions(pBitmap, w, h)





;===============================================================================================	GUI Einstellungen
Gui, -Caption
Gui, margin, 0,0					
Gui, Add, Pic, x0 y0 w%w% h%h% vPic, % "hBitmap:*" hBitmap

Gui, Show, , Geburtstage
Return






;===============================================================================================	Beenden
GUIEscape:
guiclose:
Gdip_DisposeImage(pBitmap)
DeleteObject(hBitmap)
Gdip_Shutdown(pToken)
wrkbk.Close(0)
xl.quit()
exitapp
return

[flyingDman]

To search in column C and retrieve the content of col A, B and C:

Code: Select all

xl := ComObjActive("excel.application")
for c in xl.activesheet.usedrange.columns(3).cells
	if (strsplit(c.text,".").2 = "05")
		lst .= c.offset(0,-2).text " " c.offset(0,-1).text " " c.text "`n"
msgbox % lst

[nik_the_giant]

Thats good but i need the answer like in my Skript
The answer must be the first and the last of this date.
Only the first and the last
You know?

To search in column C and retrieve the content of col A, B and C:

Code: Select all

xl := ComObjActive("excel.application")
for c in xl.activesheet.usedrange.columns(3).cells
	if (strsplit(c.text,".").2 = "05")
		lst .= c.offset(0,-2).text " " c.offset(0,-1).text " " c.text "`n"
msgbox % lst

[flyingDman]

You already had the technique to find first and last date from @mikeyww's script above. You have to put the elements together:

Code: Select all

xl := ComObjActive("excel.application")
for c in xl.activesheet.usedrange.columns(3).cells
	{
	part := strsplit(c.text,".")
	if (part.2 = "05")
		{
		date := part.3 part.2 part.1
		(date < first || !first) && (first := date, res1 := c.offset(0,-2).text " " c.offset(0,-1).text " " c.text)
		(date > last           ) && (last  := date, res2 := c.offset(0,-2).text " " c.offset(0,-1).text " " c.text)
		}
	}
msgbox % res1 "`n`n" res2