AHK - Excel Current Date/Time 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

AHK - Excel Current Date/Time

30 Jul 2021, 10:03

I have tried a lot to do this by my own, but without much success

when an AHK script runs , I want a particular excel file to get filled with the following data :

Column A : current date
Column B : static text
Column C : current time

this data should get saved.

once the month changes, a gap of one or two rows must be maintained.

I request anyone to kindly guide me

Regards .
ahk7
Posts: 574
Joined: 06 Nov 2013, 16:35

Re: AHK - Excel Current Date/Time

30 Jul 2021, 10:15

As you can open/view/edit CSV in Excel directly as well I would simply use CSV and the FileAppend command
FileAppend, %A_Now%`, static text`, %A_Hour%:%A_Mim%:%A_Sec%, yourfile.csv
User avatar
flyingDman
Posts: 2791
Joined: 29 Sep 2013, 19:01

Re: AHK - Excel Current Date/Time  Topic is solved

30 Jul 2021, 13:50

Try something like this:

Code: Select all

xl := ComObjCreate("excel.application")
wrkbk := xl.workbooks.open("c:\users\" A_UserName "\documents\spreadsheets\abcd.xlsx")
lstrw := xl.Range("A" xl.Rows.Count).End(-4162).Row
mnth := strsplit(xl.Range("A" lstrw).value, "/").1
nrw := (mnth = a_mm) ? lstrw + 1 : lstrw + 3
inputbox, colbtxt, , enter column B text
xl.Range("A" nrw).NumberFormat := "yyyy-mm-dd;@"
xl.range("A" nrw).value := A_YYYY "-" a_mm "-" a_dd
xl.range("B" nrw).value := colbtxt
xl.Range("C" nrw).NumberFormat := "[$-en-US]h:mm:ss AM/PM;@"
xl.range("C" nrw).value := A_Hour ":" A_Min ":" A_Sec

wrkbk.close(1)
xl.quit()
14.3 & 1.3.7
Fulminare
Posts: 369
Joined: 26 Jun 2021, 20:15

Re: AHK - Excel Current Date/Time

30 Jul 2021, 20:30

@flyingDman

That's what I am getting ( as shown in the image attached )

how to include a static text into the script so that I need not type it every time when the msgbox pops up?

this is what I am trying to achieve :

DATE. EVENT. TIME.
31-Jul-2021 "static text" 7:43Pm

whenever the AHK scripts runs, that particular excel file should get updated with data in the aforementioned format
Attachments
excel image.png
excel image.png (76.2 KiB) Viewed 1282 times
User avatar
flyingDman
Posts: 2791
Joined: 29 Sep 2013, 19:01

Re: AHK - Excel Current Date/Time

30 Jul 2021, 21:21

Hardcode the column B text: colbtxt := "static text"

Re: date formatting, use dd-mmm-yyyy;@ and [$-en-US]h:mm Am/Pm;@
14.3 & 1.3.7
Fulminare
Posts: 369
Joined: 26 Jun 2021, 20:15

Re: AHK - Excel Current Date/Time

30 Jul 2021, 21:48

@ahk7

Thank you

I will try that as well
Fulminare
Posts: 369
Joined: 26 Jun 2021, 20:15

Re: AHK - Excel Current Date/Time

30 Jul 2021, 21:50

flyingDman wrote:
30 Jul 2021, 21:21
Hardcode the column B text: colbtxt := "static text"

Re: date formatting, use dd-mmm-yyyy;@ and [$-en-US]h:mm Am/Pm;@
@flyingDman

I am currently trying to do what you suggested

I will update you accordingly
Fulminare
Posts: 369
Joined: 26 Jun 2021, 20:15

Re: AHK - Excel Current Date/Time

31 Jul 2021, 00:33

@flyingDman

As shown in the image attached below,

"static text" and the time are getting entered, but the date is still showing up as "#########"

Also almost every second time I am getting a prompt to save a copy of the file

What should I do to make the date get displayed properly

and to avoid the " save a copy " prompt ?
Attachments
excel image. 2.png
excel image. 2.png (94.52 KiB) Viewed 1233 times
User avatar
flyingDman
Posts: 2791
Joined: 29 Sep 2013, 19:01

Re: AHK - Excel Current Date/Time

31 Jul 2021, 01:06

Make sure all instances of Excel are closed before launching the script (open task manager to make sure) and verify the xl.quit() is included in the script.

Adjust the width of your columns (or insert xl.Columns("A:C").AutoFit)
14.3 & 1.3.7
Fulminare
Posts: 369
Joined: 26 Jun 2021, 20:15

Re: AHK - Excel Current Date/Time

31 Jul 2021, 02:45

@flyingDman


Yes! its working well now

Can you please tell me how to adjust the gap between the cells ?

it will be very helpful if you could point out which part of the code determines the gap between the cells so that I can do any modifications myself
User avatar
flyingDman
Posts: 2791
Joined: 29 Sep 2013, 19:01

Re: AHK - Excel Current Date/Time

31 Jul 2021, 11:57

these 2 lines

Code: Select all

mnth := strsplit(xl.Range("A" lstrw).value, "/").1
nrw := (mnth = a_mm) ? lstrw + 1 : lstrw + 3
determine a) what the month is of the previous entry and b) if it is the same month as today's month, sets the interval to 1, and if not, to 3.
If your native format is m/d/yyyy (that is not the way the date is displayed but how it is stored in the spreadsheet), strsplit(xl.Range("A" lstrw).value, "/").1 will split that date using "/" as separator and choose the 1st element. If for whatever reason your settings are different, adjust the separator and/or the position of the month (in Europe, the position is likely 2 rather than 1).
14.3 & 1.3.7
User avatar
flyingDman
Posts: 2791
Joined: 29 Sep 2013, 19:01

