Jetbeam        
Results 1 to 12 of 12

Thread: Excel question

  1. #1
    Flashaholic* Malpaso's Avatar
    Join Date
    Feb 2005
    Location
    MA
    Posts
    506

    Default Excel question

    Is there a way to have a cell read the last date/time the spreadsheet was changed? We have a number of people who access the same spreadsheets, and the spreadsheets change numerous times throughout the day (scheduling spreadsheets). The person who does the spreadsheet doesn't want to change the date/time manually each time he updates it. I know it can be done in the header/footer, but we only look at the file, and don't want to print it just to see if it's different.
    It's not that life is so short, it's that you're dead for so long.

  2. #2
    Flashaholic* Mad1's Avatar
    Join Date
    May 2006
    Location
    UK
    Posts
    711

    Default Re: Excel question

    Well the only way I know is that if you select the folder the Excel files are in and change the view to "Details" that should tell you when the file was last modified.
    Rightclick > View > Details

    But thats probably not much help to you.
    -=- Surefire G2 Yellow -=- Mag AA Opleac Newbeam -=- Milkyspit ML-1 -=- Surefire M3T -=- Mag ROP -=- Nextorch Magic Max -=- Fenix P1 -=- Surefire E1L -=- Fenix P1D CE Q5 -=- LEDWave X-33 -=-

  3. #3
    Flashaholic*
    Join Date
    Apr 2004
    Location
    3rd Stone
    Posts
    2,241

    Default Re: Excel question

    It's been a very long time since I've played with Excel and other spreadsheets, but it seems to me that back in the dark ages it was possible to create a macro to automatically get the current date and time and write it in a subfile. Things may well have changed since then.

    edit: Having just looked at a newer version, it should be possible to have a macro autoexecute and drop the current date and time into a cell. If you want to see the last time a file was changed w/o opening the file, that's an easier matter, easily done in the gui.
    "Show them a light, and they'll follow it anywhere..."

  4. #4
    Flashaholic* Malpaso's Avatar
    Join Date
    Feb 2005
    Location
    MA
    Posts
    506

    Default Re: Excel question

    Quote Originally Posted by Mad1
    Well the only way I know is that if you select the folder the Excel files are in and change the view to "Details" that should tell you when the file was last modified.
    Rightclick > View > Details

    But thats probably not much help to you.
    That's actually part of the way to get it into the header/footer, but I can't find any way to pull that into a cell.
    It's not that life is so short, it's that you're dead for so long.

  5. #5
    *Flashaholic* greenLED's Avatar
    Join Date
    Mar 2004
    Location
    La Tiquicia
    Posts
    13,261

    Default Re: Excel question

    Quote Originally Posted by Malpaso
    Is there a way to have a cell read the last date/time the spreadsheet was changed?
    Right click on the Excel workbook icon just to the left of the File menu option, near the upper left corner of your screen. Choose the View Code option, and paste this in:

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Sheet1.[A1] = "File was last saved " & Now
    End Sub

    Got that from here. I tried it and it works. You can change "Sheet1.[A1]" for the sheet cell location you want the info to be displayed in.

    You can also use a macro to display the cell properties (I kinda like this one myself):

    Sub LastModified_LastSavedBy()
    'Keyboard Shortcut: Ctrl+Shift+L
    'put last modified date/time in current cell
    'put who last saved the file in next cell down
    On Error GoTo err_Sub

    ActiveCell.Value = "Last Modified: " & _
    ActiveWorkbook.BuiltinDocumentProperties("Last save time").Value
    ActiveCell.Offset(1, 0).Value = "Last Saved by: " & _
    ActiveWorkbook.BuiltinDocumentProperties("Last author").Value

    exit_Sub:
    On Error Resume Next
    Exit Sub

    err_Sub:

    Debug.Print "Error: " & Err.Number & " - (" & _
    Err.Description & _
    ") - Sub: LastModified_LastSavedBy - " & _
    "Module: Module2 - " & Now()
    GoTo exit_Sub
    End Sub
    Last edited by greenLED; 08-21-2006 at 02:31 PM.

  6. #6

  7. #7
    Flashaholic* Malpaso's Avatar
    Join Date
    Feb 2005
    Location
    MA
    Posts
    506

    Default Re: Excel question

    Great! Thanks guys.
    It's not that life is so short, it's that you're dead for so long.

  8. #8

    Default Re: Excel question

    The timestamp.html info might work but will take a bit of time. Using Now() will not work because that is simply the current date and will show even if no change is made.
    You could put a button on the sheet and run a macro from that which inserted the date and closed the file.

  9. #9
    *Flashaholic* greenLED's Avatar
    Join Date
    Mar 2004
    Location
    La Tiquicia
    Posts
    13,261

    Default Re: Excel question

    The macro I pasted gives you:
    'put last modified date/time in current cell
    'put who last saved the file in next cell down

    you just need to add the button routine, or program it to run on close or something like that.

  10. #10
    Flashaholic slick228's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California
    Posts
    191

    Default Re: Excel question

    Iím glad this came up. greenLED, do you know what is the macro to insert the tab (active worksheet) name in a cell? This feature is easily done on the Header and Footer, but I canít get the same information in a cell. Any help will be appreciated.

    Thanks!

  11. #11
    *Flashaholic* greenLED's Avatar
    Join Date
    Mar 2004
    Location
    La Tiquicia
    Posts
    13,261

    Default Re: Excel question

    Not off hand, but Google popped this up:
    http://www.mrexcel.com/archive2/29300/33399.htm


    ...a little more diggin':

    Function WorkSheetName() As String
    ActiveSheet.Activate
    WorkSheetName = ActiveSheet.Name
    End Function

    haven't tried that one, though.
    Last edited by greenLED; 08-21-2006 at 03:15 PM.

  12. #12
    Flashaholic slick228's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California
    Posts
    191

    Default Re: Excel question

    greenLED,

    Thank you for helping. Excel took the macro, but I canít get it to appear in a cell.

Posting Permissions

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