How to create auto-incrementing number based on current date?

[sakura]

Hello,

I have a question regarding an auto-incrementing number based
upon the current date.

What I would like to do is create a number based on the year
and month. For example: if year is 2004, and month is March,
I'd like the number to read: 0403; if year is 2005, and month is
November, I'd like the number to read 0511. Once that is
accomplished, I would like to have another 3 digit number
which is the one that actually is incremented. So, the final
number would be something like: 0403001, 0403002, etc...

I would prefer that there be some kind of verification routine
when the file is opened which checks the value of the cell
containing the number. If the file is being opened or saved
for the first time, then the number should be placed into the
proper cell. However, ff the file is opened at some point in the
future for viewing or modification, the number should remain as
is and never be incremented.

In addition, I would like for the extra 3 digit number to cycle
back to "001" at the first of each month.

Also, the files will be not necessarily be used on the same PC so,
I would prefer that this would not be tied to the registry settings.

Thanks very much!

Sakur

 

[Rob van Gelder]

Assuming your cell which contains the ID is formatted as Text
Cell in question is Sheet1!A1

Sub ApplyID()
Const cPath = "C:\T\"
Dim rng As Range, lngTemp As Long

Set rng = Worksheets("Sheet1").Range("A1")
If IsEmpty(rng.Value) Then
lngTemp = CLng(Format(Date, "yymm")) * 1000
Do
lngTemp = lngTemp + 1
Loop Until Dir(cPath & Format(lngTemp, "0000000") & ".xls") = ""
rng.Value = Format(lngTemp, "0000000")
End If
End Sub

 

[sakura]

Rob,

Many thanks for your quick reply.

I've tried out your code both in the file I was working with
and a brand new worksheet as well. Unfortunately, it didn't
quite work. I didn't get any errors, but it did not display any
information in the cell.

