Results 1 to 3 of 3

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

  1. #1
    govblaine is offline Novice
    Windows 10 Access 2013
    Join Date
    Dec 2015
    Posts
    3

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

    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,

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    46,888
    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?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,863
    As for how, here is some code that might provide some ideas.
    Code:
    '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

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 08-20-2014, 02:00 AM
  2. Replies: 3
    Last Post: 03-06-2014, 02:53 PM
  3. Replies: 1
    Last Post: 10-02-2013, 10:32 AM
  4. Replies: 17
    Last Post: 06-04-2013, 06:36 PM
  5. Replies: 7
    Last Post: 11-20-2012, 04:08 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums