Import Exel and Add Time Stamp

[Guest]

I hope someone can help me out...


I'm trying to import an excel file that is updated daily, same format,
naming convention, except that date and time are added to the naming
convention...

I want to import and an add a field that either collect date modified or
date within the file... Preferrably by date modified... Code I'm using is
below.. I appreciate the help

Option Compare Database

Function ImportCSAT()

Dim strFile As String
Dim strPath As String
Dim stDocName As String

'File path '
strPath = "F:\Shared\PFFS_IT\Prod_Env\TMG_Inbound_Files\Customer_Service"
'Change the default directory to the file path
'
ChDir strPath
'Find the firstxls file
'
strFile = Dir("WCARE_DAILY_CONTACT_LOG_2007**.xls")
stDocName = ("She*")

'Loop through the string & import the files
'
Do While Len(strFile) > 0
DoCmd.TransferSpreadsheet acImport, , "xls_PFFS_Direct", strFile, True
'delete the file (consider moving it to an Archive folder instead.)
' Kill strPath & "/" & strFile
'Call Dir to get the next file
'
strFile = Dir
Loop

End Function

 

[Douglas J. Steele]

Assuming xls_PFFS_Direct has a date field (named DateModified) in it, try
adding the following line after the line that uses the TransferSpreadsheet
method:

CurrentDb.Execute "UPDATE xls_PFFS_Direct " & _
"SET DateModified = " & _
Format(FileDateTime(strPath & "\" & strFile), "\#yyyy\-mm\-dd
hh\:nn\:ss\#") & _
" WHERE DateModified IS NULL", dbFailOnError

 

[Guest]

Doug

Thanks for the reply... So after the initial run,,can I reference the last
data modified so not to duplicate the file... Unfortunely, the file being
imported is read only so I can't kill the file after data run... Thanks


If not I can create multiple primary keys.... Thanks

Ramon

 

[Guest]

UPDATED CODE: Thanks again



Option Compare Database

Function ImportCSAT()

Dim strFile As String
Dim strPath As String
Dim stDocName As String

'File path '
strPath = "F:\Shared\PFFS_IT\Prod_Env\TMG_Inbound_Files\Customer_Service"

'Change the default directory to the file path
'
ChDir strPath

'Find the firsttext file
'
strFile = Dir("WCARE_DAILY_CONTACT_LOG_2007**.xls")
stDocName = ("She*")

'Loop through the string & import the files
'
Do While Len(strFile) > 0


DoCmd.TransferSpreadsheet acImport, , "xls_PFFS_Direct", strFile, True
CurrentDb.Execute "UPDATE xls_PFFS_Direct " & "SET DateModified = " & _
Format(FileDateTime(strPath & "\" & strFile), "\#yyyy\-mm\-dd\#") & "
WHERE DateModified IS NULL", dbFailOnError




'delete the file (consider moving it to an Archive folder instead.)
' Kill strPath & "/" & strFile

'Call Dir to get the next file
'
strFile = Dir
Loop

End Function

 

[Douglas J. Steele]

The following will let you check whether you already have entries in the
table for the Last Modified date of the given file, and only import if you
haven't:

Do While Len(strFile) > 0
If DCount("*", "xls_PFFS_Direct", _
"DateModifed = " & Format(FileDateTime(strPath & "\" & strFile),
"\#yyyy\-mm\-dd\#")) = 0 Then

DoCmd.TransferSpreadsheet acImport, , "xls_PFFS_Direct", strFile, True
CurrentDb.Execute "UPDATE xls_PFFS_Direct " & "SET DateModified = " & _
Format(FileDateTime(strPath & "\" & strFile), "\#yyyy\-mm\-dd\#") & _
" WHERE DateModified IS NULL", dbFailOnError
End If
strFile = Dir
Loop

 

[Guest]

Doug

One last question... I'm getting the dreaded "Run Time Error" 2001 " You
Canceled the previous operation"

I've checked the spelling of the column and table, etc... Can't get it to
run... Thanks again...

Do While Len(strFile) > 0
If DCount("*", "xls_PFFS_Direct", "DateModifed =" &
Format(FileDateTime(strPath & "\" & strFile), "\#yyyy\-mm\-dd\#")) = 0 Then

DoCmd.TransferSpreadsheet acImport, , "xls_PFFS_Direct", strFile, True
CurrentDb.Execute "UPDATE xls_PFFS_Direct " & "SET DateModified = " & _
Format(FileDateTime(strPath & "\" & strFile), "\#yyyy\-mm\-dd\#") & _
" WHERE DateModified IS NULL", dbFailOnError
End If
strFile = Dir
Loop

 

[Douglas J. Steele]

Sorry, I've got no solution for you.

Have you confirmed that Format(FileDateTime(strPath & "\" & strFile),
"\#yyyy\-mm\-dd\#") is returning what it should? If it isn't, the where
clause in the DCount statement could be raising that error.

 

[Guest]

I'll check it out... When I get an answer I'll post it...
Thanks again