HI TheDBGuy
I have been working on this and set the database up better to allow me to utilise temporary tables in the front end as you suggested.
Now I have a simple form to import an excel spreadsheet into the database, which I would eventually like to use to setup a match query.
I have the following code set up, which works well. The only thing is that the code inserts an additional table.
I'm thinking that it would be easier to append to an existing table and then set up a way to clear the table after each use. I also worry that it would fill the front end up too much if I leave it in the current state. If I have it set up to append to an existing table, knowing the name of the table would make it easier to set up the query.
What would the be the best way to go?
Option Compare Database
Option Explicit
Private Sub btnBrowse_Click()
Dim diag As Office.FileDialog
Dim item As Variant
Set diag = Application.FileDialog(msoFileDialogFilePicker)
diag.AllowMultiSelect = False
diag.Title = "Please select an Excel Spreadsheet (.xlsx files only)"
diag.Filters.Clear
diag.Filters.Add "Excel Spreadsheets", "*.xls, *.xlsx"
If diag.Show Then
For Each item In diag.SelectedItems
Me.TextFileName = item
Next
End If
End Sub
Private Sub btnImportSpreadsheet_Click()
Dim FSO As New FileSystemObject
If Nz(Me.TextFileName, "") = "" Then
MsgBox "Please select a file!"
Exit Sub
End If
If FSO.FileExists(Nz(Me.TextFileName, "")) Then
ExcelImport.ImportExcelSpreadsheet Me.TextFileName, FSO.GetFileName(Me.TextFileName)
Else
MsgBox "File not found!"
End If
End Sub
Private Sub cmdClose_Click()
DoCmd.Close
End Sub
