VBA code to grab data from a website (ASP.NET)

Question

There's this website: https://mwatch.boursakuwait.com.kw/default.aspx/AllShares

There's a stock market table that I want to import into my Excel workbook.

I found this code on a website and tried to edit it:

Option Explicit
Sub gethtmltable()
Dim objWeb As QueryTable

Set objWeb = ActiveSheet.QueryTables.Add( _
Connection:="URL;https://mwatch.boursakuwait.com.kw/default.aspx/AllShares", _
Destination:=Range("A1"))

With objWeb

.WebSelectionType = xlSpecifiedTables
.WebTables = "1"
.Refresh BackgroundQuery:=False
.SaveData = True
End With
End Sub

I get a message saying that the query returned no data.

Can anyone please help? I'm using the latest version of Excel on an iMac.

There is no "Import data from a website" option.

Ohhh if only it was that easy, but it's not... and I'm afraid I can't help you because I can't read arabic. — dwirony
– dwirony, Commented Dec 27, 2018 at 22:04
I don't see any kind of API or ability to connect via a query, you'd have to webscrape the whole thing which would be a giant task in itself. — dwirony
– dwirony, Commented Dec 27, 2018 at 22:11
Are you using Excel on Windows? I think there's an "Import Data fom a website" option. Can you try doing that and copy>paste the code? — nowifi6952
– nowifi6952, Commented Dec 27, 2018 at 22:16

QHarr · Accepted Answer · 2018-12-29 01:48:49Z

0

Windows machine:

As someone has mentioned there is a POST request made that returns JSON you can parse. The dictionary returned has a weird layout so you will need to invest time looking into that. I use a json parser (jsonconverter.bas) which after adding to your project you also need to go to VBE > Tools > References > Add a reference to Microsoft Scripting Runtime.

Option Explicit
Public Sub GetInfo()
    Dim sResponse As String, json As Object

    With CreateObject("MSXML2.XMLHTTP")
        .Open "POST ", "https://mwatch.boursakuwait.com.kw/default.aspx/getData", False
        .setRequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT"
        .setRequestHeader "Content-Type", "application/json; charset=utf-8"
        .send
        sResponse = .responseText
    End With
    Set json = JsonConverter.ParseJson(sResponse) 'dictionary
    'handle code
    Stop
End Sub

If you print JSON("d") you get a string which makes clear items:

Example:

If you want to use a timed loop with Internet Explorer there is an example below:

Option Explicit

Public Sub GetInfo()
    Dim ie As New InternetExplorer, t As Date, table As Object, clipboard As Object, ws As Worksheet
    Const MAX_WAIT_SEC As Long = 20
    Set ws = ThisWorkbook.Worksheets("Sheet1")

    With ie
        .Visible = True
        .Navigate2 "https://mwatch.boursakuwait.com.kw/default.aspx/AllShares"

        While .Busy Or .readyState < 4: DoEvents: Wend

        t = Timer
        Do
            DoEvents
            On Error Resume Next
            Set table = ie.document.querySelector("#tblMarketData")
            On Error GoTo 0
            If Timer - t > MAX_WAIT_SEC Then Exit Do
        Loop While InStr(.document.body.innerHTML, "No data available in table") > 0
        If Not table Is Nothing Then
            Set clipboard = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
            clipboard.SetText table.outerHTML
            clipboard.PutInClipboard
            ws.Cells(1, 1).PasteSpecial
        End If
        .Quit
    End With
End Sub

Mac:

I would switch languages. For example to python. I am happy to add a script for that if desired.

edited Dec 29, 2018 at 1:48

answered Dec 28, 2018 at 17:27

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

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

9 Comments

Ryan Wildry Over a year ago

Since the user mentioned they are on Mac, I don't think MSXML2.XMLHTTP or the IE approach will work. Leaving this link, in case is of any help. stackoverflow.com/a/15994195/4839827

QHarr Over a year ago

I didn’t actually notice that. My bad. I will leave up for windows users. There is little selectivity with Mac. You tend to get everything! I would definitely switch languages.

nowifi6952 Over a year ago

Hello, thanks for the answer. I'm on an iMac machine. Your code returns a compile-error.

nowifi6952 Over a year ago

If you switch languages (python) would that work on my excel worksheet? I'm desparate for anything that scrapes the data on that website.

nowifi6952 Over a year ago

I will even buy you a $20 amazon/netflix/iTunes gift card as a "thank you" gesture if you help me with this code

|

Collectives™ on Stack Overflow

VBA code to grab data from a website (ASP.NET)

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related