Vba to import all files in folder to seperate tables & name each table same as file name?

[govblaine]

looking for VBA to import all text files from a folder and use the name of the file as the table name? So text file P73004.txt is imported to access as table P73004, looping through all files in the folder and create individual tables for each one using the file name as the table name?

I have found lots of examples that merge all files in a folder into one table, even one that opens a window to pick the folder, but I have had no luck researching code to import multiple text files to multiple tables using the file name for the table name and I'm starting to wonder if its even possible.




thank you,

[June7]

It's certainly possible but a process that routinely modifies db structure is usually a poor design. Why do you feel separate tables are needed?

[ItsMe]

As for how, here is some code that might provide some ideas.

'Reference Microsoft Scripting Runtime
Dim fso As New FileSystemObject
Dim txsFileChars As TextStream
Dim objMyTextFile As File
Dim strFileName As String
Dim strFilePath As String
Dim strReadLine As String

strFilePath = "C:\Test\TextFiles\"
strFileName = "SmallTextFile.txt"   'You will want to create a Folder object and enumerate file objects

Set objMyTextFile = fso.GetFile(strFilePath & strFileName)
Debug.Print objMyTextFile.Name
Set txsFileChars = objMyTextFile.OpenAsTextStream(ForReading)
While txsFileChars.AtEndOfStream = False
strReadLine = txsFileChars.ReadLine
Debug.Print strReadLine
Wend