VBA Imported Data come through in Single Cell

Grunter31

Board Regular
Joined
Mar 11, 2017
Messages
53
Office Version
  1. 365
Platform
  1. Windows
I couldn't be any newer to VBA and slowly worked out my first module. What I can't get is all the imported data to show in different cells. It just lines up in a single cell with no spaces
Also is there a way to automatically move the number on the website URL forward by 1 and bring in that new data in the next column. (I have removed the https://www from the below example so the code would display correctly

Sub Get_Web_Data()

Dim request As Object
Dim response As String
Dim html As New HTMLDocument
Dim website As String
Dim price As Variant

website = sportsbookreview.com/betting-odds/nba-basketball/3569074/odds/"

Set request = CreateObject("MSXML2.XMLHTTP")

request.Open "GET", website, False

request.setRequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT"

request.send

response = StrConv(request.responseBody, vbUnicode)

html.body.innerHTML = response

price = html.getElementsByClassName("_3DLFC").Item(0).innerText

ActiveSheet.Range("A1") = price

Range("A1") = price

End Sub
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
See if the following suit your needs
VBA Code:
Sub Get_Web_Data()

    Dim oWsROW      As Worksheet
    Dim oWsCOLUMN   As Worksheet

    Dim request     As Object
    Dim response    As String

    Dim html        As HTMLDocument
    Dim website     As String
    Dim price       As Variant
    Dim vResult     As Variant
    Dim lPage       As Long
    Dim i           As Long

    lPage = 3569074

    Set html = New HTMLDocument

    Application.ScreenUpdating = False
    Set oWsROW = ThisWorkbook.Worksheets.Add
    Set oWsCOLUMN = ThisWorkbook.Worksheets.Add

    For i = 0 To 2

        website = "https://www.sportsbookreview.com/betting-odds/nba-basketball/" & (lPage + i) & "/odds/"
        Set request = CreateObject("MSXML2.XMLHTTP")
        With request
            .Open "GET", website, False
            .setRequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT"
            .send
            response = StrConv(.responseBody, vbUnicode)
        End With
        html.body.innerHTML = response
        price = html.getElementsByClassName("_3DLFC").Item(0).innerText

        price = Replace(price, "Â", "")
        vResult = Split(price, vbLf)
        oWsROW.Cells(i + 1, 1).Resize(1, UBound(vResult)).Value = vResult
        oWsCOLUMN.Cells(1, i + 1).Resize(UBound(vResult), 1).Value = WorksheetFunction.Transpose(vResult)

    Next i

    oWsROW.UsedRange.Columns.AutoFit
    oWsCOLUMN.UsedRange.Columns.AutoFit
    oWsCOLUMN.UsedRange.Columns.HorizontalAlignment = xlCenter
    Application.ScreenUpdating = True

DONE:
    Set request = Nothing
    Set html = Nothing
    Set oWsROW = Nothing
    Set oWsCOLUMN = Nothing
End Sub
 

Grunter31

Board Regular
Joined
Mar 11, 2017
Messages
53
Office Version
  1. 365
Platform
  1. Windows
Hi GWteB. That worked perfectly. Took me a couple of minutes to work out how to get more then 3 games.
Thanks so much for taking the time to help.
I'm ever so slowly learning how to understand VBA
Thanks Again
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
You're welcome and thanks for letting me know.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,321
Messages
5,641,516
Members
417,213
Latest member
wikk

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top