REFRESH Individual Queries Automatically working with multiple sheets

XLstudent

New Member
Joined
Jul 14, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
Hello all,
I'll do my best to explain my problem, as it involves couple of parallel aspects.

The BIG PICTURE:

I've been working in a file with multiple sheets, multiple pivot tables and few queries.
As the excel sheet is a database with more than 30 different sheets and components, I've been hiding and un-hiding the tabs as the user clicks the DB which he wants to access. Ok. This is done and it works fine.

All pivot tables were updated automatically using the RefreshALL function...

The PROBLEM:
I've started to face problems when I started trying to update individual queries as the RefreshAll takes too long for the quantity of tables and queries I have.

When I click a button I'd like to go to another worksheet and as soon as the worksheet is deactivated I'd like to update the query.
I've been using the following code for refresh the query:
VBA Code:
 ActiveWorkbook.Connections("Query - Name").Refresh

The refresh is partially succesfull. However, the program is not going to the sheet I'd like to activate, it goes to the "MAIN MENU" instead...
Any idea how could I ensure to update the queries, unhide and go to the desired sheet?? Below the code

VBA Code:
Private Sub Button_Click()
Sheets("1").Visible = True
ThisWorkbook.Sheets("1").Activate
End Sub

Private Sub Worksheet_Deactivate()
ActiveWorkbook.Connections("Query - X").Refresh
ThisWorkbook.Sheets("1").Visible = False
End Sub

P.S.: This happens only when I try to refresh the QUERIES, i have disabled then and the PIVOT tables would update fine and go to the proper worksheet.

Thanks a lot!
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

XLstudent

New Member
Joined
Jul 14, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
By the way, the same happens also when I create another sub for changing whenever the worksheet is changed:

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
      ThisWorkbook.Connections("Query - X").Refresh
End Sub

So...it is for sure the "Query - X" which is causing the problem...but why??? :(
 

Watch MrExcel Video

Forum statistics

Threads
1,127,523
Messages
5,625,308
Members
416,090
Latest member
Amneziak

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top