Link to home
Start Free TrialLog in
Avatar of Kevin Buckland
Kevin BucklandFlag for United States of America

asked on

Excel formula or script to verify file date modified is today

I receive a daily email which has an Excel attachment that (unfortunately) contains a date with =now().  So whenever you open it, it always shows the current date.  I have a macro to save the file to a directory when it comes into my inbox.

Since the excel file comes through with =now(), I need a formula or script in another excel file I use to validate that we received today's file.  I assume the best way of going about this is to have a formula or script in Excel display the current date modified and perhaps conditional formatting to highlight if the modified date is <> today.  

Alternatively I think I would need a script to open the daily file ("Funding Requirements.XLS") copy | paste special | values so that the =now() formula is no longer contained in the

Thank you.
Avatar of Shums Faruk
Shums Faruk
Flag of India image

Hi Kevin,

I can make the vba to Open | Copy | Paste Special | Values.

Please provide the cell reference, sheet reference of Funding Requirement.xls
Try below, change the cell reference and sheet reference as needed:
Sub ConvertDate()
Dim TrgtWs As Worksheet
Dim FolderPath As String
Dim TrgtBook As Workbook
Dim Filter As String
Dim Caption As String
Dim TrgtFileName As String
Dim CurrSh As Worksheet

With Application
        .ScreenUpdating = False
        .DisplayStatusBar = True
        .StatusBar = "!!! Please Be Patient...Updating Records !!!"
        .EnableEvents = False
        .Calculation = xlManual
End With
Set CurrSh = ThisWorkbook.ActiveSheet

FolderPath = Application.ThisWorkbook.Path
ChDir FolderPath

' make weak assumption that active workbook is the target
Set TargetWorkbook = Application.ThisWorkbook

' get the wiz workbook
Filter = "Text files (*.xl*),*.xl*"
Caption = "Please Select an input file "

TrgtFileName = Application.GetOpenFilename(Filter, , Caption)

Set TrgtBook = Application.Workbooks.Open(TrgtFileName, Format:=xlDelimited, Local:=True)
Set TrgtWs = TrgtBook.Sheets(1) 'Change the sheet name if needed
TrgtWs.Range("A2").Value = TrgtWs.Range("A2").Value 'Change cell reference where you want to change the date as value

'Close Trgt Workbook
Application.DisplayAlerts = False
TrgtBook.Close savechanges:=True
Application.DisplayAlerts = True
With Application
    .ScreenUpdating = True
    .DisplayStatusBar = True
    .StatusBar = False
    .EnableEvents = True
    .Calculation = xlAutomatic
End With
End Sub

Open in new window

Please find attached for your reference
Before running above code, please change the cell reference, after changes as you run above code, it will prompt you to select the file, which needs changes. It will open the file, convert date to value, close the file and save the changes.

Hope this helps.
Avatar of Kevin Buckland


This is the file that I receive in my email.  It has =now() in cell A16 ("FUNDING REQUIREMENT" worksheet).  

A macro in outlook gets that email and the attachment daily and saves it to I:\Warrants\

If something broke and I by chance didn't receive the email with the updated file, I wouldn't want an old file to be updated with today's date.  But if it's just copying and pasting values, and the date had already been hardcoded then it's probably fine.  But that is why I was thinking of having a check within my other spreadsheet that checks the actual modified date of the actual file. If that's possible.
Which is the code you have to save the file to a directory when it comes into your inbox?
Another possibility would be, who ever sends the file through email, ask them to saveas with the date at the end.
This is the code we have running in outlook:

Public Sub saveWarrantAttachtoFile(itm As Outlook.MailItem)
Dim objAtt As Outlook.Attachment
Dim saveFolder As String

saveFolder = "I:\Warrants\"
     For Each objAtt In itm.Attachments
            If InStr(objAtt.DisplayName, "Funding Requirement") Then
                objAtt.SaveAsFile saveFolder & "\" & objAtt.DisplayName
            End If

       Set objAtt = Nothing
End Sub

Open in new window

Avatar of Shums Faruk
Shums Faruk
Flag of India image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi Kevin,

Did you check? it will save as your attachment with the email received date, so you wouldn't need to worry about NOW() formula within Excel File.
I haven't had time to check, hopefully will be able to test this out tomorrow.  Sorry for the delay.
I didn't forget about this, the person that needs to help me with has been out of the office. Thank you!
FYI, this didn't work for me yet because I had errors but it may be because of my limited abilities.  The IT guy that I need help from is on extended leave.  I'm good closing it out and awarding points to Shums.  Thank you for your help, Shums!