Test Date of External Data Source
 

Sub test1()
Dim fs, f
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile("c:\temp\abc.txt")
MsgBox (f.datelastmodified)
End Sub

"RayportingMonkey" wrote:

I have created a number of automated reports in Excel and now need to pass
the actual report generation on to a team of Analysts.

There is a mix of automated and manual extracts that comprise the source for
these reports.

Is there a way I can test to see if the "Date Modified" for the source
file(s) is = to TODAY?

Thank you for your assistance!

Test Date of External Data Source
 

That didn't seem to work...

Here's the appropriate fragment of the formula as I used it:
IF(" & f.datelastmodified & "<TODAY()

Note that the system inserted a space on eaither side of the &

It just throws me back to debug...

"Joel" wrote:

Just add quotes before the variable and the the &. Do the same thing after
the variable. See below:

ActiveCell.FormulaR1C1 = "=IF(" & _
f.datelastmodified & "(TODAY()+0.99999),""Future
Data?"",(IF(" & f.datelastmodified & "<TODAY(),""Old Data"",""Validated"")))"

"RayportingMonkey" wrote:

Yeah - I was able to make good use of it already...

What I was not able to do was combine it into a formula in a maco, like:

ActiveCell.FormulaR1C1 = "=IF(f.datelastmodified(TODAY()+0.99999),""Fu ture
Data?"",(IF(f.datelastmodified<TODAY(),""Old Data"",""Validated"")))"

How would I go about doing that?

"Joel" wrote:

The code actually produces a serial number date in the cell.

Sub test1()
Dim fs, f, f1, fc, s
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile("c:\temp\abc.txt")
MsgBox (f.datelastmodified)
Range("A1") = f.datelastmodified
End Sub"RayportingMonkey" wrote:

This works - But - Since I want to run logic in the worksheet based on this
data, how do I redirect the response to a cell instead of a Message Box?

"Joel" wrote:

Sub test1()
Dim fs, f
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile("c:\temp\abc.txt")
MsgBox (f.datelastmodified)
End Sub

"RayportingMonkey" wrote:

I have created a number of automated reports in Excel and now need to pass
the actual report generation on to a team of Analysts.

There is a mix of automated and manual extracts that comprise the source for
these reports.

Is there a way I can test to see if the "Date Modified" for the source
file(s) is = to TODAY?

Thank you for your assistance!

Test Date of External Data Source
 

The code actually produces a serial number date in the cell.

Sub test1()
Dim fs, f, f1, fc, s
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile("c:\temp\abc.txt")
MsgBox (f.datelastmodified)
Range("A1") = f.datelastmodified
End Sub"RayportingMonkey" wrote:

This works - But - Since I want to run logic in the worksheet based on this
data, how do I redirect the response to a cell instead of a Message Box?

"Joel" wrote:

Sub test1()
Dim fs, f
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile("c:\temp\abc.txt")
MsgBox (f.datelastmodified)
End Sub

"RayportingMonkey" wrote:

I have created a number of automated reports in Excel and now need to pass
the actual report generation on to a team of Analysts.

There is a mix of automated and manual extracts that comprise the source for
these reports.

Is there a way I can test to see if the "Date Modified" for the source
file(s) is = to TODAY?

Thank you for your assistance!

Test Date of External Data Source
 

the today function doesn't work on my PC. Wrote my own. This code works.
Should give you a good example of what you are trying to do. the problem
may just be the two extra commas and close parethesis that is your problem.

Sub test()

mydate = DateValue("1/1/07")
today = DateSerial(Year(Now()), Month(Now()), Day(Now()))

myformula = "=if(" & mydate & "< " & today & ",,)"

Range("A1").Formula = myformula

End Sub


"RayportingMonkey" wrote:

That didn't seem to work...

Here's the appropriate fragment of the formula as I used it:
IF(" & f.datelastmodified & "<TODAY()

Note that the system inserted a space on eaither side of the &

It just throws me back to debug...

"Joel" wrote:

Just add quotes before the variable and the the &. Do the same thing after
the variable. See below:

ActiveCell.FormulaR1C1 = "=IF(" & _
f.datelastmodified & "(TODAY()+0.99999),""Future
Data?"",(IF(" & f.datelastmodified & "<TODAY(),""Old Data"",""Validated"")))"

"RayportingMonkey" wrote:

Yeah - I was able to make good use of it already...

What I was not able to do was combine it into a formula in a maco, like:

ActiveCell.FormulaR1C1 = "=IF(f.datelastmodified(TODAY()+0.99999),""Fu ture
Data?"",(IF(f.datelastmodified<TODAY(),""Old Data"",""Validated"")))"

How would I go about doing that?

"Joel" wrote:

The code actually produces a serial number date in the cell.

Sub test1()
Dim fs, f, f1, fc, s
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile("c:\temp\abc.txt")
MsgBox (f.datelastmodified)
Range("A1") = f.datelastmodified
End Sub"RayportingMonkey" wrote:

This works - But - Since I want to run logic in the worksheet based on this
data, how do I redirect the response to a cell instead of a Message Box?

"Joel" wrote:

Sub test1()
Dim fs, f
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile("c:\temp\abc.txt")
MsgBox (f.datelastmodified)
End Sub

"RayportingMonkey" wrote:

I have created a number of automated reports in Excel and now need to pass
the actual report generation on to a team of Analysts.

There is a mix of automated and manual extracts that comprise the source for
these reports.

Is there a way I can test to see if the "Date Modified" for the source
file(s) is = to TODAY?

Thank you for your assistance!

Test Date of External Data Source
 

This works - But - Since I want to run logic in the worksheet based on this
data, how do I redirect the response to a cell instead of a Message Box?

"Joel" wrote:

Sub test1()
Dim fs, f
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile("c:\temp\abc.txt")
MsgBox (f.datelastmodified)
End Sub

"RayportingMonkey" wrote:

I have created a number of automated reports in Excel and now need to pass
the actual report generation on to a team of Analysts.

There is a mix of automated and manual extracts that comprise the source for
these reports.

Is there a way I can test to see if the "Date Modified" for the source
file(s) is = to TODAY?

Thank you for your assistance!