Importing several txt files programatically in Access 2003

[Yamou]

Hi,

MY QUESTION: HOW DO I REWRITE MY MACRO so that it cycles through the 16
results of a query and uploads the 16 resulting text files to my database in
one run.
1. My currently working macro (compacted here for space) to import ONE file
at a time from a specific directory to the open database:
....
FilePath = "T:\InternationalPrograms\Finance\2008-10
NAIROBI-Transactions(BON).txt"
DoCmd.TransferText acImportDelim, "Transactions Import Specification", _
"tbl_PR_Transactions", FilePath
GoTo ExitSub
....

The ""T:\InternationalPrograms\Finance\" portion of the FilePath remains
constant for all reports. The "2008-10 NAIROBI-Transactions(BON).txt" text is
created for 16 subs in a query. The callenge: splitting the FilePath String
in two so that the second part is pulled automatically for all records in my
query and each time uploaded to my database in one run of my macro.

Note: I am an occasional VBA writer, just smart enough to adapt simple codes
to my needs. Thanks for your help!

 

[Ken Snell \(MVP\)]

Hi,

MY QUESTION: HOW DO I REWRITE MY MACRO so that it cycles through the 16
results of a query and uploads the 16 resulting text files to my database
in
one run.
1. My currently working macro (compacted here for space) to import ONE
file
at a time from a specific directory to the open database:
...
FilePath = "T:\InternationalPrograms\Finance\2008-10
NAIROBI-Transactions(BON).txt"
DoCmd.TransferText acImportDelim, "Transactions Import Specification", _
"tbl_PR_Transactions", FilePath
GoTo ExitSub
...

The ""T:\InternationalPrograms\Finance\" portion of the FilePath remains
constant for all reports. The "2008-10 NAIROBI-Transactions(BON).txt" text
is
created for 16 subs in a query. The callenge: splitting the FilePath
String
in two so that the second part is pulled automatically for all records in
my
query and each time uploaded to my database in one run of my macro.

Note: I am an occasional VBA writer, just smart enough to adapt simple
codes
to my needs. Thanks for your help!

Perhaps you can adapt the code that I have here for importing all files from
a single directory folder (the code is written using TransferSpreadsheet,
but is easily adaptable for TransferText):

Import Data from All EXCEL Files in a single Folder via TransferSpreadsheet
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#ImpFolderFiles

 

[Yamou]

Thanks for your response. There is only one problem: my folder contains a
large number of text files with a specific naming convention: yyyy-mm
ProjectName-Item(Subproject).txt. I don't want the macro to keep uploading
all data all every time I run it. I just want it to focus on specific
combinations of yyyy-mm and subproject. In other words, I received 16 reports
in October and I only want to load those and not the ones from previous
months. Is there a solution to do this while keeping ALL files in the same
folder or do I have to move files monthly in to that same folder only?

 

[Ken Snell \(MVP\)]

You can modify that code to bring in only certain files. Change this line of
code:

strFile = Dir(strPath & "*.txt")


to something like this:

Dim strFile1 As String, strFile2 As String
strFile1 = "2008-12"
strFile2 = "(NameOfSubproject)"
strFile = Dir(strPath & strFile1 & "*" & strFile2 & "*.txt")

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/