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 .
AHK - Excel Current Date/Time Topic is solved
Re: AHK - Excel Current Date/Time
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
FileAppend, %A_Now%`, static text`, %A_Hour%:%A_Mim%:%A_Sec%, yourfile.csv
- flyingDman
- Posts: 2817
- Joined: 29 Sep 2013, 19:01
Re: AHK - Excel Current Date/Time Topic is solved
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
Re: AHK - Excel Current Date/Time
@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
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 (76.2 KiB) Viewed 1298 times
- flyingDman
- Posts: 2817
- Joined: 29 Sep 2013, 19:01
Re: AHK - Excel Current Date/Time
Hardcode the column B text: colbtxt := "static text"
Re: date formatting, use dd-mmm-yyyy;@ and [$-en-US]h:mm Am/Pm;@
Re: date formatting, use dd-mmm-yyyy;@ and [$-en-US]h:mm Am/Pm;@
14.3 & 1.3.7
Re: AHK - Excel Current Date/Time
@ahk7
Thank you
I will try that as well
Thank you
I will try that as well
Re: AHK - Excel Current Date/Time
@flyingDmanflyingDman wrote: ↑30 Jul 2021, 21:21Hardcode the column B text: colbtxt := "static text"
Re: date formatting, use dd-mmm-yyyy;@ and [$-en-US]h:mm Am/Pm;@
I am currently trying to do what you suggested
I will update you accordingly
Re: AHK - Excel Current Date/Time
@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 ?
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 (94.52 KiB) Viewed 1249 times
- flyingDman
- Posts: 2817
- Joined: 29 Sep 2013, 19:01
Re: AHK - Excel Current Date/Time
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)
Adjust the width of your columns (or insert xl.Columns("A:C").AutoFit)
14.3 & 1.3.7
Re: AHK - Excel Current Date/Time
@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
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
- flyingDman
- Posts: 2817
- Joined: 29 Sep 2013, 19:01
Re: AHK - Excel Current Date/Time
these 2 linesdetermine 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).
Code: Select all
mnth := strsplit(xl.Range("A" lstrw).value, "/").1
nrw := (mnth = a_mm) ? lstrw + 1 : lstrw + 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
- flyingDman
- Posts: 2817
- Joined: 29 Sep 2013, 19:01
Re: AHK - Excel Current Date/Time
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
Re: AHK - Excel Current Date/Time
@flyingDmanflyingDman wrote: ↑31 Jul 2021, 11:57these 2 linesdetermine 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.Code: Select all
mnth := strsplit(xl.Range("A" lstrw).value, "/").1 nrw := (mnth = a_mm) ? lstrw + 1 : lstrw + 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).
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 (15.78 KiB) Viewed 1197 times
- flyingDman
- Posts: 2817
- Joined: 29 Sep 2013, 19:01
Re: AHK - Excel Current Date/Time
No lstrw + 1 is no gap (just next row) and lstrw + 3 is 2 row gap.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
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
Re: AHK - Excel Current Date/Time
flyingDman wrote: ↑31 Jul 2021, 23:32No lstrw + 1 is no gap (just next row) and lstrw + 3 is 2 row gap.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
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 (1.78 KiB) Viewed 1156 times
Re: AHK - Excel Current Date/Time
@flyingDman
Update
I tried the following code on
2010 activated excel
2019 activated excel
2013 un-activated excel
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
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 (30.89 KiB) Viewed 1102 times
Who is online
Users browsing this forum: Google [Bot], OrangeCat and 290 guests