I have a workbook which runs a macro to import various files and run
reconciliations between them. Problem is that those files need to be current
- ie. the VBA needs to test the files BEFORE the import process starts and if
the source files don't have today's date then the user needs to be notified
and the process prevented from running.

I think I can do it with adding a reference to Windows Scripting or
something, but how do I guarantee that my user(s) will also have that
reference?

Any help gratefully received!
Thanks
HMA

The VBA function FileDateTime returns the later of the file creation
date and file modification date. You can test a file and if the
FileDateTime is earlier than today, don't open it. E.g.,

Sub AAAA()
Dim FName As String
Dim LastModDate As Date
Dim WB As Workbook
FName = "C:\Book2.xls"
LastModDate = FileDateTime(FName)
If CLng(LastModDate) = CLng(Now) Then
Debug.Print "Created or modified today"
Set WB = Workbooks.Open(FName)
Else
Debug.Print "Modified earlier than today"
End If
End Sub


Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Fri, 28 Nov 2008 15:09:01 -0800, HelenA
wrote:

I have a workbook which runs a macro to import various files and run
reconciliations between them. Problem is that those files need to be current
- ie. the VBA needs to test the files BEFORE the import process starts and if
the source files don't have today's date then the user needs to be notified
and the process prevented from running.

I think I can do it with adding a reference to Windows Scripting or
something, but how do I guarantee that my user(s) will also have that
reference?

Any help gratefully received!
Thanks
HMA

Chip

Thank you so so much for such a quick response :) I really appreciate this.
I'll give it a go and post back with results later this am.

Thanks again

If CLng(LastModDate) = CLng(Now) Then

should really be
If Int(LastModDate) = Int(Now) Then

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Fri, 28 Nov 2008 17:24:09 -0600, Chip Pearson
wrote:

The VBA function FileDateTime returns the later of the file creation
date and file modification date. You can test a file and if the
FileDateTime is earlier than today, don't open it. E.g.,

Sub AAAA()
Dim FName As String
Dim LastModDate As Date
Dim WB As Workbook
FName = "C:\Book2.xls"
LastModDate = FileDateTime(FName)
If CLng(LastModDate) = CLng(Now) Then
Debug.Print "Created or modified today"
Set WB = Workbooks.Open(FName)
Else
Debug.Print "Modified earlier than today"
End If
End Sub


Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Fri, 28 Nov 2008 15:09:01 -0800, HelenA
wrote:

I have a workbook which runs a macro to import various files and run
reconciliations between them. Problem is that those files need to be current
- ie. the VBA needs to test the files BEFORE the import process starts and if
the source files don't have today's date then the user needs to be notified
and the process prevented from running.

I think I can do it with adding a reference to Windows Scripting or
something, but how do I guarantee that my user(s) will also have that
reference?

Any help gratefully received!
Thanks
HMA

I've got this so far and it's dying with a "File not Found" error which is
weird. When I watch the FName variable it's finding the first file, and then
tells me File not Found! Any ideas?? Thanks in advance :)

Dim FName As String
Dim LastModDate As Date
Dim wbk As Workbook
Dim RunAnyway As VbMsgBoxResult

OKDate = True

MyDate = Now
MyDay = day(MyDate)
MyMth = Month(MyDate)
MyYr = Year(MyDate)

Application.ScreenUpdating = False
Application.DisplayAlerts = False

PathName = "H:\Architectural\Kagan Recons\"
ChDir PathName

FName = Dir(PathName)

