+ Reply to Thread
Results 1 to 4 of 4

Date/Time stamp:inserting the source book date & time into the destination book

  1. #1
    Forum Contributor
    Join Date
    02-05-2014
    Location
    Bay Area
    MS-Off Ver
    Excel 2010
    Posts
    164

    Date/Time stamp:inserting the source book date & time into the destination book

    Good morning.

    I have a DESTINATION workbook with macros that pulls raw data from separate SOURCE files/workbooks. The source files/workbooks are updated daily (each time receiving a new date and time in the file name), and it's important for me to insert a date and time stamp on the destination book, so that I can track the specific source file that the macros are using. My macro automatically prompts the user to select a specific source file for use with the macros. Is there a way to insert the date and time from the chosen source file?

    Thank you, in advance.

  2. #2
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: Date/Time stamp:inserting the source book date & time into the destination book

    Greetings Tyro,

    Try the following macro, which returns a string of the form ' 2/23/2014 10:12:01 AM'. You can use the VBA 'format' command to format the string to a form that suits you. Please note that if the file is open, this function may not return the correct time (Vista 32 and 64 bit systems using Excel 2003), but may return the date and time the file was opened.

    Lewis


    NOTE that the input parameter 'sFile' is path name and file (e.g. 'c:\tmp\xyz.xls').
    Please Login or Register  to view this content.
    Last edited by LJMetzger; 02-23-2014 at 11:22 AM. Reason: Added 'sFile' note.

  3. #3
    Forum Contributor
    Join Date
    02-05-2014
    Location
    Bay Area
    MS-Off Ver
    Excel 2010
    Posts
    164

    Re: Date/Time stamp:inserting the source book date & time into the destination book

    Hi Lewis.

    Thank you very much for the feedback and code. As a novice, I'm going to ask two naive questions.

    First, does a function, such as this one, get embedded into my existing code (never worked with a function)? If so, I'm not even sure where to put the function. My existing code automatically prompts the user to select a source book to upload (raw data), and then that data from that source book is transferred to the destination book. I'd like the date/time stamp to be inserted on the destination sheet where the data is placed. I'll play around with it, but I thought I'd ask, in case you know the answer.

    Second, if "sFile" is the path and file name, it would seem that this code would need to change for each source book uploaded to the destination (e.g., daily, when the data is updated).

    Maybe I'm just not thinking clearly this morning. Regardless, I sincerely appreciate the help!

    Jeff

  4. #4
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: Date/Time stamp:inserting the source book date & time into the destination book

    Hi Jeff,

    The best way to answer your questions is probably by example.
    On Sheet 'Main' try the following two Macros, by clicking on the appropriate Shape:
    a. ExampleOneOfUsingFileModifiedDate()
    b. ExampleTwoOfUsingFileModifiedDate()

    There are several other examples in the file that you may find useful.

    Lewis

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Adding time (more than 24 hours) to date time stamp
    By sidapt in forum Excel General
    Replies: 6
    Last Post: 01-06-2014, 04:08 AM
  2. [SOLVED] Splitting a time stamp into one column for time and another for date
    By Ellsworth Smith in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-19-2013, 02:41 AM
  3. [SOLVED] Populate time based on condition from Book 1 to Book 2....
    By [email protected] in forum Excel Formulas & Functions
    Replies: 29
    Last Post: 12-12-2012, 01:52 PM
  4. [SOLVED] Extract a time from a cell with date and time stamp and then perform calculations
    By Marcos Aristotelous in forum Excel General
    Replies: 3
    Last Post: 10-31-2012, 04:36 AM
  5. Show zero values w.r.t Date & Time in sheet2 of same work book
    By hina chingini in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-30-2012, 03:02 AM

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