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

Consider using xmlhttp instead of webquery. This code downloads the data from 39 pages link — Santosh
– Santosh, Commented Jan 16, 2014 at 15:17

robnick · Accepted Answer · 2014-01-16 04:20:03Z

2

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?

answered Jan 16, 2014 at 4:20

robnick

1,76817 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel 2013 VBA Web Query connection string containing variable

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related