Re: AHK - Excel Current Date/Time

31 Jul 2021, 12:14

From the image you posted, I also see that your are working on an Excel version that was not activated. This can create all kinds of issues.
14.3 & 1.3.7
Fulminare
Posts: 369
Joined: 26 Jun 2021, 20:15

Re: AHK - Excel Current Date/Time

31 Jul 2021, 22:22

flyingDman wrote:
31 Jul 2021, 11:57
these 2 lines

Code: Select all

mnth := strsplit(xl.Range("A" lstrw).value, "/").1
nrw := (mnth = a_mm) ? lstrw + 1 : lstrw + 3
determine a) what the month is of the previous entry and b) if it is the same month as today's month, sets the interval to 1, and if not, to 3.
If your native format is m/d/yyyy (that is not the way the date is displayed but how it is stored in the spreadsheet), strsplit(xl.Range("A" lstrw).value, "/").1 will split that date using "/" as separator and choose the 1st element. If for whatever reason your settings are different, adjust the separator and/or the position of the month (in Europe, the position is likely 2 rather than 1).
@flyingDman

so you are basically saying

lstrw + 1 will give a gap of one row if it is the same month

lstrw + 3 will give a gap of three rows if the month is not same as the previous month

i.e

Month-Jul

entry1
"gap of one row"
entry2

Month-Aug
"gap"
"gap"
"gap"
entry1 ( 1st entry in the month of aug)



But when I am running the script

irrespective of the month it is giving a gap of two rows between entries

i.e

Jul

entry1
"gap"
"gap"
entry2

Aug
"gap"
"gap"
entry1 ( 1st entry in the month of aug)


I ran the script today as it is Aug now

there was no change in the gap between entries

I then manually changed the system's month but still there was still no change


ps : yes this excel version is not activated

I have an activated version on another system but I want to do all the Ahk work here before sending the final script to the other system
Attachments
excel image. 3png.png
excel image. 3png.png (15.78 KiB) Viewed 1181 times
User avatar
flyingDman
Posts: 2791
Joined: 29 Sep 2013, 19:01

Re: AHK - Excel Current Date/Time

31 Jul 2021, 23:32

