Refresh backgroundquery false ошибка

Sub Macro1()
Dim URL As String
Dim Path As String
Dim i As Integer
For i = 2 To 50
If Range("Prices!E" & i).Value <> 1 Then
URL = Range("Prices!D" & i).Text
Path = Range("Prices!F" & i).Text
End If
Sheet19.Activate
With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;" & URL _
    , Destination:=ActiveSheet.Range("$A$1"))
    .Name = _
    "" & Path
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .WebSelectionType = xlEntirePage
    .WebFormatting = xlWebFormattingNone
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False
    //'In the Line above the above
    //'Run time error '1004
    //'An unexpected error has occured
End With
Next i
End Sub

The code above creates an error at the specified line. A google search on .Refresh BackgroundQuery shows that it is picky in its functionality in loops. Simply deleting the line makes nothing show up in excel.

With the current error message the code works fine for the first i value and then breaks.

For Answer and comments-
TLDR: .Refresh BackgroundQuery:=False will fail if your query input is invalid or malformed. The problem in this case was the for…next loop was calling cells to use as url’s that hand no values in them. However it will fail anytime the query is malformed.

asked Apr 5, 2011 at 23:10

I’m not sure why my fix worked, but here it is:

I also used querytables.add within a for loop, and I was adding .asc files. This error was only popping up after the last addition—so my program essentially did what I wanted it to, but it would interrupt function. On the last run through the For loop, I removed the .Refresh BackgroundQuery:=False statement. It was necessary for it to paste my data for all the previous runs through the For loop.

Basically I replaced this:

          .Refresh BackgroundQuery:=False

With this:

          If Index = ctr Then

          Else
               .Refresh BackgroundQuery:=False
          End If

answered Jun 10, 2016 at 13:57

Sub Macro1()
Dim URL As String
Dim Path As String
Dim i As Integer
For i = 2 To 50
If Range("Prices!E" & i).Value <> 1 Then
URL = Range("Prices!D" & i).Text
Path = Range("Prices!F" & i).Text
End If
Sheet19.Activate
With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;" & URL _
    , Destination:=ActiveSheet.Range("$A$1"))
    .Name = _
    "" & Path
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .WebSelectionType = xlEntirePage
    .WebFormatting = xlWebFormattingNone
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False
    //'In the Line above the above
    //'Run time error '1004
    //'An unexpected error has occured
End With
Next i
End Sub

The code above creates an error at the specified line. A google search on .Refresh BackgroundQuery shows that it is picky in its functionality in loops. Simply deleting the line makes nothing show up in excel.

With the current error message the code works fine for the first i value and then breaks.

For Answer and comments-
TLDR: .Refresh BackgroundQuery:=False will fail if your query input is invalid or malformed. The problem in this case was the for…next loop was calling cells to use as url’s that hand no values in them. However it will fail anytime the query is malformed.

asked Apr 5, 2011 at 23:10

I’m not sure why my fix worked, but here it is:

I also used querytables.add within a for loop, and I was adding .asc files. This error was only popping up after the last addition—so my program essentially did what I wanted it to, but it would interrupt function. On the last run through the For loop, I removed the .Refresh BackgroundQuery:=False statement. It was necessary for it to paste my data for all the previous runs through the For loop.

Basically I replaced this:

          .Refresh BackgroundQuery:=False

With this:

          If Index = ctr Then

          Else
               .Refresh BackgroundQuery:=False
          End If

answered Jun 10, 2016 at 13:57

Sub Macro1()
Dim URL As String
Dim Path As String
Dim i As Integer
For i = 2 To 50
If Range("Prices!E" & i).Value <> 1 Then
URL = Range("Prices!D" & i).Text
Path = Range("Prices!F" & i).Text
End If
Sheet19.Activate
With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;" & URL _
    , Destination:=ActiveSheet.Range("$A$1"))
    .Name = _
    "" & Path
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .WebSelectionType = xlEntirePage
    .WebFormatting = xlWebFormattingNone
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False
    //'In the Line above the above
    //'Run time error '1004
    //'An unexpected error has occured
End With
Next i
End Sub

The code above creates an error at the specified line. A google search on .Refresh BackgroundQuery shows that it is picky in its functionality in loops. Simply deleting the line makes nothing show up in excel.

With the current error message the code works fine for the first i value and then breaks.

For Answer and comments-
TLDR: .Refresh BackgroundQuery:=False will fail if your query input is invalid or malformed. The problem in this case was the for…next loop was calling cells to use as url’s that hand no values in them. However it will fail anytime the query is malformed.

asked Apr 5, 2011 at 23:10

I’m not sure why my fix worked, but here it is:

I also used querytables.add within a for loop, and I was adding .asc files. This error was only popping up after the last addition—so my program essentially did what I wanted it to, but it would interrupt function. On the last run through the For loop, I removed the .Refresh BackgroundQuery:=False statement. It was necessary for it to paste my data for all the previous runs through the For loop.

Basically I replaced this:

          .Refresh BackgroundQuery:=False

With this:

          If Index = ctr Then

          Else
               .Refresh BackgroundQuery:=False
          End If

answered Jun 10, 2016 at 13:57

Sub Macro1()
Dim URL As String
Dim Path As String
Dim i As Integer
For i = 2 To 50
If Range("Prices!E" & i).Value <> 1 Then
URL = Range("Prices!D" & i).Text
Path = Range("Prices!F" & i).Text
End If
Sheet19.Activate
With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;" & URL _
    , Destination:=ActiveSheet.Range("$A$1"))
    .Name = _
    "" & Path
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .WebSelectionType = xlEntirePage
    .WebFormatting = xlWebFormattingNone
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False
    //'In the Line above the above
    //'Run time error '1004
    //'An unexpected error has occured
End With
Next i
End Sub

The code above creates an error at the specified line. A google search on .Refresh BackgroundQuery shows that it is picky in its functionality in loops. Simply deleting the line makes nothing show up in excel.

With the current error message the code works fine for the first i value and then breaks.

For Answer and comments-
TLDR: .Refresh BackgroundQuery:=False will fail if your query input is invalid or malformed. The problem in this case was the for…next loop was calling cells to use as url’s that hand no values in them. However it will fail anytime the query is malformed.

asked Apr 5, 2011 at 23:10

I’m not sure why my fix worked, but here it is:

I also used querytables.add within a for loop, and I was adding .asc files. This error was only popping up after the last addition—so my program essentially did what I wanted it to, but it would interrupt function. On the last run through the For loop, I removed the .Refresh BackgroundQuery:=False statement. It was necessary for it to paste my data for all the previous runs through the For loop.

Basically I replaced this:

          .Refresh BackgroundQuery:=False

With this:

          If Index = ctr Then

          Else
               .Refresh BackgroundQuery:=False
          End If

answered Jun 10, 2016 at 13:57