Importing Files W/system Date?

[CATT1]

Hi;

I have a function that I created with a lot of help. It creates an array from files in a specified directory, dumps these filenames into an Access table, renames each file with the correct extension and then imports each file. After this I have some cleanup queries.


The original goal of this process is to rename and import files on a weekly basis through a form. File date and import date would be assessed at the time that the file is renamed and imported. This part works perfect.

Here is the problem, there are appx 250 files from the past year that I need imported, which is why I have the array. But I need the filename attached to the group of records, and the date that it was imported into the PC. Can this be done? I tried doing this one by one, but every time I mess up, I have to start from the beginning.


Please help.


Here’s is the code for the array.

Function M_ALL_LOANSA()

Dim SQL As String
Dim DirectoryFiles() ' Array that contains dir listing
Dim count

ffile = DIR("C:\WINPATH\RECEIVE-BILL\E3*.REC")
Do While ffile <> ""
If ffile <> "" Then
ReDim Preserve DirectoryFiles(count)
DirectoryFiles(count) = ffile
count = count + 1

End If

ffile = DIR()

Loop
DoCmd.SetWarnings False
For Each itm In DirectoryFiles

TMP1 = Split(itm, ".")

SQL = "INSERT INTO FILENAMES (FILENAME) VALUES ('" & TMP1(0) & "')"
DoCmd.RunSQL SQL

FileCopy "c:\winpath\receive-bill\" & itm, "C:\WINPATH\receive-bill\" & TMP1(0) & ".txt"

DoCmd.TransferText acImportFixed, "hesc-down Import Specification", "ALL-LOANS", "C:\WINPATH\receive-bill\" & TMP1(0) & ".txt", False, ""

'DoCmd.OpenQuery "Q-EMPTY OUT FILENAMES", acViewNormal, acEdit
'DoCmd.Close acQuery, "Q-EMPTY OUT FILENAMES"

' CLEAN ALL LOANS

DoCmd.OpenQuery "Q-CLEAN ALL LOANS", acViewNormal, acEdit
DoCmd.Close acQuery, "Q-CLEAN ALL LOANS"

' APPEND TO ALL LOANS FORMATED
DoCmd.OpenQuery "Q-ALL LOANS APPEND TABLE", acViewNormal, acEdit
DoCmd.Close acQuery, "Q-ALL LOANS APPEND TABLE"
DoCmd.OpenQuery "Q-ALL LOANS EMPTY OUT TABLE", acViewNormal, acEdit
DoCmd.Close acQuery, "Q-ALL LOANS EMPTY OUT TABLE"

Next
M_ALL_LOANS_Exit:
Exit Function
M_ALL_LOANS_Err:
MsgBox Error$
Resume M_ALL_LOANS_Exit

End Function

 

[noccy]

If you create the columns you need in your table, and then use ADO to add the filenames... This might get you in on the right track

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

Set cn = CurrentProject.Connection

'Find all records where Filename is blank (that means that all old records must have the filename right)

rs.Open "SELECT * FROM TheTableToPutFilenamesIn WHERE Filename IS Null", cn, adOpenStatic, adLockOptimistic

Do While rs.EOF = False
With rs
!Filename = itm 'you set the value in the Field to be your filename
!Timestamp = Now
.Update
.MoveNext
End With
Loop


'All records missing filename will now be updated


noccy

 

[CATT1]

Hi:

With the help of my son (who is a VB fanatic) I had been able to rename files, import them and copy the name of the file into the table with the file records, but I still needed the file creation date.

He suggested I go to VBcode.com and do a search, well I found code that lets me choose the path and it downloads all the file names and properties into a csv file. This VB code gave me everything I needed in minutes (Ihave been trying to get filenames with creation dates for over a week).

I did have to look through appx 16 pages of lists of code to find it but it was worth it. I do think you need VB installed in your pc, though. If anybody needs it I will be glad to look for it again.


Judy


P.S. Thank You noccy for all your help.

 

[ghudson]

but I still needed the file creation date.

The VBA FileDateTime function will do what you want.

FileDateTime(pathname)

Do While rs.EOF = False
With rs
!Filename = itm 'you set the value in the Field to be your filename
!Timestamp = Now 
!FileCreationDate = FileDateTime("PathName")
.Update
.MoveNext
End With
Loop

Check the Access help files for more info on using the FileDateTime function.

HTH