asked on

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

ASKER CERTIFIED SOLUTION

Scott McDaniel (EE MVE )

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

iainmacleod

ASKER

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