so you are basically saying
lstrw + 1 will give a gap of one row if it is the same month
lstrw + 3 will give a gap of three rows if the month is not same as the previous month
No lstrw + 1 is no gap (just next row) and lstrw + 3 is 2 row gap.
You need to find out what default format is for dates on your system. In the US is m/d/yyyy (unless changed by the user) and therefore strsplit(xl.Range("A" lstrw).value, "/").1 will result in m. As I said in previous post: "adjust the separator and/or the position of the month" to ypur circumstance. What is your default format? Find out! (msgbox % xl.range("A" lstrw).value).
That said it is very possible that because your system is not activated, this will not work as MS deactivates a random number of features when Excel is not activated (and I have lost more time with this issue than I want to admit!).

PS. It goes without saying that the script works fine here.
14.3 & 1.3.7
Fulminare
Posts: 369
Joined: 26 Jun 2021, 20:15

Re: AHK - Excel Current Date/Time

01 Aug 2021, 01:51

flyingDman wrote:
31 Jul 2021, 23:32
so you are basically saying
lstrw + 1 will give a gap of one row if it is the same month
lstrw + 3 will give a gap of three rows if the month is not same as the previous month
No lstrw + 1 is no gap (just next row) and lstrw + 3 is 2 row gap.
You need to find out what default format is for dates on your system. In the US is m/d/yyyy (unless changed by the user) and therefore strsplit(xl.Range("A" lstrw).value, "/").1 will result in m. As I said in previous post: "adjust the separator and/or the position of the month" to ypur circumstance. What is your default format? Find out! (msgbox % xl.range("A" lstrw).value).
That said it is very possible that because your system is not activated, this will not work as MS deactivates a random number of features when Excel is not activated (and I have lost more time with this issue than I want to admit!).

PS. It goes without saying that the script works fine here.

@flyingDman

I am so sorry for causing needless trouble

please bear with me

Based on what I understand :

A) I need to adjust the date format

B) I need an activated version of excel


now

the system uses the following format : DD-MMM-YY

so what should be changed in the code ?

once I get the format right I will try to run the script here ( on the un-activated version )
if I still don't get it , then I will try on my other system which has the activated version of excel



" If your native format is m/d/yyyy (that is not the way the date is displayed but how it is stored in the spreadsheet), strsplit(xl.Range("A" lstrw).value, "/").1 will split that date using "/" as separator and choose the 1st element. If for whatever reason your settings are different, adjust the separator and/or the position of the month (in Europe, the position is likely 2 rather than 1).'


you have explained that to me , but I did not understand
Attachments
Date format.png
Date format.png (1.78 KiB) Viewed 1140 times
Fulminare
Posts: 369
Joined: 26 Jun 2021, 20:15

Re: AHK - Excel Current Date/Time

01 Aug 2021, 07:21

@flyingDman

Update

I tried the following code on

2010 activated excel
2019 activated excel
2013 un-activated excel

Code: Select all

xl := ComObjCreate("excel.application")
wrkbk := xl.workbooks.open("C:\Users\user\Desktop\New Microsoft Excel Worksheet.xlsx")

lstrw := xl.Range("A" xl.Rows.Count).End(-4162).Row
mnth := strsplit(xl.Range("A" lstrw).value, "-").2

nrw := (mnth = A_MM) ? lstrw + 1 : lstrw + 3              




colbtxt := "static text"
xl.Range("A" nrw).NumberFormat := "dd-mmm-yyyy;@ "
xl.range("A" nrw).value := A_YYYY "-" a_mm "-" a_dd
xl.range("B" nrw).value := colbtxt
xl.Range("C" nrw).NumberFormat := "[$-en-US]h:mm Am/Pm;@"
xl.range("C" nrw).value := A_Hour ":" A_Min ":" A_Sec

wrkbk.close(1)
xl.quit()

In line 5 , I have changed "/" to "-"



It worked exactly as intended only on the 2010 activated excel

i.e no gap between entries in the same month

a gap of two rows when the month changes
Attachments
excel 5.jpeg
excel 5.jpeg (30.89 KiB) Viewed 1086 times

Return to “Ask for Help (v1)”

Who is online

Users browsing this forum: No registered users and 120 guests