FileDateTime

[D]

Hi:

I would like to check the files (two Excel files) creation date before
importing with Command96.

Can you please tell me how do I make the below working: I get Error 53 =file
not found

Thank you,

Dan

Dim mydate
mydate = Date
If FileDateTime("U:\TREASURY\PUBLIC\TBM Middle
Office\ROBONEILL\2008\Autorec\4Alice\$.xls") = mydate Then


Command96_Click
Else
DoCmd.Quit
end if
end sub

 

[Jerry Whittle]

First off it could be a word-wrap problem causing the file not found error.

Next the Date and file date/time stamp will probably never match as the
stamp has a time component.

I don't think that $.xls is a valid file name. Are you trying to get at a
particular worksheet in a spreadsheet?

Last instead of running what is behind Command96, you would be better off
putting the code for in in the module.

Below is something to play with that (1) might help a word-wrapping problem,
(2) takes care of the time problem in the file stamp. I'll let you put in the
proper file locations and code to do the import.

Function fFileDateTime()

Dim myfile As String
myfile = "C:\Temp\Test.txt"

If Fix(FileDateTime(myfile)) = Date Then
MsgBox ("Date Matches")
Else
MsgBox ("Date Does Not Match")
End If
End Function

 

[D]

Thank you Jerry!

I will try; there are two different excel files; the location is fine...

I will let you know.

Thanks again,

Dan

 

[D]

Hi Jerry:

Does not work!?

Please see below the code;

Thanks,

Dan

*********

Function fFileDateTime()

Dim myfile As String
myfile = "U:\TREASURY\PUBLIC\TBM Middle
Office\ROBONEILL\2008\Autorec\4Alice\" & Format(Date, "mmmm yyyy") & "\$.xls"

If Fix(FileDateTime(myfile)) = Date Then
'MsgBox ("Date Matches")
Dim strImportSetUp As String
Dim strImportPath As String

strImportSetUp = "U:\TREASURY\PUBLIC\TBM Middle
Office\ROBONEILL\2008\Autorec\4Alice\" & Format(Date, "mmmm yyyy") & "\$.xls"


strImportPath = Replace(strImportSetUp, "$", "mthly_journals")
DoCmd.TransferSpreadsheet acImport, , "Journals", strImportPath, True
strImportPath = Replace(strImportSetUp, "$", "mthly_journals_99124")
DoCmd.TransferSpreadsheet acImport, , "Journals", strImportPath, True

Else
DoCmd.Quit
End If
End Function

 

[D]

Hi Jerry:

You were right about the file name; the below looks like is working.

THANKS,

Dan

****
Function fFileDateTime()

Dim myfile As String
Dim myfile1 As String

myfile = "U:\TREASURY\PUBLIC\TBM Middle
Office\ROBONEILL\2008\Autorec\4Alice\" & Format(Date, "mmmm yyyy") &
"\mthly_journals.xls"
myfile1 = "U:\TREASURY\PUBLIC\TBM Middle
Office\ROBONEILL\2008\Autorec\4Alice\" & Format(Date, "mmmm yyyy") &
"\mthly_journals_99124.xls"

If Fix(FileDateTime(myfile)) And Fix(FileDateTime(myfile1)) = Date Then
'MsgBox ("Date Matches")
Dim strImportSetUp As String
Dim strImportPath As String

strImportSetUp = "U:\TREASURY\PUBLIC\TBM Middle
Office\ROBONEILL\2008\Autorec\4Alice\" & Format(Date, "mmmm yyyy") & "\$.xls"


strImportPath = Replace(strImportSetUp, "$", "mthly_journals")
DoCmd.TransferSpreadsheet acImport, , "Journals", strImportPath, True
strImportPath = Replace(strImportSetUp, "$", "mthly_journals_99124")
DoCmd.TransferSpreadsheet acImport, , "Journals", strImportPath, True

Else
DoCmd.Quit
End If
End Function