Access Macros Help

[Tom]

Can I write a macro to import multiple text files?
Files are named like

FL0422
FL0423
FL0424

etc.

I would like to improt all files named FL* in one effort instead of one at a time.
Possible?

[linrom1]

Ill post toningt how you can do that.

[linrom1]

This is code that that you can copy and paste into a module that can be executed by a macro. It imports all files that it finds in a directory with .csv extension and deletes the files.

1. Open Access and choose insert a module.

2. Paste the following code into the module.
-----------------------------------------
Function ImportFiles()
On Error GoTo Err_F

Dim fso As Object, fol As Object, fils As Object, f As Object
Dim strPathFile As String, strFile As String, strPath As String, strSpec As String
Dim strTable As String, ynFieldName As Boolean
' use ynFieldName to tell TransferText whether .csv file has field names (True) or not (False)
ynFieldName = False
' strPath is the path to the folder in which the files will be placed - use your names
strPath = "D:\Programs\your directory\"
' strSpec is name of import specification
strSpec = "your_specfile"
'strTable is name of table to which file is to be imported
strTable = "Your_Table"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fol = fso.GetFolder(strPath)
Set fils = fol.Files
DoCmd.SetWarnings False
For Each f In fils ' checks all files in the folder specified above
If Right(f.Name, 4) = ".csv" Then ' found a ".csv" file
strPathFile = strPath & f.Name ' set entire path&file name
' import .csv file
DoCmd.TransferText acImportDelim, strSpec, strTable, strPathFile, ynFieldName
' delete the file that was just imported (leave out this step if you want to manually delete the file)
f. delete
End If
Next f

Exit Function

Err_F:
MsgBox Err.Number & " " & Err.Description

End Function
--------------------------------------
Note the specification file can be set up by using Import Text Wizard and choosing advanced tab, it tells Access what fields to import so that imported files match your table structure.

3. Create a new macro and scroll down to RunCode under Action heading, and enter ImportFiles as a Function Name.

[Skanoochies]

HUH?:D

[railbird]

HUH?

ROTFLMAO , My thoughts exactly!

[Tom]

Linrom 1...Thanks you very much! Wow. I never would have guessed that! LOL
Seriously, thanks for the help. I owe you one.
A tip o' the hat to you!

[Zaf]

Watch out TOM, that resembles that new melt my hard drive virus code :D :D :D LOL

ZAFONIC

[JustRalph]

Looks like the right thing to me........I found some very similar stuff researching this for tom via some newsgroups....Linrom beat me to it........looks good........