+ Reply to Thread
Results 1 to 8 of 8

Extract data from filename and auto-set static date

  1. #1
    Registered User
    Join Date
    05-12-2010
    Location
    South Africa
    MS-Off Ver
    Excel 2003
    Posts
    4

    Extract data from filename and auto-set static date

    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.

  2. #2
    Forum Contributor
    Join Date
    08-11-2009
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    149

    Re: Extract data from filename and auto-set static date

    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:
    Please Login or Register  to view this content.
    (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.
    Last edited by masteff; 05-12-2010 at 06:11 PM.

  3. #3
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Extract data from filename and auto-set static date

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


    Please Login or Register  to view this content.

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Extract data from filename and auto-set static date

    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?
    Entia non sunt multiplicanda sine necessitate

  5. #5
    Registered User
    Join Date
    05-12-2010
    Location
    South Africa
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Extract data from filename and auto-set static date

    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?

  6. #6
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Extract data from filename and auto-set static date

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

    Please Login or Register  to view this content.
    Check out this previous thread for other ideas

    http://www.excelforum.com/excel-gene...e-in-cell.html
    Last edited by davesexcel; 05-18-2010 at 07:37 PM.

  7. #7
    Registered User
    Join Date
    05-12-2010
    Location
    South Africa
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Extract data from filename and auto-set static date

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

  8. #8
    Registered User
    Join Date
    05-12-2010
    Location
    South Africa
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Extract data from filename and auto-set static date

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1