0

PLease can anyone help with the following code?

I get an object error when I run it and I can't see why.

the error message is "Run-time error 1004. Microsoft Excel is refreshing some data. Please try again later"and debug is on BackgroundQuery = True.

Sub RereshQuery()

Max = 10
Query = Worksheets("Setting").Range("Query_Text3").Text
For i = 1 To Max
Application.ActiveWorkbook.Worksheets("Report").Range("G6").Value = i

Sheets("Book").Select
    With ActiveWorkbook.Connections("Book").ODBCConnection
        .BackgroundQuery = True
        .CommandText = Query
        .CommandType = xlCmdSql
        .Connection = "ODBC;DRIVER=XXXX;"
        .RefreshOnFileOpen = False
        .SavePassword = False
        .SourceConnectionFile = ""
        .SourceDataFile = ""
        .ServerCredentialsMethod = xlCredentialsMethodIntegrated
        .AlwaysUseConnectionFile = False
    End With
        ActiveWorkbook.Connections("Book").Refresh

Next i

End Sub
Uwe Keim
  • 38,279
  • 56
  • 171
  • 280
yugo1004
  • 1
  • First, try refactoring your code using the multiple answers of [How to avoid using Select in Excel VBA](https://stackoverflow.com/q/10714251/1422451). Namely, avoid `.Select` and `ActiveWorkbook`. – Parfait Jun 06 '21 at 13:03

0 Answers0