none
VB Import text file into Access 2007 table

    Question

  • Hello,

    I'm looking for a VB code that imports a text file into a Access 2007 table. The text file can be located in different directories. So I need a Open window to display to navigate to the appropiate directory. The VB code will also need to utilize a previously saved import specification for the text file being imported.

    Thanks in advance for your help.

    Kevin

     

    Wednesday, May 11, 2011 2:14 AM

Answers

  • Hi Kevin,

     

    By using the FileDialog() its hard to automate the next step. If you can specify file name (ends with "code.txt"), you got automate this by doing something like below code. Copy below code into a New Module and Save it. Then under a CommandButton or Macro;

    ImportTXT("C:\YourDirectory\",True

       Note: Last option "True" if you want to search through subdirectories as well.

          Note: Make sure you change the SpecificationName and TableName into yours.

             Note: Add Microsoft Scripting Runtime to your References.

     

    Public Function ImportTXT(SourceFolderName As String, IncludeSubfolders As Boolean)
    Dim FSO As New FileSystemObject, SourceFolder As Folder, Subfolder As Folder, FileItem As File
    Dim lngCount As Long, strSQL As String

    On Error GoTo Err_Handler
       
        Set SourceFolder = FSO.GetFolder(SourceFolderName)
       
        For Each FileItem In SourceFolder.Files
            Select Case Right(FileItem.Name, 8)
                Case "code.txt"          

                       DoCmd.TransferText acImportFixed, "YourSpecificationName", "YourTableName", FileItem, True
                         
                   '  Optional for deleting file after Importing

                   'Kill FileItem
                End Select
                                           
            lngCount = lngCount + 1
        Next FileItem
       
        If IncludeSubfolders Then
            For Each Subfolder In SourceFolder.SubFolders
                ImportTXT Subfolder.Path, True
            Next Subfolder
        End If
       
         
        Set FileItem = Nothing
        Set SourceFolder = Nothing
      

    Exit_Handler:
        Exit Function

    Err_Handler:
        MsgBox "Error " & Err.Number & ": " & Err.Description
        Resume Exit_Handler

    End Function

     

    HTH,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    • Marked as answer by Bruce Song Friday, May 27, 2011 10:55 AM
    Wednesday, May 11, 2011 2:32 AM