Show dialog box to choose a file for import in to MS Access

Posted on 2012-09-11
Last Modified: 2012-12-13
I have been using some code to locate a csv file for import into an Access application. (See below)

This code is fine as long as the file names have known names. I need to give the user the ability to choose a file name that can be imported. as i may have multiple files starting with the same numbers/names.

If Me.shipperid = 20000043 Then
    strFileName = Dir("c:\scansystem\import\20000043*.csv")
    Do Until strFileName = ""
    FileCopy "c:\scansystem\import\" & strFileName, "c:\scansystem\import\history\" & strFileName
    FileCopy "c:\scansystem\import\" & strFileName, "c:\scansystem\import\BL\portal return file.csv"
    Kill "c:\scansystem\import\20000043*.csv"
    strFileName = Dir
    DoCmd.OpenQuery "BLappendtoScanManifest", acViewNormal, acReadOnly
Question by:iainmacleod
    LVL 84

    Accepted Solution

    Do you mean you want the user to select a specific file, and then Import it? Your code above doesn't seem to do that - it just copies and deletes files, and opens a query.

    Here's a nice writeup on using the FileDialog to locate a file:

    The section of that code shown below is where the filename is returned. If you just want to grab that value and not display it, then do this (mods are in BOLD)

    '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.
          Dim sFile As String
          If .Show = True Then
             'Loop through each file selected and add it to the list box.
             For Each varFile In .SelectedItems
                sFile = varFile
             MsgBox "You clicked Cancel in the file dialog box."
          End If

    The variable "sFile" contains the file the user chose, so you could then use that for TransferText if need be:

    The basic syntax for that is:

    DoCmd.TransferText acImportFixed", "", "YourTableName", sFile

    Author Comment

    Thanks LSM, that is really helpful and i am making headway.
    Could you shed some light on how I specify the path name as I am getting a 2522 error.

    Dim Cdb As DAO.Database, cresponseNoRec As String, strmsgNoRec As String, sFile As String, fDialog As Office.FileDialog, varFile As Variant

       Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
       With fDialog
          .Title = "Please select the file you would like to import"

          'Clear out the current filters, and add our own.
          .Filters.Add "CSV", "*.csv"
          .Filters.Add "All Files", "*.*"
          .Filters.Add "Access Databases", "*.MDB; *.ACCDB"

         If .Show = True Then
              sFile = varFile
              DoCmd.TransferText acImportDelim, , ("scanmanifest"), sFile, False
             MsgBox "No file selected!"
          End If
       End With

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    779 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

    11 Experts available now in Live!

    Get 1:1 Help Now