Good Day,
I have followed a video and successfully created the code to import a spreadsheet into my DB. Being new to VBA I don’t really know what is going on and therefore unclear which elements of the code I need to change to meet my requirements. I hope someone could explain what is happening at each stage for me to understand better?
My requirement is to be able to import an excel file with 4 separate tabs and create a table for each excel worksheet. I have researched this on here and other places I just cannot work out where the changes go without creating an error.
I have followed a video and successfully created the code to import a spreadsheet into my DB. Being new to VBA I don’t really know what is going on and therefore unclear which elements of the code I need to change to meet my requirements. I hope someone could explain what is happening at each stage for me to understand better?
My requirement is to be able to import an excel file with 4 separate tabs and create a table for each excel worksheet. I have researched this on here and other places I just cannot work out where the changes go without creating an error.
- The excel file name is likely to change, therefore the picker option I have works fine.
- The workbook tab names and structure will always be the same.
- Ideally the new table names will include both the excel filename and workbook tab name EG: LoaderA.Frequency
Code:
Option Compare Database
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"
diag.Filters.Clear
diag.Filters.Add " Excel Spreadsheets", "*.xls, *.xlsx"
If diag.Show Then
For Each item In diag.SelectedItems
Me.txtfileName = item
Next
End If
End Sub
Private Sub btnImportSpreadsheet_Click()
Dim FSO As New FileSystemObject
If Nz(Me.txtfileName, "") = "" Then
MsgBox "Please select a file"
End If
If FSO.FileExists(Nz(Me.txtfileName, "")) Then
ImportExcelSpreadsheet.ImportExcelSpreadsheet Me.txtfileName, FSO.GetFileName(Me.txtfileName)
Else
MsgBox "File Not Found"
End If
End Sub
Code:
Option Compare Database
Public Sub ImportExcelSpreadsheet(fileName As String, tableName As String)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, tableName, fileName, True
End Sub