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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Watch MrExcel Video

Forum statistics

Threads
1,118,658
Messages
5,573,451
Members
412,529
Latest member
cTatch
Top