Delete table if exist before create new one

xdenama

New Member
Joined
Feb 12, 2016
Messages
39
Office Version
  1. 365
How to delete excel table if exist before create new one. Below is my macro code.

VBA Code:
Sub Macro1()
'
' Macro1 Macro



'

    ActiveWorkbook.Queries.Add Name:="Data", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Excel.CurrentWorkbook(){[Name=""PreData""]}[Content]," & Chr(13) & "" & Chr(10) & "    FillDown = Table.FillDown(Source,{""Column2""})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    FillDown"
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Data;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Data]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Data"
        .Refresh BackgroundQuery:=False
    End With
End Sub
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Watch MrExcel Video

Forum statistics

Threads
1,118,355
Messages
5,571,701
Members
412,413
Latest member
dvprajapati
Top