VBA QueryTable.Refresh

0
votes

I've created a simple spreadsheet that has 3 worksheets that are utilized in Data Queries & Connections processing. They are accessed in the m code with statements like "Source = Excel.CurrentWorkbook(){[Name="TPCT_Static"]}[Content]")". The results of the processing are ultimately connected to a table in the same workbook. When I execute some simple VBA code (i.e. Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False) to ask for a refresh, all is good -- processing occurs and output table is updated. However, anyone else who uses the spreadsheet and asks for a refresh, via the same macro, gets an error "runtime error 1004 - application defined or object defined error" when it hits the above line of QueryTable.Refresh code.

I've had people run the sheet on their PCs, remote into my PC and try to run it there, and everyone but me gets the error. My mind goes to some kind of permissions issue, but all the data is local in the workbook. There were external SQL queries in the spreadsheet in the past but these were all removed -- really. Everything is now static data held in the workbook.

Any ideas?

Thanks, Mark

1
excelvbapowerquery
Try not using the selection object. Instead, get a reference directly to the listobject you want to refresh – ArcherBird
Not sure if this 100% is it, but I had some issues around Privacy and the "Fast Combine" code (on workbook open) helped. stackoverflow.com/questions/52761367/… – SMS
I've also tried --> Worksheets("Lookup").ListObjects(1).Refresh -- but this doesn't resolve the issue. – user6801146

1 Answers

0
votes

The comment about "Fast Combine" got me thinking. Since most of my work is just for me, I typically set Power Query privacy to "None". Perhaps this was the origin of the issue? So, I reset the level to "Public" and then I started to get an error message when I tried to refresh: "Formula.Firewall: Query 'QueryName' (step 'StepName') references other queries or steps and so may not directly access a data source. Please rebuild this data combination." Resolving this error was clarified by instructions at the following site. "https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/". It is a very nice write-up. I'm not sure why the privacy level of "None" didn't work for everyone, but after attending to the rebuild, refreshes work for everyone. Thanks to all who read the post, made suggestions and thought about a solution.

Thanks, Mark