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