Serial Number For Excel Topic is solved

[Fulminare]

@boiler

In this code ,

Code: Select all

xl := ComObjCreate("excel.application")
wrkbk := xl.workbooks.open("C:\Users\Administrater\Desktop\New Microsoft Excel Worksheet.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++
}

colctxt := "static text"
xl.Range("A" nwrw):= nmbr + 1
xl.Range("A" nwrw).NumberFormat := "## "
xl.Range("B" nwrw).value := A_MM "/" A_DD "/" A_YYYY
xl.Range("C" nwrw).value := colctxt
xl.Range("D" nwrw).NumberFormat := "[$-en-US]h:mm Am/Pm;@"
xl.range("D" nwrw).value := A_Hour ":" A_Min ":" A_Sec


wrkbk.close(1)
xl.quit()

I changed

Code: Select all

xl.Range("B" nwrw).value := A_MM "/" A_DD "/" A_YYYY

to

Code: Select all

xl.Range("B" nwrw).value := A_DD "/" A_MM "/" A_YYYY

Which is causing this to happen. what should I do ? ( the date is getting displayed correctly but the serial numbers are getting messed up)

[boiler]

I suppose you’re in Europe or somewhere else where the day is listed before the month. It should work if you also change the line where the variable mnth is assigned to the following:

Code: Select all

mnth := strsplit(xl.Range("B" lstrw).value, "-").2

[Fulminare]

@boiler

I have done that

but to no avail.

this is the code after I changed it

Code: Select all

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


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

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


if nmbr is not number
	nmbr := 0
if (mnth != A_MM) {
	nmbr := 0
	nwrw++
}

colctxt := "static text"
xl.Range("A" nwrw):= nmbr + 1
xl.Range("A" nwrw).NumberFormat := "## "
xl.Range("B" nwrw).value := A_DD "-" A_MM "-" A_YYYY
xl.Range("C" nwrw).value := colctxt
xl.Range("D" nwrw).NumberFormat := "[$-en-US]h:mm Am/Pm;@"
xl.range("D" nwrw).value := A_Hour ":" A_Min ":" A_Sec


wrkbk.close(1)
xl.quit()

Actually,

it is working on 2010 activated excel
but not on 2013 un- activated excel and 2019 activated excel


it still enters the data as follows

[boiler]

Add the following line after the line where mnth is assigned and let me know what it shows:

Code: Select all

MsgBox, % mnth

[Fulminare]

@boiler

The message box is displaying "Aug"

[boiler]

Try changing this line:

Code: Select all

if (mnth != A_MM) {

to this:

Code: Select all

if (mnth != A_MMM) {

[Fulminare]

Try changing this line:

Code: Select all

if (mnth != A_MM) {

to this:

Code: Select all

if (mnth != A_MMM) {

@boiler

Finally !

It is working

Thank you very much for the trouble you've taken.

[boiler]

You're welcome. Glad to hear it's working for you.

[Fulminare]

Posting this for everyone's benefit. ( it saves the trouble of going through all the posts )
in essence this is the answer to my question.

The code does the following

Enters serial numbers in column A
Date ( DD-MM-YYYY) in column B
"static text" in column C
Time in column D

Once the month changes, a gap of one row is given before entering the data of the next month

Code: Select all

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


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


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


if nmbr is not number
	nmbr := 0
if (mnth != A_MMM) {
	nmbr := 0
	nwrw++
}


;ENTRIES 

colctxt := "static text"
xl.Range("A" nwrw):= nmbr + 1
xl.Range("A" nwrw).NumberFormat := "## "
xl.Range("B" nwrw).value := A_DD "-" A_MM "-" A_YYYY
xl.Range("C" nwrw).value := colctxt
xl.Range("D" nwrw).NumberFormat := "[$-en-US]h:mm Am/Pm;@"
xl.range("D" nwrw).value := A_Hour ":" A_Min ":" A_Sec


wrkbk.close(1)
xl.quit()