Linking all files

[DaLiMan]

Hi,

I'm looking for a way to automate the task of linking files (especially NEW files in the directory)
Is there a way to create a macro or something that checks all .txt files in a specific directory and compares if they are already linked.
If they are not linked do this automatically in a new table in the Access Database.

Sinds I have no way of telling when a new file is added in this directory I need to check all by hand. Now I'm not very happy with this as you can understand. I really hope someone can help me with this.

 

[mdmilner]

Remove those lines.
I left in some commented out lines (that's the ' character) and that particular line isn't needed at all for it to run.

Mike

 

[DaLiMan]

Wow, it works.
This is really cool Mike.

Only I found the existing tables are being duplicated like:
Test.xls
Test.xls1
Test.xls2
etc...

And can this also be done with TXT files.
I run into the problem that the script didn't recognize TXT files.
I tried to change "DoCmd.TransferSpreadsheet" in the Import function but due to my limited understanding without succes.

You're really helping me out here !!! Thanx !!!

 

[mdmilner]

Text file import uses a slightly different command.

spec is an import specification.
tblA is the name of hte table (after import in Access)
dstFile is the full path to the file

Import specifications are a wonderful thing in Access. With a Fixed import, it allows you to specify the exact size and composition of a given field. You can force dates into Text, for example, without allowing Access to 'guess'

They're built by importing a text file manually, and (BEFORE DONE) hitting the Advanced button on the bottom left hand corner of the Import Wizard. You can then save a specification file (or resave over old names or delete ones no longer in use)

Sub ImportRoutine(spec As String, tblA As String, dstFile As String)

  DoCmd.TransferText acImportFixed, spec, tblA, dstFile

End Sub

 

[DaLiMan]

OK, so TXT files are a different ballgame......

I'll just keep it at spreadsheets which work fine.
Thanx Mike for all your time and help.

Greetings,
Daniel