[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 484
  • Last Modified:

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

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
    Loop
    DoCmd.OpenQuery "BLappendtoScanManifest", acViewNormal, acReadOnly
0
iainmacleod
Asked:
iainmacleod
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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:

http://msdn.microsoft.com/en-us/library/office/bb243865(v=office.12).aspx

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
         Next
      Else
         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:

http://msdn.microsoft.com/en-us/library/office/bb214141(v=office.12).aspx

The basic syntax for that is:

DoCmd.TransferText acImportFixed", "", "YourTableName", sFile
0
 
iainmacleodAuthor Commented:
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.Clear
      .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
      Else
         MsgBox "No file selected!"
      End If
   End With
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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