Do While FName < ""
LastModDate = FileDateTime(FName) <----- Dies here
If CLng(LastModDate) < CLng(MyDate) Then
RunAnyway = MsgBox(FName & " has not been saved today. Do you
want to continue anyway? (Your reconciliation file will still have today's
date", vbOKCancel)
If RunAnyway = vbCancel Then
OKDate = False
Exit Sub
End If
End If
FName = Dir
Loop

End Sub


"Chip Pearson" wrote:


If CLng(LastModDate) = CLng(Now) Then

should really be
If Int(LastModDate) = Int(Now) Then

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Fri, 28 Nov 2008 17:24:09 -0600, Chip Pearson
wrote:

The VBA function FileDateTime returns the later of the file creation
date and file modification date. You can test a file and if the
FileDateTime is earlier than today, don't open it. E.g.,

Sub AAAA()
Dim FName As String
Dim LastModDate As Date
Dim WB As Workbook
FName = "C:\Book2.xls"
LastModDate = FileDateTime(FName)
If CLng(LastModDate) = CLng(Now) Then
Debug.Print "Created or modified today"
Set WB = Workbooks.Open(FName)
Else
Debug.Print "Modified earlier than today"
End If
End Sub


Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Fri, 28 Nov 2008 15:09:01 -0800, HelenA
wrote:

I have a workbook which runs a macro to import various files and run
reconciliations between them. Problem is that those files need to be current
- ie. the VBA needs to test the files BEFORE the import process starts and if
the source files don't have today's date then the user needs to be notified
and the process prevented from running.

I think I can do it with adding a reference to Windows Scripting or
something, but how do I guarantee that my user(s) will also have that
reference?

Any help gratefully received!
Thanks
HMA

An update : I've added a line just into the start of the loop:

FName = PathName & FName

But now, it's just reporting on the same file over and over - it's not
looping through the file. Once again, any suggestions would be helpful.
Thanks to Chip for the steer in the right direction to start with!

I think you have two choices, but delete your latest change in both cases:

Choice 1:
First, delete the "ChDir PathName" line.

Second, try using this line:
LastModDate = FileDateTime(PathName & FName)

Choice 2:
Insert a line before this:
ChDir PathName
to:
chdrive pathname
ChDir PathName

and you should be able to use:
LastModDate = FileDateTime(FName)

===
I'd use choice 1.

The chdir won't work if you ever decide to use a UNC path
(\\someserver\somefolder\) instead of the mapped drive.



HelenA wrote:

An update : I've added a line just into the start of the loop:

FName = PathName & FName

But now, it's just reporting on the same file over and over - it's not
looping through the file. Once again, any suggestions would be helpful.
Thanks to Chip for the steer in the right direction to start with!

--

Dave Peterson

I don't see a file name specified, just the path. Did I miss it?

"HelenA" wrote:

I've got this so far and it's dying with a "File not Found" error which is
weird. When I watch the FName variable it's finding the first file, and then
tells me File not Found! Any ideas?? Thanks in advance :)

Dim FName As String
Dim LastModDate As Date
Dim wbk As Workbook
Dim RunAnyway As VbMsgBoxResult

OKDate = True

MyDate = Now
MyDay = day(MyDate)
MyMth = Month(MyDate)
MyYr = Year(MyDate)

Application.ScreenUpdating = False
Application.DisplayAlerts = False

PathName = "H:\Architectural\Kagan Recons\"
ChDir PathName

FName = Dir(PathName)

Do While FName < ""
LastModDate = FileDateTime(FName) <----- Dies here
If CLng(LastModDate) < CLng(MyDate) Then
RunAnyway = MsgBox(FName & " has not been saved today. Do you
want to continue anyway? (Your reconciliation file will still have today's
date", vbOKCancel)
If RunAnyway = vbCancel Then
OKDate = False
Exit Sub
End If
End If
FName = Dir
Loop

End Sub


"Chip Pearson" wrote:


If CLng(LastModDate) = CLng(Now) Then

should really be
If Int(LastModDate) = Int(Now) Then

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Fri, 28 Nov 2008 17:24:09 -0600, Chip Pearson
wrote:

The VBA function FileDateTime returns the later of the file creation
date and file modification date. You can test a file and if the
FileDateTime is earlier than today, don't open it. E.g.,

Sub AAAA()
Dim FName As String
Dim LastModDate As Date
Dim WB As Workbook
FName = "C:\Book2.xls"
LastModDate = FileDateTime(FName)
If CLng(LastModDate) = CLng(Now) Then
Debug.Print "Created or modified today"
Set WB = Workbooks.Open(FName)
Else
Debug.Print "Modified earlier than today"
End If
End Sub


Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Fri, 28 Nov 2008 15:09:01 -0800, HelenA
wrote:

I have a workbook which runs a macro to import various files and run
reconciliations between them. Problem is that those files need to be current
- ie. the VBA needs to test the files BEFORE the import process starts and if
the source files don't have today's date then the user needs to be notified
and the process prevented from running.

I think I can do it with adding a reference to Windows Scripting or
something, but how do I guarantee that my user(s) will also have that
reference?

Any help gratefully received!
Thanks
HMA

The file name comes from the Dir function - all is well; I've posted the
final code below - might not be pretty, but hey, it works!!

Sub CheckFileDates()
'
' To check files and be sure they're saved with the current date
' Adapted from code posted in MSDN Newsgroup when I couldn't figure out how
to do this without Windows script!
' Created by HelenA 28th November 2008
'
Dim FName As String
Dim FToTest As String
Dim LastModDate As Date
Dim RunAnyway As VbMsgBoxResult

OKDate = False

MyDate = Now
MyDay = day(MyDate)
MyMth = Month(MyDate)
MyYr = Year(MyDate)

Application.ScreenUpdating = False
Application.DisplayAlerts = False

PathName = "H:\Architectural\Kagan Recons\"
ChDir PathName

FName = Dir(PathName)

Do While FName < ""
FToTest = PathName & FName
LastModDate = FileDateTime(FToTest)
If Int(LastModDate) < Int(MyDate) Then
RunAnyway = MsgBox(FName & " has not been saved today. Do you
want to continue anyway? (Your reconciliation file will still have today's
date.)", vbOKCancel)
If RunAnyway = vbCancel Then
OKDate = False
Exit Sub
End If
OKDate = True
Exit Sub
Else
OKDate = True
End If
FName = Dir
Loop
End Sub