• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 446
  • Last Modified:

Import text file allowing user to browse for file

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.

Thanks,

Brent
0
blang476
Asked:
blang476
  • 3
  • 2
1 Solution
 
Patrick MatthewsCommented:
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.

Regards,

Patrick
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

0
 
blang476Author Commented:
matthewspatrick,

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?
0
 
blang476Author Commented:
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?
0
 
Patrick MatthewsCommented:
You may need to add a reference to the Microsoft Office library (Tools|References in the VBA Editor's
menu).
0
 
blang476Author Commented:
Thank you very much for your help!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now