CODEWITHSUNDEEP
excelvba
Xodarap
Xodarap

Reputation: 381

using VBA to refresh one by one the queries of my workbook - Excel 2016

When I try to refresh some queries from my workbook, for some reasons I get this error :

The index does not belong to the selection (execution error 9)

I'm starting with using this :

    For Month=1 To 12 Step 1
      ThisWorkbook.Connections("Query - Tmp" & Month).OLEDBConnection.Refresh
    Next Month

I have 12 queries (Tmp1 / Tmp2 / ...) for 12 sheets (one for each month).

I already tried things like using :

Not working

    ThisWorkbook.ActiveSheet.QueryTables.Count 
    '(Result: QueryTables.Count = 0)

or :

    ThisWorkbook.Connections("Query - Tmp1").OLEDBConnection.Refresh
    '(Result: same error message)

Working

    ThisWorkbook.RefreshAll
    '(all my queries are refreshed)

Upvotes: 1

Views: 780

Answers (1)

ONC
ONC

Reputation: 71

You have to use an index instead of the connection name.

See https://learn.microsoft.com/fr-fr/office/vba/api/excel.workbook.connections

You can get the connection name with the "name" property.

So, something like that should be ok :

For i=0 To NbConnections-1
  If ThisWorkBook.Connections(i).Name = "Query - Tmp" & Month Then
      ThisWorkbook.Connections(i).OLEDBConnection.Refresh
  End If
Next  i

Upvotes: 1

Related Questions