excel - Auto refresh Pivot tables on data change -
i have worksheet data
web scrapes data based on dynamic link. there pivottable
pivot tables based on data
worksheet.
data
worksheet uses following macro , clears contents of cells before web scraping new updated data. data updated every 1 minute.
i have following code refresh pivot tables on data update. thisworkbook.worksheets("pivottable").pivottables("pivottable1").refreshtable
since data takes 20 seconds complete updating, there no data (as cell contents cleared first) pivot table refresh. so, error.
data
uses following code update data:
with thisworkbook.worksheets("data").querytables.add(connection:= _ "<url redacted>", destination:=thisworkbook.worksheets("data").range("$a$1")) .name = "datapull" .fieldnames = true .rownumbers = false .filladjacentformulas = false .preserveformatting = true .refreshonfileopen = false .backgroundquery = true .refreshstyle = xlinsertdeletecells .savepassword = false .savedata = true .adjustcolumnwidth = true .refreshperiod = 0 .webselectiontype = xlentirepage .webformatting = xlwebformattingnone .webpreformattedtexttocolumns = true .webconsecutivedelimitersasone = true .websingleblocktextimport = false .webdisabledaterecognition = false .webdisableredirections = false .refresh backgroundquery:=true ' .delete end
i have tried updating .refreshstyle = xlinsertdeletecells
.refreshstyle = xloverwritecells
. overwrites cells until end of rows of new data. if new data (number of rows) less old data's rows, old data rows @ end not deleted. want data latest update kept.
how auto refresh pivot tables based on above conditions?
just set .backgroundquery = false
query performed synchronously (meaning, wait data loaded before doing pivot refresh).
Comments
Post a Comment