Extract data from filename and auto-set static date

**Peter Visser** · 05-12-2010, 09:51 AM

Good day

I'm Peter and have a query regarding a VBA function I want to implement in an Excel 2003 file. I've some Matlab programming background, but no VBA experience, so assistance would be greatly appreciated.

I've got a file template sitting on a server. A user pulls this file and creates a new file with the filename and path in the form Z:\Facility\Admin\Order\BxxxxYYY.xls

What I want to happen is that cell F6 should automatically contain the filename, ie BxxxxYYY when the file is opened. Furthermore, in cell H10 the date should automatically be entered when the file is opened, but should from then on stay static. With =today(), I can obtain the date the file has been accessed for the first time, but this changes everytime the file is opened on a different date.

I've tried my luck on forums and by myself, to no avail. Hence I contact the experts.

**masteff** · 05-12-2010, 06:08 PM

For cell F6, either =CELL("filename") or if you don't want the directory and sheet names then =MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND("]",CELL("filename"))-1-FIND("[",CELL("filename")))

As for your H10 date, you need a macro that runs when the workbook is opened like:

Private Sub Workbook_Open()
If Range("H10") = "" Then
    Range("H10").Select
    ActiveCell.FormulaR1C1 = "=TODAY()"
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
End If
End Sub

(Note: this requires the template be blank in that cell, or you could fill it w/ something like "DATE" and then change the if statement in the macro from "" to "DATE" or whatever)

Oh and there might be a simpler way to past the date into the cell, I admit that I'm not much of a macro person myself.

**davesexcel** · 05-12-2010, 07:57 PM

Instead of entering a formula in the cell you can enter the date

Range("H10") = Date
Range("F6") = ThisWorkbook.Name

**shg** · 05-12-2010, 08:09 PM

The default action on a template is New (as opposed to Open). When you do that, if the template's name is Bob, the workbook name is Bob1 -- no extension, since it hasn't been saved as a workbook (or anything else).

How do you intend to synthesize the name for the new file BxxxxYYY.xls?

**Peter Visser** · 05-18-2010, 04:26 AM

Thanks to everyone for the reply, sorry I took so long to reply, but I've been in the workshop most of the time.

I've implemented masteff's function and code. Both do what they should, but I see that I stated my own problem incorrectly. What I should've noted was that the file template is an existing file with data, not an empty template, so as masteff's code requires the date cell to be empty, it can't do the job I require as it has the date of a previous file.

What I was thinking was the following in terms of the algorithm required: A file has three date attributes in Windows; date created, date accessed and date modified. Is it not possible to write into cell H10 the date created attribute of the file? I think this might negate the requirement of an empty cell to test whether a date should be entered in the cell.

Thanks again for evernyone's help. By the bye, what text would be recommended to learn VBA, specifically for Excel?

**davesexcel** · 05-18-2010, 07:26 PM

This will insert the date Modified into H10 date created into H11

Range("H10") = Format(ActiveWorkbook.BuiltinDocumentProperties(12), "mmm dd,yyyy") 'date modified
Range("H11") = Format(ActiveWorkbook.BuiltinDocumentProperties("Creation Date"), "mmm dd,yyyy") 'date created

Check out this previous thread for other ideas

http://www.excelforum.com/excel-gene...e-in-cell.html

**Peter Visser** · 05-19-2010, 03:35 AM

Davesexcel, thank you very much - your code implemented and it works 100%. Many thanks for everyone's help.

**Peter Visser** · 05-19-2010, 05:31 AM

Hi there, it's me again. Davesexcel's code worked as mentioned, but on some of the files it pulls a date from way back when. I read up a little on "BuiltInDocumentProperties" and came upon a site that explains the date created property.

If one navigates to File, Properties and under the General tab, one will find a Date Created property which might differ from that of the Statistics tab. In my case I need to extract the Date Created under the General tab. I've found some code on the following website (http://www.j-walk.com/ss/excel/tips/tip57.htm) which apparently extracts that date, but I don't know how to get the date into a cell. The author also states that it should be entered in a module, whereas I always entered the code in Microsoft Excel Objects\ThisWorkbook. Thanks again for assisting.