VBA Multiple Spreadsheet Import

[GC1]

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.

• 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

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

Option Compare Database



Public Sub ImportExcelSpreadsheet(fileName As String, tableName As String)

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, tableName, fileName, True

End Sub

 

[CJ_London]

Ideally the new table names will include both the excel filename and workbook tab name EG: LoaderA.Frequency

you can't use transferspreadsheet to include workbook and worksheet names. You can use transfer spreadsheet to link to the workbook/sheet and then use an insert query to transfer the data plus the additional fields for workbook/sheet. To identify a specific sheet, use the range parameter of transferspreadsheet

see the links at the bottom of this thread for same questions asked

 

[GC1]

Thanks @CJ_London

I managed to pull something together from the similar posts in the end.