Hi,
I have a working database for a while and haven't been able to work this out, but I need to be able to import and delete tables if the tables already exist.
Code:
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"
diag.Filters.Clear
diag.Filters.Add "Excel Spreadsheets", "*.xls, *.xlsx, *.xlsm"
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 FSO.FileExists(Nz(Me.txtFileName, "")) Then
ImportExcelSpreadsheet Me.txtFileName, FSO.GetFileName(Me.txtFileName)
ElseIf Nz(Me.txtFileName, "") = "" Then
MsgBox "Please select a file!", vbExclamation
Else
MsgBox "File not found!", vbExclamation
End If
End Sub
Public Sub ImportExcelSpreadsheet(Filename As String, TableName As String)
On Error Resume Next
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, TableName, Filename, True
If Err.Number = 3125 Then
If vbOK = MsgBox(Err.Description & vbNewLine & vbNewLine & "Skip column header and continue?", vbExclamation + vbOKCancel, "Error with Excel Column header") Then
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, TableName, Filename, False
MsgBox "Done", vbInformation
End If
Exit Sub
ElseIf Err.Number <> 0 Then
MsgBox Err.Number & ":" & Err.Description, vbCritical
Exit Sub
End If
MsgBox "Upload Complete", vbInformation
End Sub
I have tried but failed quite a bit trying to add this into the code.
If anyone has an already existed database that imports Excels and deletes tables if they match the excel file names that would be absolutely amazing.
Below are the screen shots of my excel file name and the database I currently have.
Thanks
Dan