Import Exel and Add Time Stamp


G

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
 
Ad

Advertisements

D

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
 
G

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
 
G

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
 
D

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
 
G

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
 
Ad

Advertisements

D

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.
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top