I was hoping to use this as well, I have a similar problem. 20 - 30 .txt files to import to a specific table each day. But I am using Access 2000 and Office 2000. I don't have the references available as per the attached jpg.
Any way that I can still do this?
Debug on the above code is throwing up;
Dim fs as FileSearch
Compile error
User-defined type not defined
Hi,
I have a different method which works a treat, and doesn't need as much code. I currently import on a dialy basis around 300 text files. I have created import specifications where required, but essentially all of my text files conform to the exact same layout and therefore I do not require any more than 3 import specifications.
So if all your text files conform to the same layout this will be easy for you.
The first part is time consuming, but once done and assuming there will not be any changes to text file formats, it's there for good.
using one of your text files to manually import the data and create the import specification your code might look something like this:
DoCmd.TransferText acImportFixed, "Import Spec 63 Rev 2", "tblNewImportSpec63Rev_2", "Z:\aga.fof\aga.txt", False, ""
Docmd.TransferText acimportFixed identifies that its a text file and that it is in Fixed Width format [this also works for CSV format]
"Import Spec 63 Rev 2" is the name I have given to my import specification.
"tblNewImportSpec63Rev_2" is the name of the table in which I want to import the data to.
"Z:\aga.fof\aga.txt", False," is the location of where the text file can be found, which in this case is my Z Drive and Folder Named : aga.fof and the name of the text file: aga.txt
This process assumes that your folder and text files will always have the same name.
I hope this is of some assistance.
John