Excel formula or script to verify file date modified is today

Kevin Buckland
Kevin Buckland used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

Commented:
Hi Kevin,

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

Please provide the cell reference, sheet reference of Funding Requirement.xls
ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

Commented:
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.Activate
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
CurrSh.Activate
CurrSh.Range("A1").Select
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
Change-Date-as-Value-in-Closed-Work.xlsm
ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

Commented:
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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Kevin BucklandInvestment Officer

Author

Commented:
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.
Funding-Requirement.XLS
ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

Commented:
Which is the code you have to save the file to a directory when it comes into your inbox?
ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

Commented:
Another possibility would be, who ever sends the file through email, ask them to saveas with the date at the end.
Kevin BucklandInvestment Officer

Author

Commented:
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
     Next
End Sub

Open in new window

Managing Director/Excel VBA Developer
Distinguished Expert 2018
Commented:
Please try below, which will save your attachment with email received date:
Public Sub saveWarrantAttachtoFile(itm As Outlook.MailItem)

Dim objAtt As Outlook.Attachment
Dim saveFolder As String
Dim fso As Object
Dim oldName

Dim file As String
Dim DateFormat As String
Dim newName As String

Dim enviro As String
enviro = CStr(Environ("USERPROFILE"))
saveFolder = enviro & "I:\Warrants\"

Set fso = CreateObject("Scripting.FileSystemObject")
On Error Resume Next

 For Each objAtt In itm.Attachments
     file = saveFolder & objAtt.DisplayName
     objAtt.SaveAsFile file
     
     Set oldName = fso.GetFile(file)
     DateFormat = Format(oldName.DateLastModified, "yyyy-mm-dd ")
     newName = DateFormat & objAtt.DisplayName
     oldName.Name = newName
    
     Set objAtt = Nothing
 Next
  
 Set fso = Nothing
 End Sub

Open in new window

ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

Commented:
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.
Kevin BucklandInvestment Officer

Author

Commented:
I haven't had time to check, hopefully will be able to test this out tomorrow.  Sorry for the delay.
Kevin BucklandInvestment Officer

Author

Commented:
I didn't forget about this, the person that needs to help me with has been out of the office. Thank you!
Kevin BucklandInvestment Officer

Author

Commented:
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!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial