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.
Kevin BucklandInvestment OfficerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ShumsExcel & VBA ExpertCommented:
Hi Kevin,

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

Please provide the cell reference, sheet reference of Funding Requirement.xls
ShumsExcel & VBA ExpertCommented:
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
ShumsExcel & VBA ExpertCommented:
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 Business Longevity with As-A-Service

Using the as-a-service approach for your business model allows you to grow your revenue stream with new practice areas, without forcing you to part ways with existing clients just because they don’t fit the mold of your new service offerings.

Download eBook
Kevin BucklandInvestment OfficerAuthor 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
ShumsExcel & VBA ExpertCommented:
Which is the code you have to save the file to a directory when it comes into your inbox?
ShumsExcel & VBA ExpertCommented:
Another possibility would be, who ever sends the file through email, ask them to saveas with the date at the end.
Kevin BucklandInvestment OfficerAuthor 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

ShumsExcel & VBA ExpertCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ShumsExcel & VBA ExpertCommented:
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 OfficerAuthor Commented:
I haven't had time to check, hopefully will be able to test this out tomorrow.  Sorry for the delay.
Kevin BucklandInvestment OfficerAuthor 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 OfficerAuthor 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!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.