Results 1 to 5 of 5
  1. #1
    moorecurls is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Posts
    24

    VBA code to import text Files

    In Access 365, I have the following code (which worked in previous version of Access):
    Option Compare Database
    Option Explicit


    Public Function REN()


    Const strPath As String = "O:\Accounting\load" 'Directory Path
    Dim strFile As String 'Filename
    Dim strFileList() As String 'File Array


    Dim intFile As Integer 'File Number
    Dim rs As DAO.Recordset
    'Loop through the folder & build file list
    strFile = Dir(strPath & "*.txt")
    While strFile <> ""
    'add files to the list
    intFile = intFile + 1
    ReDim Preserve strFileList(1 To intFile)
    strFileList(intFile) = strFile
    strFile = Dir()
    Wend
    'see if any files were found
    If intFile = 0 Then
    MsgBox "No files found"
    Exit Function
    End If
    'cycle through the list of files & import to Access
    'creating a new table called MyTable


    For intFile = 1 To UBound(strFileList)


    DoCmd.TransferText acImportDelim, frbubr, "RENFILES", strPath & strFileList(intFile), False



    Next
    End Function

    I'm getting a 2425 runcode error and/or MS Access No files found error; yet the text files are saved in the strPath folder. I'm not seeing what is causing the error. Thanks for your help.

  2. #2
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    First I would compile the code and see what error it gives if same as above. Also put a breakpoint at top and step through the code to see if it tells you what line has the issue. Mouse over variables to see what values they are using.

    Also on the DoCmd.TransferText acImportDelim, frbubr, "RENFILES", strPath & strFileList(intFile), False line, swap actual values for those variables as a test and see if it works.

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I ran a test with your code, changing the "Const strPath " to a path on my computer.
    I also had to delete "frbubr" from the transfer text command, because I don't know what your import specification is.

    The first time I executed the code, it bombed. The problem is a missing back slash after "load"
    Code:
    Const strPath As String = "O:\Accounting\load\"    'Directory Path


    This worked for me:
    Code:
    Public Function REN()
        Const strPath As String = "O:\Accounting\load\"    'Directory Path
        
        Dim strFile As String    'Filename
        Dim strFileList() As String    'File Array
        Dim intFile As Integer    'File Number
        ' Dim rs As DAO.Recordset           '<<-- not used so I commented it out
        
        'Loop through the folder & build file list
        strFile = Dir(strPath & "*.txt")
        While strFile <> ""
            'add files to the list
            intFile = intFile + 1
            ReDim Preserve strFileList(1 To intFile)
            strFileList(intFile) = strFile
            strFile = Dir()
        Wend
        
        'see if any files were found
        If intFile = 0 Then
            MsgBox "No files found"
            Exit Function
        End If
        
        'cycle through the list of files & import to Access
        'creating a new table called MyTable
        For intFile = 1 To UBound(strFileList)
            DoCmd.TransferText acImportDelim, frbubr, "RENFILES", strPath & strFileList(intFile), False
        Next
    
    End Function

  4. #4
    moorecurls is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Posts
    24
    Thank you. It was the extra last \ in the string. Thanks for noticing. I looked it too many times...missing it! It is working now. Thanks again.

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Excellent. Good luck with your project...

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

Similar Threads

  1. Import Specs and Text Files
    By Gloverb06 in forum Access
    Replies: 3
    Last Post: 03-15-2016, 01:43 PM
  2. VBA Code to Import Excel Files
    By Rxp in forum Programming
    Replies: 2
    Last Post: 07-26-2012, 03:57 PM
  3. Import multiple Text files with a Macro
    By ArchMap in forum Access
    Replies: 3
    Last Post: 07-01-2011, 04:56 PM
  4. Import Text Files without access
    By 95DSM in forum Import/Export Data
    Replies: 1
    Last Post: 09-10-2010, 04:36 PM
  5. Cannot import excel or text files
    By donald_s in forum Access
    Replies: 2
    Last Post: 04-13-2010, 11:48 PM

Tags for this Thread

Posting Permissions

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