user clicks SELECT FILE button to select file to import
filepath is stored in textbox on the form until user click IMPORT button
code will determine what kind of file (vendor / voucher)
save file to generic filename that is already attached as table
run append query to collect data
first ,create generic files of each, they get overwritting everytime.
these 2 files are attached as tables.
when user selects a file, it copies it to the generic name (here its: c:\data\vendor.csv, etc)
build 2 queries to append the external table to the main data.
user picks the file
Code:
sub btnSelectFile_click()
txtFile = UserPick1File("c:\folder\")
end sub
user click IMPORT DATA button to import
Code:
sub btnImport_click()
dim sTarget, sQry, sName
const kVEND = "vendor"
const kVOU = "voucher"
if IsNull(txtFile) then
msgbox "No File to import"
else
select case true
case instr(txtFile,kVEND )>0
sQry = "qaAddVendor"
sName = kVEND
case instr(txtFile,kVOU)>0
sQry = "qaAddVoucher"
sName = kVOU
case else
msgbox "Invalid import file"
exit sub
end select
filecopy txtFile, "c:\data\" & sName & ".csv"
docmd.setwarnings false
docmd.openquery sQry
docmd.setwarnings true
msgbox sName & "imported"
endif
end sub
put this code into a MODULE for user to pick the fule
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 11.0 Object Library, in vbe menu, TOOLS, REFERENCES
'===================
With Application.FileDialog(msoFileDialogFilePicker)
.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