Monday, September 17, 2012 6:31 PM
I currently receive several daily text files from one of our vendors that I import into a Access 2007 database table. All these text files have the same fields. I also have a separate table with the file names of these text files. Currently, I import these manually using the import button on the ribbon.
What I want to do is use a VB code that compares the name of the text files (all of which are located in the same directory) to the list of file names in my table. Then import any text files with file names not in my table. I have a file import specification saved that can be used in this process.
Can this be done? Please let me know if I can provide more information.
- Edited by KevinATF Monday, September 17, 2012 6:32 PM
Monday, September 17, 2012 6:59 PMModerator
In VBA, loop over the files in the directory, with Dir "C:\path\*.txt" as example (using a wildcard to identify all possible files). Inside the loop, check if the file is in the list of already imported data, or not, and if it is not, make a copy of that file ( FileCopy ) into file name which is the one used by your import specs. Import the data. Append the orignal file name to the list of files already handled. Continue the loop.
Monday, September 17, 2012 8:31 PM
Thanks for your reply. I'm still new at VB. Do you have an example code you can share with me?
Wednesday, September 19, 2012 6:13 AMModerator
Welcome to the MSDN forum.
Please refer to the following code:
Sub DailyImport() Dim rs As Recordset Dim sql As String Dim sPath As String sPath = Dir$("D:\00-Access\TestDailyImport\" & "*.txt", vbNormal) Do Until sPath = "" sql = "SELECT * From tbFiles Where FileName='" & sPath & "'" Set rs = CurrentDb.OpenRecordset(sql) 'Check if the file name existing or not If rs.RecordCount = 0 Then 'Import the data from sPath with the specification DoCmd.TransferText acImportDelim, "Daily Import Specification", "tbData", _ "D:\00-Access\TestDailyImport\" & sPath, True 'Insert the file name into the table sql = "Insert Into tbFiles (FileName) Values('" & sPath & "')" CurrentDb.Execute sql Set rs = Nothing End If sPath = Dir$ Loop End Sub
tbData - The table store the data from the text file
tbFiles - The table store the name of the imported text file
Have a nice day.
MSDN Community Support | Feedback to us
- Marked As Answer by KevinATF Wednesday, September 19, 2012 2:47 PM
Wednesday, September 19, 2012 2:48 PM
Thanks. This works great! Kevin
Saturday, September 29, 2012 4:00 AM
Maybe something like this:
Change the following line:
strFile= Dir(strPath & "\*.xls")
strFile = Dir(strPath & "\*.txt")
- Edited by ryguy72 Saturday, September 29, 2012 4:01 AM