I would save the workbook to a generic file name so it can be saved to the same place everytime. This way the queries always point to the same worksheet. Attach all the worksheets as tables.
Then build the 4 queries to import the data from all tables.
put all these in an IMPORT MACRO....say mImportAllXLSheets
So the user will pick the file,
the file will save to the generic name (therefore already be connected to the db)
the macro runs
done...all data imported.
Below is the UserSelectFile code: (you can hookin your macro)
Code:
' by ranman256
Private Const kGENERICxl = "c:\abc\generic.xlsx"
'-----------------------
Sub ImportXLData()
'-----------------------
Dim vFile
MsgBox "YOU MUST ADD REFERENCE : Microsoft Office Object Library", , "Then delete this msg"
vFile = UserPick1File("c:\startpath")
If vFile <> "" Then
FileCopy vFile, kGENERICxl
If MsgBox("Begin Import", vbQuestion + vbYesNo, "Confirm") = vbYes Then
docmd.runmacro "mImportAllXLdata"
End If
End If
End Sub
'-----------------------
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 x.x 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 "Excel Files", "*.xls;*.xlsx"
.Filters.Add "All Files", "*.*"
.InitialFileName = "c:\"
.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