The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.
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.
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
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.
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
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
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.
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!
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.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
I can make the vba to Open | Copy | Paste Special | Values.
Please provide the cell reference, sheet reference of Funding Requirement.xls