Share via

Change file dialog code to display text files to import text files

Anonymous
2015-05-21T09:51:57+00:00

I found the below code at https://support.microsoft.com/en-us/kb/824272?wa=wsignin1.0  and have it displaying the file open dialog, but when I try to open a text file it says,

"Run Time Error 6014" "The RowSourceType Property must be set to 'Value List' to use this method."

And it's highlighting the line I've emphasized.

Can anyone help me modify it so it accepts text files?  I want to import text files with a file dialog box.  Thanks in advance. 

Option Compare Database

Option Explicit

Private Sub cmdFileDialog_Click()

' This requires a reference to the Microsoft Office 11.0 Object Library.

   Dim fDialog As Office.FileDialog

   Dim varFile As Variant

   ' Clear the list box contents.

   Me.FileList.RowSource = ""

   ' Set up the File dialog box.

   Set fDialog = Application.FileDialog(msoFileDialogFilePicker)

   With fDialog

      ' Allow the user to make multiple selections in the dialog box.

      .AllowMultiSelect = True

      ' Set the title of the dialog box.

      .Title = "Select One or More Files"

      ' Clear out the current filters, and then add your own.

      .Filters.Clear

      .Filters.Add "Access Databases", "*.MDB"

      .Filters.Add "Access Projects", "*.ADP"

      .Filters.Add "All Files", "*.*"

      ' Show the dialog box. If the .Show method returns True, the

      ' user picked at least one file. If the .Show method returns

      ' False, the user clicked Cancel.

      If .Show = True Then

         ' Loop through each file that is selected and then add it to the list box.

         For Each varFile In .SelectedItems

Me.FileList.AddItem varFile

         Next

      Else

         MsgBox "You clicked Cancel in the file dialog box."

      End If

   End With

End Sub

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

ScottGem 68,810 Reputation points Volunteer Moderator
2015-05-21T11:37:40+00:00

Andy, you are using the code incorrectly. If what you are trying to do is select a file for import, do not use a list box. Create a text box and name it txtFilename. Then change the line of code you have highlighted to:

Me.txtFilename = varFile

Also change AllowMultiSelect to False.

Then add a TransferText method after the End With line. In the argument for Source file put Me.txtFilename.

The code you found is used to create a list of files in a folder. But that's not what you want, you want to select a file and do something with it. 

One more thing, you can comment out the .Filters lines that refer to Access databases. You don't need those. If you want you can replace them with filters for .txt and .csv.

Was this answer helpful?

0 comments

Answer accepted by question author

Anonymous
2015-05-21T10:36:36+00:00

Ciao Andy

it seems you are populating a list box, right?

set the list box to value list.

the code looks quote correct so the problem is the control in your form.

Ciao, Sandro.

Was this answer helpful?

0 comments

8 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2015-05-21T12:32:38+00:00

    Oh my gosh!  This is incredible!  Thanks so much! Grazie, Sandro also!

    What I ended up with...

    Private Sub cmdFileDialog_Click()

    ' This requires a reference to the Microsoft Office 11.0 Object Library.

       Dim fDialog As Office.FileDialog

        Dim varFile As Variant

       ' Clear the list box contents.

        Me.txtFileName.ControlSource = ""

       ' Set up the File dialog box.

        Set fDialog = Application.FileDialog(msoFileDialogFilePicker)

        With fDialog

           ' Allow the user to make multiple selections in the dialog box.

           .AllowMultiSelect = False

          ' Set the title of the dialog box.

           .Title = "Select One or More Files"

          ' Clear out the current filters, and then add your own.

           .Filters.Clear

           .Filters.Add "Text Files", "*.txt"

           .Filters.Add "CSV Files", "*.csv"

           .Filters.Add "All Files", "*.*"

          ' Show the dialog box. If the .Show method returns True, the

           ' user picked at least one file. If the .Show method returns

           ' False, the user clicked Cancel.

           If .Show = True Then

              ' Loop through each file that is selected and then add it to the list box.

              For Each varFile In .SelectedItems

                 Me.txtFileName = varFile

              Next

           Else

              MsgBox "You clicked Cancel in the file dialog box."

              Exit Sub

           End If

        End With

        DoCmd.TransferText acImportDelim, , "ImportedFile", Me.txtFileName, True

    End Sub

    Was this answer helpful?

    0 comments
  2. Anonymous
    2015-05-21T11:20:32+00:00

    Good italian Andy, it's not so easy to write "meraviglioso"😃.

    Listen...do you prefer to enter in your table file names of selected files in your list bix or whole list box content?

    if the latter why not entering the file names at the same time you are populating the List box?

    Let us Know.

    Ciao,Sandro

    Was this answer helpful?

    0 comments
  3. Anonymous
    2015-05-21T10:58:33+00:00

    That worked great, Sandro.  Meraviglioso!

    I really want to use the contents of that list box to then always do an import to create a table (it's a comma delimited text file).  How do I refer to the contents of that list box?

    Was this answer helpful?

    0 comments