Hi All, I've used the macro recorder to record the loading of a power query to table and I'm curious if the last line of code is redundant?
It appears to me that the .Refresh BackgroundQuery property is set to False at the last line within the initial 'With' / 'End With' section, and the table appears to have loaded at End With, but then the recorder included a subsequent line
This command appears to reload the table all over again effectively doubling how long it takes...? Is this line redundant? can it be removed?
Thanks,
Joe
Code:
With Sheets("Temp").ListObjects.Add(SourceType:=0, Source:= _ "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=DateCompleted;Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [DateCompleted]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = False
.ListObject.DisplayName = "DateCompleted"
.Refresh BackgroundQuery:=False
End With
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
It appears to me that the .Refresh BackgroundQuery property is set to False at the last line within the initial 'With' / 'End With' section, and the table appears to have loaded at End With, but then the recorder included a subsequent line
Code:
Selection.ListObject.QueryTable.Refresh BackgroundQuery:= False
This command appears to reload the table all over again effectively doubling how long it takes...? Is this line redundant? can it be removed?
Thanks,
Joe