Webquery - multiple pages from website


New Member
Jan 24, 2017
Hi All

I am trying to do a web query to obtain data from a table split across multiple web pages and then insert the data onto one sheet in Excel.

I have been unable to get this to work.

I get the following error on the Refresh.BackgroundQuery=False line - Run-time error '1004': Application-defined or object-defined error

The connection seems to be established in Excel and when I click on the query and then on edit I can see the data but it's not loaded into the sheet and the loop does not run

Below is my current effort

Your help will be highly appreciated!


Sub WebQuery1()
' WebQuery1 Macro

Dim p As Integer
Dim r As Integer
Dim l As Integer
Dim t As Integer
Dim WebString As String
Dim WebString2 As String
Dim WebSource As String

r = 1
l = 1
t = 2

For p = 1 To 352

WebString = "https://www.website.ashx?v=111&r=" & r
WebString2 = Chr(34) & WebString & Chr(34)
WebSource = "Source = Web.Page(Web.Contents(" & WebString2 & ")),"

ActiveWorkbook.Queries.Add Name:="Table" & t, Formula:= _
"let" & Chr(13) & "" & Chr(10) & WebSource & Chr(13) & "" & Chr(10) & " Data2 = Source{2}[Data]," & Chr(13) & "" & Chr(10) & " #""Promoted Headers"" = Table.PromoteHeaders(Data2, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""No."", Int64.Type}, {""Ticker"", type text}, {""Company"", type text}, {""Sector" & _
""", type text}, {""Industry"", type text}, {""Country"", type text}, {""Market Cap"", type text}, {""P/E"", type text}, {""Price"", type number}, {""Change"", Percentage.Type}, {""Volume"", Int64.Type}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type"""

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table "" & t;Extended Properties=""""" _
, Destination:=Range("$A$" & l)).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [Table" & t & "]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table_" & t
.Refresh BackgroundQuery:=False

End With

r = r + 20
t = t + 1
l = l + 21

Next p
End Sub

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...