Function to insert CREATE DATE?

[dmagnus]

I am using some VB code to do several things, one of which is to insert a line in an excel field that reads "Created by Joe Shmo on XX/XX/XXXX", where the X's are the file's create date. Is there a function to get this date programatically?

Thanks in advance for your responses. Dan

 

[Damon Ostrander]

Hi dmagnus,

Okay, here is a user-defined function that returns document properties. Use it to return the creation date in a cell like this:

=DocProperty("Creation Date")

and don't forget to format the cell as Date (Format > Cell > Date).

Function DocProperty(PropName As String) As Variant
   DocProperty = ThisWorkbook.BuiltinDocumentProperties(PropName)
End Function

To install this function, go to the VBE (keyboard Alt-TMV), insert a new macro module (Alt-IM) and paste this code into the Code pane.

Keep Excelling.

Damon

PS. Note that this function can be used to give many other document properties, e.g., "Author", "Last Print Date", "Last Save Time", "Title".

 

[dmagnus]

So after putting the Function code into a VB module, I woud use this line of code?

ActiveCell.FormulaR1C1 = "=""Prepared on" & DocProperty("Creation Date") & " by Dan Magnus"

**Remember, I'm trying to get the create date into a text string.

I actually just tried this line of code by "stepping into" the code and it errored at this line. Anything look wrong to you? Thanks.

 

[Damon Ostrander]

Hi again dmagnus,

I think the only thing wrong is that all the interior quotation marks must be doubled, not just the first.

Damon

 

[dmagnus]

Damon, perhaps I don't understand your last suggestion. As far as I can tell ALL the quotes in my attempted code have double quotes...

 

[Damon Ostrander]

Hi again Dan,

This is what I meant:

ActiveCell.FormulaR1C1 = "=""Prepared on"" & DocProperty(""Creation Date"") & "" by Dan Magnus"""

This is because in order to include a quotation mark within a string you must use two. If the quotes aren't "doubled", the VBA interpreter will assume that each time it encounters a quote that it is either the start or end of a text string--which you don't want because the entire formula must be a single string. Every computer language uses this or a similar rule to allow one to enter quotes within a string without interpreting them as ending the string.

Damon

 

[dmagnus]

Is the DocProperty in the code you give supposed to be "PropName", or is this what I want to change to "CreateDate"?

Either way I'm not getting it to work. I have the code you suggested in it's own module just as you typed it, and the code in my macro has been corrected to include double quotes.

Any thoughts? Thanks! Dan

 

[Norie]

Dan

Do you actually need a formula in the cell?

ActiveCell = "Prepared on " & DocProperty("Creation Date") & " by Dan Magnus"

 

[dmagnus]

Nope...I'm just trying to create a text string that automatically includes the file's create date.

 

[Norie]

Well try the code I posted.