Capture file info, append data to table, then move file 2

[SMHSleepy]

Here's a simplified version of what I'd like to do with a click of a button. I can't even begin coding it because I don't know if it's a VBA solution, SQL, or some combination. Plus my programming skills are very limited so forgive me for not giving it a solid attempt before asking for help.

I have .rtf files in a folder called "Complete". The file names are formatted as Name_987654321.rtf, where 987654321 is a unique record number (RecNum).

I have a table (tblCompletionLog)with fields RecNum, Name, and CompletionDate.

I'd like to read each file in the folder, extract the RecNum and Name from the filename and the CompletionDate from the LastModifiedDate of the file, then append the records to tblCompletionLog.

Once the log is captured and appended to the table, I'd like to move the file to a different folder called "Archive".

I hope this makes sense and someone can offer some valuable code to help me along with it. Thanks.

 

[Andrzejek]

Here is very nice place about what you need (VBA and FSO), includes how to read files from folder, get their names, DateLastModified, etc:

http://www.techbookreport.com/tutorials/fso2.html

Have fun.

---- Andy

 

[SMHSleepy]

Thanks Andy,

I've seen similar tutorials but since FSO can only create text files, I guess I have to then import the text file into my Access table? This is an extra step which could get rather laborious. That's why I thought I'd need some SQL to append the data to my table.

 

[Andrzejek]

FSO can create text files, but also can read (any) file, including txt, or in your situation .rtf (or pdf, jpg, xls, doc, xyz, whatever...) Since you do not really need to open any files, you just need to get their name (and split it into smaller chunks) and the LastDateModified, that's all what you need to get to Insert (append) a record into your table, right?

No, do not import any files anywhere, into any table. You do not need to do that.

If you look at this tutorial you may as well try it (I think it is in Excel VBA) and you'll see all info about the files displayed on the sheet.

Have fun.

---- Andy

 

[PHV]

If you don't want to use FSO then you may condider the Dir and FileDateTime functions and the Name ... As instruction.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Andrzejek]

If, let's say, you have all your rtf files in SomeFolder on C:\ drive, try this code:
(You need to go to: Tools - References... and add: Microsoft Scripting Runtime)

Option Explicit

Private Sub DealWithFiles()
Dim fso As New FileSystemObject
Dim fls As Files
Dim f As File

Set fls = fso.GetFolder("[red]C:\SomeFolder\[/red]").Files

For Each f In fls
    MsgBox "The File Name is " & f.Name & vbNewLine & _
           "Date Last Modified is " & f.DateLastModified
Next

Set fso = Nothing

End Sub

Have fun.

---- Andy

 

[Andrzejek]

Or PHV suggestion (no reference required):

Option Explicit
Private Const MY_PATH As String = "[red]C:\SomeFolder\[/red]"

Private Sub DealWithFiles()
Dim strFile As String

strFile = Dir(MY_PATH)

Do While strFile <> ""
    MsgBox "The File Name is " & strFile & vbNewLine & _
           "Date Last Modified is " & FileDateTime(MY_PATH & strFile)
    strFile = Dir
Loop

End Sub

Have fun.

---- Andy

 

[SMHSleepy]

Thanks, I think I get that part. I know how to split the file name, get the components, and extract the LastModifiedDate.

What I need now is to append the data into my existing table (tblCompletionLog) and then move the file into a different folder.

Here's a sample of what my tblCompletionLog looks like:
RecNum Name CompletionDate
98765421 John 12/24/2011
15651254 Jane 11/26/2011

So let's say I have a file named Bob_12554715.rtf which was last modified on 5/5/2011. After the process, tblCompletionLog should now look like:
RecNum Name CompletionDate
98765421 John 12/24/2011
15651254 Jane 11/26/2011
12554715 Bob 5/5/2011

Then the file Bob_12554715.rtf should get moved into a different folder so as not to be added again to the table.

 

[PHV]

A starting point:

strSQL = "INSERT INTO tblCompletionLog(RecNum,Name,CompletionDate) VALUES(" _
 & lngRecNum & ",'" & strName & "',#" & dtDate & "#)"
DoCmd.RunSQL strSQL

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SMHSleepy]

oh, that looks good. So I can use this SQL line within the loop that reads the file names and extracts the info? I'll give it a whirl. Thanks!

 

[Andrzejek]

Then - still inside the loop - you can do:
[tt]
FileCopy sourcefile destination
Kill sourcefile
[/tt]

Have fun.

---- Andy

 

[SMHSleepy]

SWEET! Thanks, both of you.