When I close the file, it always asks me if I want to save
(even if I don't manually make any changes) - which tells
me that something is happening, but I just don't see it.

Any ideas of what I might be doing wrong?

 

[Rob van Gelder]

All that procedure does is the following:

If the contents of cell A1 is empty then generate the next magic_number and
store in cell A1

I didn't write your entire requirements as I didn't fully understand them.
Are you trying to use the magic_number as the filename for a workbook?

 

[sakura]

The filename is completely different from the magic_number.

On the new blank worksheet that I created, saved, and
re-opened - something should have been displayed in
cell A1 correct? The procedure you wrote is there, but
nothing is ever displayed in cell A1. It is always empty.

I'll try to explain a bit more clearly this time if I can:

I have an Excel file - "packinglist.xls". The original file
is always blank. I'll open this file, enter the items that
have shipped, and save the file. At this point, I would like
to have a unique identifying number added to the file which
is saved. I will manually type the file name based on the
customer, when I save it. If I reopen this same file again
for any reason, I would like for the unique id number to remain
the same as when originally saved. When I open "packinglist.xls"
for another customer, I would like another unique id number
added to that particular file when it's saved. And so on... Hope
this is a bit more clearer for you now.

To try and further clarify - when "packinglist.xls" is opened, there
will never be any unique identifying number in any cell. When
the file is saved, regardless of the file name it is saved as the
unique identifier will be added. When any of those files are
re-opened, the unique identifier will remain as it was originally
saved as.

Is what I am trying to do even possible since I am saving
to various file names and always leaving the original file intact?

Thanks again for your assistance,

Sakura

 

[Rob van Gelder]

Now I understand.

The procedure I gave you is not running. Something must trigger it's
execution. These triggers are usually Workbook events, such as
Workbook_BeforeSave()

There's no difficulty adding a magic_number to the (about to be saved)
workbook and keeping it intact from then on.

I think the real difficulty will be determining the 3rd part of the
magic_number. How does the generator know what number it's up to? Somewhere
you have to have a place to store the "counter" and the month for which it
applies.
Without it, you would have to open each saved workbook, read the cell and
use some logic to determine the next number in the series for that month.
Not impossible, but slow and impractical.

My suggestion is to forget about a unique sequence number and use another
date format, such as yymmddhhmmss or some combination like that.
Let me know what you decide...


So, if that issue gets solved then you would add the following code to the
Workbook to get it to run:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
ApplyID
End Sub



--
Rob van Gelder - http://www.vangelder.co.nz/excel

The filename is completely different from the magic_number.

On the new blank worksheet that I created, saved, and
re-opened - something should have been displayed in
cell A1 correct? The procedure you wrote is there, but
nothing is ever displayed in cell A1. It is always empty.

I'll try to explain a bit more clearly this time if I can:

I have an Excel file - "packinglist.xls". The original file
is always blank. I'll open this file, enter the items that
have shipped, and save the file. At this point, I would like
to have a unique identifying number added to the file which
is saved. I will manually type the file name based on the
customer, when I save it. If I reopen this same file again
for any reason, I would like for the unique id number to remain
the same as when originally saved. When I open "packinglist.xls"
for another customer, I would like another unique id number
added to that particular file when it's saved. And so on... Hope
this is a bit more clearer for you now.

To try and further clarify - when "packinglist.xls" is opened, there
will never be any unique identifying number in any cell. When
the file is saved, regardless of the file name it is saved as the
unique identifier will be added. When any of those files are
re-opened, the unique identifier will remain as it was originally
saved as.

Is what I am trying to do even possible since I am saving
to various file names and always leaving the original file intact?

Thanks again for your assistance,

Sakura

 

[sakura]

Rob,

Thanks for your explanation and continued assistance.

I pasted the Workbook_BeforeSave code into the blank
worksheet that I have been playing around with. Saved the
file, closed the file, re-opened the file, and there's still nothing
displayed in cell A1.

Here's exactly how the code appears:

PRIVATE SUB WORKBOOK_BEFORESAVE(BYVAL SAVEASUI AS BOOLEAN, CANCEL A
BOOLEAN)
APPLYID
END SUB
-------------------------------------------------------------------------------------------
SUB APPLYID()
CONST CPATH = \"C:\T\\"
DIM RNG AS RANGE, LNGTEMP AS LONG
SET RNG = WORKSHEETS(\"SHEET1\").RANGE(\"A1\")
IF ISEMPTY(RNG.VALUE) THEN
LNGTEMP = CLNG(FORMAT(DATE, \"YYMM\")) * 1000
DO
LNGTEMP = LNGTEMP + 1
LOOP UNTIL DIR(CPATH & FORMAT(LNGTEMP, \"0000000\") & \".XLS\") = \"\"
RNG.VALUE = FORMAT(LNGTEMP, \"0000000\")
END IF
END SUB


I like your idea about using the hours, minutes, and seconds. My
only concern is that the id number would be a bit too long. Could
I do something like:

yymmdd##

Where the '##' would be replaced by a value determined by the
current 'hhmmss'? Further, is it possible to create a unique
2 digit number (##) which is based upon 'hhmmss'?

Alternatively, could I store the unique id into an independent
text file which the worksheet would read upon each creation
of the unique id? I suppose though that then I would get into
problems if I was to use different computers and making sure
the text file always stays current with the worksheet on the
appropriate computer.

My main concern now is to just get the code that you provided
to actually do something. Still am confused about how to get
unique id number to display in cell A1. I've tried to closely follo

all your details, but seems that I'm missing some major point.

Thanks again!

*Now I understand.

The procedure I gave you is not running. Something must trigger it's
execution. These triggers are usually Workbook events, such as
Workbook_BeforeSave()

There's no difficulty adding a magic_number to the (about to b
saved)
workbook and keeping it intact from then on.

I think the real difficulty will be determining the 3rd part of the
magic_number. How does the generator know what number it's up to
Somewhere
you have to have a place to store the "counter" and the month fo
which it
applies.
Without it, you would have to open each saved workbook, read the cel
and
use some logic to determine the next number in the series for tha
month.
Not impossible, but slow and impractical.

My suggestion is to forget about a unique sequence number and us
another
date format, such as yymmddhhmmss or some combination like that.
Let me know what you decide...


So, if that issue gets solved then you would add the following cod
to the
Workbook to get it to run:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
ApplyID
End Sub

[/QUOTE]

 

[Rob van Gelder]

Those backslashes are strange. Are they just something your e-mail program
does?

ApplyID isn't going to work until you decide on a number system.


Here's another ApplyID. uses yymm then ddhhmm converted to hexadecimal (to
make it slightly shorter). So it'll be unique to the minute.

Sub ApplyID()
Dim rng As Range

Set rng = Worksheets("Sheet1").Range("A1")
If IsEmpty(rng.Value) Then
rng.Value = Format(Date, "yymm") & Hex(Format(Now, "ddhhmm"))
End If
End Sub

 

[sakura]

I'm not sure about the backslashes, they didn't appear in the
posting I made. I only saw them with your last reply.

Also, I guess I don't fully fathom what you're saying about the
ApplyID won't work until I decide on a number system. What
does that mean? The format of cell A1 is supposed to be "text"
correct?

Perhaps I'm wrong, but I thought the code in Workbook_BeforeSave
is what enables the ApplyID to take effect?

*Those backslashes are strange. Are they just something your e-mai
program
does?

ApplyID isn't going to work until you decide on a number system.


Here's another ApplyID. uses yymm then ddhhmm converted t
hexadecimal (to
make it slightly shorter). So it'll be unique to the minute.

Sub ApplyID()
Dim rng As Range

Set rng = Worksheets("Sheet1").Range("A1")
If IsEmpty(rng.Value) Then
rng.Value = Format(Date, "yymm") & Hex(Format(Now, "ddhhmm"))
End If
End Sub

 

[sakura]

Still am not able to get any of these codes to actually do
something. I'm obviously missing something, but haven't
a clue what it is.

If anyone would know what I need to do in order for the
ApplyID code to work, I'd appreciate hearing about it.

I've tried using this:
PRIVATE SUB WORKBOOK_BEFORESAVE(BYVAL SAVEASUI AS BOOLEAN, CANCEL A
BOOLEAN)
APPLYID
END SUB

But, apparently nothing is happening when I save the file
because when I re-open there still is nothing displayed in the
cell.

I'm rather new to the world of Excel and even newer still using
VBA. If anyone out there could point me in the right direction of
what other code is necessary for these things to take effect,
I would be most grateful!!!

Thanks very much!

 

[Norman Goldsmith]

sakura
Maybe this is the major point you are missing. Where did you paste the code? It does NOT go
into a blank worksheet. You have to paste the code into a VBA code sheet. Start from the tools
menu. Locate macro and select create. Enter any name just to bring you to the macro editor.
THEN, paste in the code, erasing anything that you find there.
Norm

 

[sakura]

Greetings Norman,

Thanks so much for your reply.

I tried your suggestion of creating the macro, but regretfully
it's still not doing anything. Your suggestion seems to be
exactly what I have been doing though since all this code
was already there, when I began to create the new macro.

I've attempted everything I can think of the past several days,
but still not having any luck at all with getting the code to perform
any action whatsoever. It's really quite frustrating at this point -
to have the code which appears as if it would do exactly what I
am seeking; only to fail so miserably with whatever method I
attempt to initiate said code.

PLEASE, if there would be anyone out there who could offer any
other advise, it would be so very much appreciated.

THANK YOU!!!

Sakura

*sakura
Maybe this is the major point you are missing. Where did you past
the code? It does NOT go
into a blank worksheet. You have to paste the code into a VBA cod
sheet. Start from the tools
menu. Locate macro and select create. Enter any name just to brin
you to the macro editor.
THEN, paste in the code, erasing anything that you find there.
Norm