Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Mar 2003
    Location
    Summerville, South Carolina, USA
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Importing Text files > 1KB (Access2000)

    I have the below code that works great. However, I want to add 3 things. 1) Only import files that are greater than 1 KB and 2) Give me a message box that tells me how many were imported in. 3) Be able to prompt the user to import a range of dates (i.e. from 7/1/04 - 7/31/04) based on the file date.

    FolderName = "elearn01fdkClaimsClaims ManualTracking for Claims Manual"

    FileName = Dir(FolderName & "" & txtBegin & "*.txt")


    While FileName <> ""
    DoCmd.TransferText acImportDelim, "SurveySpec", "tblHolding1", FolderName & "" & FileName
    FileName = Dir()
    Wend

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing Text files > 1KB (Access2000)

    This is probably best done through the FileSystemObject; this is part of the scripting library. On some corporate networks, access to the scripting library is limited or disabled. See if you can set a reference to Microsoft Scripting Runtime in Tools | References...
    The FileSystemObject has lots of methods to work with files and folders, and you can easily get at the properties of files, such as DateLastModified and Size.

  3. #3
    Star Lounger
    Join Date
    Mar 2003
    Location
    Summerville, South Carolina, USA
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Text files > 1KB (Access2000)

    I am pretty sure that I have access to the scripting library. However, I am not sure where to start. I am assuming that I need to create a public module that will identify the text files I need to import. Then create a variable that will allow them to enter a date from a form and call to the public function to only import those dates. I may also be way off. Let me know.

    Thanks.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing Text files > 1KB (Access2000)

    Here is code you can use as a starting point. It doesn't do anything useful, that is up to you. It assumes the code is run from a form with text boxes txtStartDate and txtEndDate. It looks for .txt files in a folder C:Test with last modified date between the supplied dates. You can modify all this as needed, and you can generalize it if you wish.

    Sub TestLoop()
    Dim fso As New Scripting.FileSystemObject
    Dim fld As Scripting.Folder
    Dim fil As Scripting.File
    Dim lngNum As Long

    On Error GoTo ErrHandler

    Set fld = fso.GetFolder("C:Test")
    For Each fil In fld.Files
    If Right(fil.Name, 3) = "txt" And fil.Size > 1024 And _
    fil.DateLastModified >= Me.txtStartDate And fil.DateLastModified <= Me.txtEndDate Then
    ' Insert your useful code here
    Debug.Print fil.Name
    lngNum = lngNum + 1
    End If
    Next fil

    MsgBox lngNum & " files processed.", vbInformation

    ExitHandler:
    Set fil = Nothing
    Set fld = Nothing
    Set fso = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •