Import text file allowing user to browse for file

Posted on 2009-02-09
Last Modified: 2013-12-25
The goal is to import a text file automatically after user has selected a file!

I already have an import spec.

Do I need to open the import dialog box in a different fashion than simply initiating the import text wizard with DoCmd.RunCommand acCmdImport?

Also, how do I pass both the table name and file path to the DoCmd.TransferText command?

The table name needs to be exactly what the text file name is minus the file extension .txt.
The path name is normally the same path, with only the name of the text file changing, but I would like to make it dynamic in the event it does change completely.

I only need text files to be the filter on the dialog box, however.

For example, the text file will be called BK02-05.txt for February 5ths version, and I have been using the import wizard to apply my import spec on this day after day.  However, someone is taking over this function, and it would be beneficial to make it easier for them.

I hope I was detailed enough.  I am either looking for the code for the complete process or code to pass these parameters to the TransferText command from the acCmdImport command if that's even possible?

The former of the two options would probably be the best!  Thanks for any assistance you can provide!  If 200 points is not high enough, please advise and I can add more points if need be.

I have scoured the questions already asked, but the code is too advanced for me to apply to my situation specifically or the question has been answered a long time ago and the link is no longer available.


Question by:blang476
    LVL 92

    Expert Comment

    by:Patrick Matthews
    Hello blang476,

    The snippet below has code from a recent project (anaonymized, of course!) showing one way to do it.  In
    this particular instance, I loaded the data into a "temp" table, ran some validation checks, and then used
    INSERT / UPDATE queries to move the imported data into the "permanent" table.


    Private Sub cmdImportFile_Click()
        Dim fd As FileDialog
        Dim FilePath As String
        Set fd = Application.FileDialog(msoFileDialogFilePicker)
        With fd
            .Title = "Select import file"
            .Filters.Add "Text files", "*.txt"
            If .Show = -1 Then FilePath = .SelectedItems(1)
        End With
        Set fd = Nothing
        If FilePath = "" Then
            MsgBox "No file selected", vbExclamation, "Aborting"
            Exit Sub
        End If
        With DoCmd
            .SetWarnings False
            .RunSQL "DELETE * FROM Table_Name"
            .TransferText acImportDelim, "Specification_Name", "Table_Name", FilePath, True
            'more code here for validation queries
            .SetWarnings True
        End With
        MsgBox "Done importing file"
    End Sub

    Open in new window


    Author Comment


    thank you very much for replying.  Code definitely makes sense to the naked eye, however, I am getting an error that FileDialog is not a user-defined data type.  I tried [FileDialogImport Function] to no avail, so maybe you could enlighten me as to what I am doing wrong?

    Author Comment

    Ok, well, obviously, the second half of my response tells you I don't really know waht I'm doing with respect to vba.  Do we have to set up that data type, and, if so, I haven't the foggiest clue what code to use in order to do that?
    LVL 92

    Accepted Solution

    You may need to add a reference to the Microsoft Office library (Tools|References in the VBA Editor's

    Author Closing Comment

    Thank you very much for your help!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
    When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now