Hi First Post and hope someone can help as it is causing hair loss.
I design GUI's using excel and access. 2003 i never had a problem but since updating to 2007/2010 i have having a problem with a seamingly simple thing.
In 2003 i could refresh a query on a very hidden sheet using
Sheet2.Range("a1").QueryTable.Refresh BackgroundQuery:=False
in 2007 / 2010 i cannot do this. It works if i cut and paste the query off the 2003 sheets into 2007 / 2010 Perfectly but not if it is a query i build in 2007 / 2010.
The recorder spits out
Range("Table_Query_from_MS_Access_Database_1[[#Headers],[Trans_ID]]").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
Which the page has to be selected first to run
so logically i have tried
Sheet2.Range("a1").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
which selects the position of the query first again wont work it fails at the Sheet2.Range("a1").Select part
Then i logically tried
Sheet2.Range("a1").Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
again this doesnt work
Sheet2.Range("Table_Query_from_MS_Access_Database_1[[#Headers],Trans_ID]]") _
.Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
This Also doesnt work
All i want to do is refresh a query on a very hidden sheet
Using screen.update = false then unhide macro hide screen update = true isnt really ideal as i want to stop the end user from getting to this query as it is part of a logon System in excel i just cant work out a way to refresh the query on the hidden sheet which was a simple task in 2003.
Please help my hair loss
Thanks
Richard
Best Answer
Select does not work on a very hidden sheet. Try it without the select -
Sheet2.Range("a1").ListObject.QueryTable.Refresh BackgroundQuery:=False