Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Pivot table in power query

hello, there is a help to make the Pivot table in the picture in a power query.   Picture of the table below. It will make the job much easier and simpler in a situation like the first pictur...
Need Help
  • rubayatyasmin's avatar
    rubayatyasmin
    3 years ago

    ThisWorkbook.Connections("Combined_Financials_Sector").Refresh

     

    note that Background refresh option should be checked. In the Excel UI, you can access this setting by clicking on "Connections" in the "Data" ribbon, selecting a connection, and clicking on "Properties". The "Enable background refresh" setting is in the dialog box that opens.

     

     

     

rubayatyasmin's avatar
rubayatyasmin
Community Champion
3 years ago

Anonymous 

 

 

The "Run-time error 9: Subscript out of range" typically occurs when the specified object or element does not exist within the collection. In this case, the error is likely due to the fact that there is no connection named "Combined_Financials_Sector" in the workbook connections.

To resolve this error, you should check if the connection name is correct and make sure that the connection exists in the workbook. To verify the connection name, you can follow these steps:

  1. Open your Excel workbook.
  2. Click on the "Data" tab in the ribbon.
  3. Click on "Connections" to view the workbook connections.
  4. Look for the connection named "Combined_Financials_Sector" in the list of connections. If it's not there, you may have used a different name or the connection might have been deleted.

If you find the correct connection name, you can use the following VBA code to refresh the connection in the background with the "Enable background refresh" option checked:

 

Sub RefreshConnectionInBackground() On Error Resume Next ThisWorkbook.Connections("Combined_Financials_Sector").Refresh On Error GoTo 0 End Sub

This code will try to refresh the connection, and if it doesn't find the connection, it will simply resume the code without raising an error. However, it's essential to make sure that the connection name is correct and exists in the workbook.

If you are still encountering the error after verifying the connection name, please double-check the name spelling and ensure that there are no typos or extra spaces in the connection name. Also, make sure that the connection is created before trying to refresh it programmatically.

 

 

Anonymous's avatar
Anonymous
Not applicable
3 years ago

I checked what you wrote but there is no workbook link.

I checked what you wrote but there is no workbook link.
I am updating the pivot table.

Private Sub CommandButton1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
        

Sheets("Symbols_Price").Range("Combined_Price_Sector").ListObject.QueryTable.Refresh BackgroundQuery:=True


Worksheets("PT_C").PivotTables("PivotTable1").PivotCache.Refresh



End Sub

 

It continues to download in the background but I get another error.
Actually, I just need to press "end" for the refresh to finish.