Function to insert CREATE DATE?

dmagnus

Board Regular
Joined
Dec 16, 2005
Messages
66
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
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
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).


Code:
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

Board Regular
Joined
Dec 16, 2005
Messages
66
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

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
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

Board Regular
Joined
Dec 16, 2005
Messages
66

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
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

Board Regular
Joined
Dec 16, 2005
Messages
66

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Dan

Do you actually need a formula in the cell?
Code:
ActiveCell = "Prepared on " & DocProperty("Creation Date") & " by Dan Magnus"
 

dmagnus

Board Regular
Joined
Dec 16, 2005
Messages
66
Nope...I'm just trying to create a text string that automatically includes the file's create date.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Well try the code I posted.:)
 

Forum statistics

Threads
1,136,345
Messages
5,675,225
Members
419,555
Latest member
Paddington

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top