Serial Number For Excel 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

Serial Number For Excel

31 Jul 2021, 11:07

Broke my head over this

I want ahk to update column A of excel with serial number

this is what I have tried

Code: Select all

xl := ComObjCreate("excel.application")
wrkbk := xl.workbooks.open("C:\Users\Administrater\Desktop\Lol.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 + 1


var = 1
xl.Range("A" nrw):= var+1


wrkbk.close(1)
xl.quit()
[Mod edit: [code][/code] tags added.]

this is just entering the value "2" into the new rows


Can anyone please help me ?
User avatar
boiler
Posts: 16770
Joined: 21 Dec 2014, 02:44

Re: Serial Number For Excel

31 Jul 2021, 11:29

For one thing, this line is assigning the same thing (lstrw + 1) to nrw no matter what the result of the conditional statement is:

Code: Select all

nrw := (mnth = a_mm) ? lstrw + 1 : lstrw + 1
What did you want it to do? Something like the this?:

Code: Select all

nrw := (mnth = a_mm) ? lstrw : lstrw + 1

Are you expecting any other value than 2 to be entered? You assign 1 to var immediately before entering the value var + 1, so it is always going to enter the value 2.
User avatar
flyingDman
Posts: 2791
Joined: 29 Sep 2013, 19:01

Re: Serial Number For Excel

31 Jul 2021, 13:17

So, I guess this is a fork of this post: https://www.autohotkey.com/boards/viewtopic.php?f=76&t=93210

I assume you want to include a column with a number. Each time the script is launched, the new number is equal to the previous number + 1.
Find out what the number is of the previous entry: nmbr:= xl.Range("A" lstrw).value, then set the value in the new row : xl.range("A" nrw).value := nmbr + 1.
nwr is either lstrw + 1 or lstrw + 3 depending on whether the current month (A_MM ; built-in variable) is the same as that for the previous entry (mnth ; see the other post for that) or not. For that we use a ternary operator (see here: https://www.autohotkey.com/docs/Variables.htm#ternary).

Code: Select all

nrw := (mnth = a_mm) ? lstrw + 1 : lstrw + 3
.

Hope this helps.
14.3 & 1.3.7
Fulminare
Posts: 369
Joined: 26 Jun 2021, 20:15

Re: Serial Number For Excel

01 Aug 2021, 03:47

boiler wrote:
31 Jul 2021, 11:29
For one thing, this line is assigning the same thing (lstrw + 1) to nrw no matter what the result of the conditional statement is:

Code: Select all

nrw := (mnth = a_mm) ? lstrw + 1 : lstrw + 1
What did you want it to do? Something like the this?:

Code: Select all

nrw := (mnth = a_mm) ? lstrw : lstrw + 1

Are you expecting any other value than 2 to be entered? You assign 1 to var immediately before entering the value var + 1, so it is always going to enter the value 2.

@boiler

I wanted the values to keep increasing by 1

your code is working well

but I am initially having to enter "1" into the cell

if I run the script without entering "1" manually, it's not entering any data

after manually entering "1" then the script writes "2" into the next row


how to ensure that the script enters "1" by itself

note : the first useable row is A2 as A1 would be reserved for heading i.e " serial number "

so the 1st entry will be from A2
User avatar
boiler
Posts: 16770
Joined: 21 Dec 2014, 02:44

Re: Serial Number For Excel

01 Aug 2021, 03:54

Then it could easily be done without your var variable and just be based on the row number:

Code: Select all

xl.Range("A" nrw):= nrw - 1
Fulminare
Posts: 369
Joined: 26 Jun 2021, 20:15

Re: Serial Number For Excel

01 Aug 2021, 03:57

flyingDman wrote:
31 Jul 2021, 13:17
So, I guess this is a fork of this post: https://www.autohotkey.com/boards/viewtopic.php?f=76&t=93210

I assume you want to include a column with a number. Each time the script is launched, the new number is equal to the previous number + 1.
Find out what the number is of the previous entry: nmbr:= xl.Range("A" lstrw).value, then set the value in the new row : xl.range("A" nrw).value := nmbr + 1.
nwr is either lstrw + 1 or lstrw + 3 depending on whether the current month (A_MM ; built-in variable) is the same as that for the previous entry (mnth ; see the other post for that) or not. For that we use a ternary operator (see here: https://www.autohotkey.com/docs/Variables.htm#ternary).

Code: Select all

nrw := (mnth = a_mm) ? lstrw + 1 : lstrw + 3
.

Hope this helps.
@flyingDman

yes it is related to my previous question (I was trying to build up on your code )


your script is also giving me correct results

again, as I have mentioned in my reply to @boiler, the first entry should be manually entered. I want the script to enter "1" by itself


but the date function is something that I am unable to understand

( I have asked you the same question regarding the date in my previous question )
Fulminare
Posts: 369
Joined: 26 Jun 2021, 20:15

Re: Serial Number For Excel

01 Aug 2021, 04:11

boiler wrote:
01 Aug 2021, 03:54
Then it could easily be done without your var variable and just be based on the row number:

Code: Select all

xl.Range("A" nrw):= nrw - 1
@boiler

Yes. It is working as intended.
the first entry is getting entered in A2

but it is stopping at '9"

i.e
1
2
3
4
5
6
7
8
9

after 9 it is not entering "10"

Code: Select all

xl := ComObjCreate("excel.application")
wrkbk := xl.workbooks.open("C:\Users\Administrater\Desktop\Lol.xlsx")
lstrw := xl.Range("A" xl.Rows.Count).End(-4162).Row
mnth := strsplit(xl.Range("A" lstrw).value, "/").1
nrw := (mnth = a_mm) ? lstrw : lstrw + 1


nmbr:= xl.Range("A" lstrw).value


xl.Range("A" nrw):= nrw - 1

xl.Range("A" nrw).NumberFormat := "## "


wrkbk.close(1)
xl.quit()
[Mod edit: [code][/code] tags added.]
Last edited by gregster on 01 Aug 2021, 04:42, edited 1 time in total.
Reason: Please use [code] tags. Thank you!
User avatar
boiler
Posts: 16770
Joined: 21 Dec 2014, 02:44

Re: Serial Number For Excel

01 Aug 2021, 04:58

Are you sure it enters 9 and doesn’t instead stop at 8? That’s what it should do since the current month is 8 and you have it so it stops advancing rows when the last value entered is equal to the month number. I don’t know what you are expecting it to do at that point, but that’s the reason for it.
Fulminare
Posts: 369
Joined: 26 Jun 2021, 20:15

Re: Serial Number For Excel

01 Aug 2021, 05:13

boiler wrote:
01 Aug 2021, 04:58
Are you sure it enters 9 and doesn’t instead stop at 8? That’s what it should do since the current month is 8 and you have it so it stops advancing rows when the last value entered is equal to the month number. I don’t know what you are expecting it to do at that point, but that’s the reason for it.

@boiler




Regarding what you've said, I have changed the month to Spt in my system
perhaps that's why it entered 9
( after reading your reply I changed the month back to Aug and now its entering 8 )

I am confused

the script should keep entering the serial numbers.

one the month changes the script should reset the count

eg

Month : Aug

1
2
3
4
5
6

Month : Spt
1
2
3
4

so on and so forth
User avatar
boiler
Posts: 16770
Joined: 21 Dec 2014, 02:44

Re: Serial Number For Excel

01 Aug 2021, 07:23

Well, there's not much in the logic of the code you posted or the structure of your Excel file that would accomplish that. You can’t just base the serial number on the row number as I suggested if you're going to have it starting over at 1 at certain points, so you should use flyingDman’s xl.range("A" nrw).value := nmbr + 1.

But more importantly, the value of the serial number in the last row equaling the month number would have nothing to do with when to start over at 1. Do you want it to start over when the actual current date is the start of a new month (i.e., different than the month of the last entry)? Then you would need another cell next to the serial number in which the script would enter the date, then it would compare the current month to the date in that cell. It would then make sense to start over at 1 if the month from the date in that cell was not the same as the current month. Something like this:

Code: Select all

xl := ComObjCreate("excel.application")
wrkbk := xl.workbooks.open("C:\Users\Administrater\Desktop\Lol.xlsx")
lstrw := xl.Range("A" xl.Rows.Count).End(-4162).Row
mnth := strsplit(xl.Range("B" lstrw).value, "/").1

nmbr := xl.Range("A" lstrw).value
if nmbr is not number
	nmbr := 0
if (mnth != A_MM)
	nmbr := 0

xl.Range("A" lstrw + 1):= nmbr + 1
xl.Range("A" lstrw + 1).NumberFormat := "## "
xl.Range("B" lstrw + 1).value := A_MM "/" A_DD "/" A_YYYY

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

Re: Serial Number For Excel

01 Aug 2021, 08:16

boiler wrote:
01 Aug 2021, 07:23
Well, there's not much in the logic of the code you posted or the structure of your Excel file that would accomplish that. You can’t just base the serial number on the row number as I suggested if you're going to have it starting over at 1 at certain points, so you should use flyingDman’s xl.range("A" nrw).value := nmbr + 1.

But more importantly, the value of the serial number in the last row equaling the month number would have nothing to do with when to start over at 1. Do you want it to start over when the actual current date is the start of a new month (i.e., different than the month of the last entry)? Then you would need another cell next to the serial number in which the script would enter the date, then it would compare the current month to the date in that cell. It would then make sense to start over at 1 if the month from the date in that cell was not the same as the current month. Something like this:

Code: Select all

xl := ComObjCreate("excel.application")
wrkbk := xl.workbooks.open("C:\Users\Administrater\Desktop\Lol.xlsx")
lstrw := xl.Range("A" xl.Rows.Count).End(-4162).Row
mnth := strsplit(xl.Range("B" lstrw).value, "/").1

nmbr := xl.Range("A" lstrw).value
if nmbr is not number
	nmbr := 0
if (mnth != A_MM)
	nmbr := 0

xl.Range("A" lstrw + 1):= nmbr + 1
xl.Range("A" lstrw + 1).NumberFormat := "## "
xl.Range("B" lstrw + 1).value := A_MM "/" A_DD "/" A_YYYY

wrkbk.close(1)
xl.quit()

@boiler

Code: Select all

xl.range("A" nrw).value := nmbr + 1
where should this code be entered ?

this is what I am getting on running your code
Attachments
excel 6.png
excel 6.png (4.11 KiB) Viewed 1009 times
User avatar
boiler
Posts: 16770
Joined: 21 Dec 2014, 02:44

Re: Serial Number For Excel

01 Aug 2021, 09:22

Fulminare wrote:

Code: Select all

xl.range("A" nrw).value := nmbr + 1
where should this code be entered ?
Nowhere. I changed it to xl.Range("A" lstrw + 1):= nmbr + 1, which is the same thing but without the need for another variable (nrw).

Fulminare wrote: this is what I am getting on running your code
It looks like you are getting that because your dates are getting dashes between them with your version of Excel instead of slashes like mine does below and what the code tries to write there. It must have to do with location or preference settings.

Code: Select all

1 	8/1/2021
2 	8/1/2021
3 	8/1/2021
4 	8/1/2021
5 	8/1/2021
6 	8/1/2021

Because of that, the StrSplit is not finding the slash, so try this version instead, where it splits based on a dash:

Code: Select all

xl := ComObjCreate("excel.application")
wrkbk := xl.workbooks.open("C:\Users\Administrater\Desktop\Lol.xlsx")
lstrw := xl.Range("A" xl.Rows.Count).End(-4162).Row
mnth := strsplit(xl.Range("B" lstrw).value, "-").1

nmbr := xl.Range("A" lstrw).value
if nmbr is not number
	nmbr := 0
if (mnth != A_MM)
	nmbr := 0

xl.Range("A" lstrw + 1):= nmbr + 1
xl.Range("A" lstrw + 1).NumberFormat := "## "
xl.Range("B" lstrw + 1).value := A_MM "/" A_DD "/" A_YYYY

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

Re: Serial Number For Excel

01 Aug 2021, 09:39

Update

@boiler @flyingDman

I have taken the code given by @mikeyww from one of my previous questions
( https://www.autohotkey.com/boards/viewtopic.php?f=76&t=93171&p=412577#p412577 )

And the code ( I think I did some modifications to it ) given by flyingDman and came up with this

Code: Select all

ini := StrReplace(A_ScriptFullPath, ".ahk", ".ini"), track := "Track"
IniRead, year , %ini%, %track%, year
IniRead, month, %ini%, %track%, month
IniRead, runs , %ini%, %track%, runs, 0
If (year != A_YYYY || month != A_MM) {
       IniWrite, %A_YYYY%   , %ini%, %track%, year
       IniWrite, %A_MM%     , %ini%, %track%, month
       IniWrite, % runs := 1, %ini%, %track%, runs
} Else IniWrite, % ++runs   , %ini%, %track%, runs
MsgBox = %runs%





xl := ComObjCreate("excel.application")
wrkbk := xl.workbooks.open("C:\Users\user\Desktop\LoL.xlsx")
lstrw := xl.Range("A" xl.Rows.Count).End(-4162).Row
mnth := strsplit(xl.Range("A" lstrw).value, "-").2
nrw := (mnth = a_mm) ? lstrw : lstrw + 1


nmbr:= xl.Range("A" lstrw).value


xl.Range("A" nrw):= MsgBox

xl.Range("A" nrw).NumberFormat := "##"


wrkbk.close(1)
xl.quit()
This is resetting the count to 1 when the month changes

But I am unable to give a gap ( between "10" and "1")

Can someone please guide me ?
Attachments
excel 7.png
excel 7.png (7.79 KiB) Viewed 993 times
Fulminare
Posts: 369
Joined: 26 Jun 2021, 20:15

Re: Serial Number For Excel

01 Aug 2021, 09:43

boiler wrote:
01 Aug 2021, 09:22
Fulminare wrote:

Code: Select all

xl.range("A" nrw).value := nmbr + 1
where should this code be entered ?
Nowhere. I changed it to xl.Range("A" lstrw + 1):= nmbr + 1, which is the same thing but without the need for another variable (nrw).

Fulminare wrote: this is what I am getting on running your code
It looks like you are getting that because your dates are getting dashes between them with your version of Excel instead of slashes like mine does below and what the code tries to write there. It must have to do with location or preference settings.

Code: Select all

1 	8/1/2021
2 	8/1/2021
3 	8/1/2021
4 	8/1/2021
5 	8/1/2021
6 	8/1/2021

Because of that, the StrSplit is not finding the slash, so try this version instead, where it splits based on a dash:

Code: Select all

xl := ComObjCreate("excel.application")
wrkbk := xl.workbooks.open("C:\Users\Administrater\Desktop\Lol.xlsx")
lstrw := xl.Range("A" xl.Rows.Count).End(-4162).Row
mnth := strsplit(xl.Range("B" lstrw).value, "-").1

nmbr := xl.Range("A" lstrw).value
if nmbr is not number
	nmbr := 0
if (mnth != A_MM)
	nmbr := 0

xl.Range("A" lstrw + 1):= nmbr + 1
xl.Range("A" lstrw + 1).NumberFormat := "## "
xl.Range("B" lstrw + 1).value := A_MM "/" A_DD "/" A_YYYY

wrkbk.close(1)
xl.quit()
@boiler
this is working and is simpler than the solution I sort of came up in my previous post

but how do I eliminate the date entry in column B ? I just want the serial number
User avatar
boiler
Posts: 16770
Joined: 21 Dec 2014, 02:44

Re: Serial Number For Excel

01 Aug 2021, 09:49

The date entry is the reason it works. Your original code was trying to extract a month from a cell containing the serial number, not a date, so that obviously can’t work. That’s why I added a date column. It’s the only way that approach makes sense. You can always hide that column in Excel if you don’t want to see it, and/or you can use a column way out to the right like Z instead of B for the date column.
Fulminare
Posts: 369
Joined: 26 Jun 2021, 20:15

Re: Serial Number For Excel

01 Aug 2021, 09:59

@boiler

Okay I will try hiding the column

Thank you :)
User avatar
boiler
Posts: 16770
Joined: 21 Dec 2014, 02:44

Re: Serial Number For Excel

01 Aug 2021, 10:54

This puts a gap row when a new month starts for my version of the code:

Code: Select all

xl := ComObjCreate("excel.application")
wrkbk := xl.workbooks.open("C:\Users\user\Desktop\LoL.xlsx")
lstrw := xl.Range("A" xl.Rows.Count).End(-4162).Row
mnth := strsplit(xl.Range("B" lstrw).value, "-").1

nwrw := lstrw + 1
nmbr := xl.Range("A" lstrw).value
if nmbr is not number
	nmbr := 0
if (mnth != A_MM) {
	nmbr := 0
	nwrw++
}

xl.Range("A" nwrw):= nmbr + 1
xl.Range("A" nwrw).NumberFormat := "## "
xl.Range("B" nwrw).value := A_MM "/" A_DD "/" A_YYYY

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

Re: Serial Number For Excel

01 Aug 2021, 11:18

@boiler

The gap function is working nicely

couple of more doubts though

How to make the first entry start from A2? it is starting from A3 now

and

how to get the date in the following format : "01-Aug-2021"
User avatar
boiler
Posts: 16770
Joined: 21 Dec 2014, 02:44

Re: Serial Number For Excel

01 Aug 2021, 11:35

Fulminare wrote: How to make the first entry start from A2? it is starting from A3 now
Try this:

Code: Select all

xl := ComObjCreate("excel.application")
wrkbk := xl.workbooks.open("C:\Users\user\Desktop\LoL.xlsx"S)
lstrw := xl.Range("A" xl.Rows.Count).End(-4162).Row
mnth := strsplit(xl.Range("B" lstrw).value, "-").1

nwrw := lstrw + 1
nmbr := xl.Range("A" lstrw).value
if (nmbr = "")
	nwrw--
if nmbr is not number
	nmbr := 0
if (mnth != A_MM) {
	nmbr := 0
	nwrw++
}

xl.Range("A" nwrw):= nmbr + 1
xl.Range("A" nwrw).NumberFormat := "## "
xl.Range("B" nwrw).value := A_MM "/" A_DD "/" A_YYYY

wrkbk.close(1)
xl.quit()

Fulminare wrote: how to get the date in the following format : "01-Aug-2021"
Format the column how you normally would in Excel. Click the column header, right-click, select "Format Cells...", select or create the date format of your choice.
Fulminare
Posts: 369
Joined: 26 Jun 2021, 20:15

Re: Serial Number For Excel

01 Aug 2021, 23:00

@boiler

It is working now.

I am trying to add some more data like "static text" and current time into column C and D respectively.

if I do not succeed, I will request for help again

Thank you.

Regards.

Return to “Ask for Help (v1)”

Who is online

Users browsing this forum: Google [Bot], JoeWinograd, yabab33299 and 133 guests