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!
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.
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.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi Kevin,
I can make the vba to Open | Copy | Paste Special | Values.
Please provide the cell reference, sheet reference of Funding Requirement.xls
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:
Change-Date-as-Value-in-Closed-Work.xlsm
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
Change-Date-as-Value-in-Closed-Work.xlsm
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.
Hope this helps.
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
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
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
Next
End Sub
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
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.
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial