usage:
save a generic file: c:\folder\file2Import.xls
link it into the db as a linked table.
user clicks SELECT FILE button to select file to import
filepath is stored in textbox on the form
then this file is copied to the generic name everytime and overwrites it, c:\folder\file2Import.xls
runs the append query to collect data
user picks the file
then imports the data
user picks the file
Code:
sub btnSelectFile_click()
vFile = UserPick1File("c:\folder\")
if vFile <> "" then
filecopy vFile, "c:\folder\file2Import.xls
docmd.openquery "qaImportData"
endif
end sub
put this code into a MODULE for user to pick the file
Code:
Public Function UserPick1File(Optional pvPath)
Dim strTable As String
Dim strFilePath As String
Dim sDialog As String, sDecr As String, sExt As String
'===================
'YOU MUST ADD REFERENCE : Microsoft Office xx.0 Object Library, in vbe menu, TOOLS, REFERENCES
'===================
With Application.FileDialog(msoFileDialogFilePicker) 'msoFileDialogSaveAs
.AllowMultiSelect = False
.Title = "Locate a file to Import"
.ButtonName = "Import"
.Filters.Clear
'.Filters.Add "CSV Files", "*.csv"
'.Filters.Add "Excel Files", "*.xls;*.xlsx"
.Filters.Add "All Files", "*.*"
.InitialFileName = pvPath
.InitialView = msoFileDialogViewList 'msoFileDialogViewThumbnail
If .Show = 0 Then
'There is a problem
Exit Function
End If
'Save the first file selected
UserPick1File = Trim(.SelectedItems(1))
End With
End Function