turning a date into a incremental number

[Abdullah]

Hi guys,

I'm having problems getting a template to work. Essentially, there is a table where info is entered. The first column is a number, and the others are info. Upon completing a row, the user clicks a macro button that adds another row to the table.

Currently, I have a field in the header that says "Click then enter date". Once it is clicked a macro deletes the text and leaves the cursor there to type in the date.

The user will type in whateve date it happens to be in M/d/yyyy format (ex: 2/4/2008). I need to take that date and turn it into a number that is entered into column 1. But it changes format, in column 1 I need the info to be:

MMddrrr

where rrr is a 3 digit row number, (or incremental number starting at 001)

for the above example column 1 row 1 of the table would read:

0204001

the user would input whatever info they want, and hit the add a row macro button, I'm hoping to then fill in

0204002

in column 1 of row 2
Once the add a row button is hit again, and a row is added I hope column 1 would read

0204003

for that row 3, etc and so on

the last 3 digits would be the row number, or just an incrementable integer starting at 001(whichever is easier). And the first 4 digits would always be the month and day that is entered when the template is first opened.

I'm not really sure how to approach this, I tried making a form field for the date, but that made things complicated.

Any help would be greatly appreciated.

[fumei]

What have you actually got so far?

Field in the header? Do you mean the heading row of the table, or the real header?

[Abdullah]

I mean a field in the actual header of the document.

All I really have working is a macro that adds a row, when you click the add a row button

[fumei]

Why on earth would you use a field in the header to add rows to a table????

Try posting a sample file with what you have. I am having a hard time imaging what you are doing.

Also, are you using this as a template, a real template? A .dot file that is cloned?

[Abdullah]

Yes, I am using a .dot file that is cloned. Also, I have realized that having an input embedded in the header is more of a pain than it is worth.

What I've since been trying to do is have prompts when the file is opened, asking for the 2 digit month, then the 2 digit day, and the 4 digit year, as 3 variables.

I could use those 3 to fill in an actual date (where needed) and then disregard the year for my actual application. Which would print the following in the first column of row 1.

MMDD001 (where MM is 2 digit month, DD is 2 digit day, and 001 is just a three digit representation of 1 (a seperate and 4th variable). Then when the add row button is clicked it would add a row and then just add 1 to the 3 digit variable on the end (the 001).

Unfortunately I'm having problems with prompting for the date info. And it will take a little more research to make the integer I use for the row # a 3 digit number.

Any help would be greatly appreciated.

[Abdullah]

The add row button I have is below the last row in the table created.

[fumei]

"What I've since been trying to do is have prompts when the file is opened, asking for the 2 digit month, then the 2 digit day, and the 4 digit year, as 3 variables."

Having inputs when the file is opened is very easy. Simply put either inputboxes, or a userform Show, in the Document_Open event.

[fumei]

Just following up.

Yes, using something in the header is not normal for getting user input.

Again, if you want to get user input on opening the document, use the Document_Open event.

Your 3 digit integer is a little bit of an issue, as 001 is not a valid integer.

You have a couple of options.

A) Use a SEQ field, which is a sequentially inrementing field number. However, it still is 1...not 001. So you will have to process to get your leading 0's. Obviously you will need logic to determine if the number is 9 (or 009) then the NEXT number has to be 010. In other words, if the integer is > 9 then add ONE zero (as text) to it. if it is <= 9, then add TWO zeros (as text) to it.

B) Use a Docvariable. Your row adding macro would get the value of the DocVariable, add a row, and increment the Docvariable by 1.

[Abdullah]

Fumei,

Thank you for the help. Once I stopped having the input in the header things went much smoother. I set up prompts to ask the user for month and day (input those as strings so that 02 would retain the 0).


ActiveDocument.Tables(1).Rows(2).Cells(1).Select
month = InputBox$("Enter the month:")
day = InputBox$("Enter the day:")
'gives reference to cell 1 then adds text
rownum = ActiveDocument.Tables(1).Rows.Count
Set cel = ActiveDocument.Tables(1).Rows(rownum - 1).Cells(1)
cel.Range.Text = month & day & "00" & rownum - 2


I actually set up a seperate macro (as a button) for the addition of the rows.


Selection.MoveUp Unit:=wdLine, Count:=1
Selection.InsertRowsBelow 1
Selection.MoveLeft Unit:=wdCharacter, Count:=1
rownum = ActiveDocument.Tables(1).Rows.Count
Set cel = ActiveDocument.Tables(1).Rows(rownum - 1).Cells(1)
cel.Range.Text = month & day & "00" & rownum - 2


I haven't done it yet, but I am going to add the if logic to add the appropriate number of 0's.

[Abdullah]

One last question...is there a way for me to insert the date obtained from the prompts into the last line of the header?

Can't get the cursor to move up into the header. Wasn't sure if there was a go to header command, or something similar for me to have it output the date obtained into the header.

[Abdullah]

Nevermind, figured out a work around for having a date in my document.

Thank you for all your assistance though

[gwkenny]

OP: This is truly weird. I can only think that your definition of "Header" is different than everyone else's here.

First you say that your table regarding these inputs are in the "Header" then you say, "Can't get the cursor to move up into the header".

Can't have it both ways :(

My comments regarding what I can understand:

You don't have any data validation. Someone can enter "SE" for month and "XY for date. Your macro, as it stands, will output "SEXY000".

Instead of 2 input boxes asking for month and day separately, just one input box for the date.

Then validate if that input is really a date with the ISDATE() function. If it is not valid, then query the user again.

Let's assume you got the date and validated it in the variant variable named va_Date.

To get the month, define a string variable (let's say it's st_A). Then use this to get the month:

st_A = FORMAT(MONTH(va_Date),"00")

Using the same logic, to get the day would be:

st_B = FORMAT(DAY(va_Date),"00")

Then you use the following to put the information into your cell assuming your existing code has been modified as stated above:

cel.Range.Text = st_A & st_B & FORMAT(rownum - 2,"000")

This negates any need for logic. Always try and get the computer to do as much work for you as possible!!!

__________________
g-
gwkenny@Fin-ITSolutions.com (gwkenny@Fin-ITSolutions.com)
___________________________________

Looking for jobs big or small, drop me a line! Thanks!

[fumei]

You do not need to put the cursor into a header to put text into the header. You can use either a Range object of the header, or a HeaderFooter object of the header (and then its range).

In other words, you can action the content of a header directly, without ever having anything to do with Selection (the cursor).

ActiveDocument.Sections(1).Headers(wdHeaderFooterPrimary) _
.Range.InsertAfter " some text"would simply add the text " some text" at the end of that header content.

gwkenny, are these not contradictory?

"Using the same logic, to get the day would be:"

"This negates any need for logic. "

Just razzing....please forgive me. I think I know what you were getting at. Although I would disagree there is EVER anything that negates a need for logic!

[gwkenny]

"Just razzing"

I laughed and that's all that matters

"Although I would disagree there is EVER anything that negates a need for logic!"

I was going to answer Chaos Theory but dammit if there isn't logic down in there somewhere!!!

:hi:

[fumei]

"I was going to answer Chaos Theory but dammit if there isn't logic down in there somewhere!!!"

Now THAT cracked ME up!