Excel 2013 VBA Web Query connection string containing variable

Question

I am getting a Run-time error'1004' error on the following line.

With ActiveSheet.QueryTables.add(Connection:=connstring, Destination:=Range("$b$2"))
Destination:=Range("$b$1"))

The Variable connstring seems to be causing the problem. How do I properly use the variable name in this connection statement?

Help would be greatly appreciated

---

Sub add()

For x = 1 To 58000

 Worksheets("PAGES").Select
 Worksheets("PAGES").Activate

 connstring = "http://www.name-list.net/russia/1"

 With ActiveSheet.QueryTables.add(Connection:=connstring, Destination:=Range("$b$2"))
 Destination:=Range("$b$1"))

    .Name = "1"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .WebSelectionType = xlSpecifiedTables
    .WebFormatting = xlWebFormattingNone
    .WebTables = "2"
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False
End With

Next x

Answer 1

Change connstring to be -->

connstring = "URL;http://www.name-list.net/russia/1"

Better still -->

Dim connstring As String
connstring = "URL;http://www.name-list.net/russia/1"

The MSDN doco for that method is QueryTables.Add Method

I can't guarantee the rest of your code will work as you expect. Why are you looping 58000 times?