Import Excels with table checking - access 2016

Question

I have googled everywhere but I am unable to find out to do it without rewriting all the code, is there anyway to have this code check whether the file name matches table names and if it does then clear that table and re import or if not then create a new table?

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

Thank for any help

June7 · Accepted Answer · 2020-09-10T07:08:38.777

You'll have to rewrite some. Without looping through Tables collection and testing against each name, every method seems to involve handling an error. Here is one:

Function TableExists(strTableName As String) As Boolean
On Error Resume Next
TableExists = IsObject(CurrentDb.TableDefs(strTableName))
End Function

Call the function:
If TableExists("YourTableName") = True Then

More examples in How to check if a table exists in MS Access for vb macros

Import Excels with table checking - access 2016

1 Answers1