Retrieving text from a webpage.

[QuinnE]

Hi All,

First, I apologize that I am a noob when it comes to website data. I might need some hand holding and don't hate me for asking stupid questions. I have been searching forums to solve my problem and have come up with lots of stuff that doesn't seem to apply to me. I want to retrieve information from a site that displays bill status. Ultimately the goal is to retrieve legislators voting and pull it into a searchable spreadsheet. Each bill has it's own page. So it would be "HOUSE BILL 330 – Idaho State Legislature" for example. Where the last piece is the bill number. The information on the page is just text as far as I can tell. When I look at the source, I don't find any "elements" as has been suggested in other forums. So I am having trouble trying to identify what to pull a variable and how. Any help would be appreciated. I don't have any completed code as yet, so examples would help. I want to just pull the text into a variable and parse it from there and put it into a table of legislator votes.

 

[alansidman]

Can be achieved with Power Query

let
    Source = Web.Page(Web.Contents("https://legislature.idaho.gov/sessioninfo/2023/legislation/H0330/")),
    Data0 = Source{0}[Data]
in
    Data0

 

[QuinnE]

Can be achieved with Power Query

let
    Source = Web.Page(Web.Contents("https://legislature.idaho.gov/sessioninfo/2023/legislation/H0330/")),
    Data0 = Source{0}[Data]
in
    Data0

This is awesome and gives me a thread to follow. I think I can make this work, although I will have to learn about Power Query first. I see that it's now called Get & Transform. I tried it manually and it worked to pull the info into a cell. I can work with that to parse it out. Thank you.

 

[QuinnE]

I hope its OK to continue this thread after I have already marked the answer. I can do this with Power Query, but I need a little extra pointer. I am having trouble feeding the query a variable. I am getting an error "Expression.Error: The import webaddress matches no exports. Did you miss a module reference?" So 'webaddress' is the variable, but clearly its not resolving as it should be 'WebAddress'. I must be formatting this incorrectly. Any help would be appreciated. The second piece of code is from a recorded macro, so I was just trying to replace the part in the quotes with the string variable.

Sub GetBillInfoFromWeb()
'
' Macro3 Macro
'

'
Dim BillNum As String
Dim WebAddress As String
WebAddress = "https://legislature.idaho.gov/sessioninfo/2023/legislation/"
Dim RowNum As Integer
Dim ColNum As Integer
Dim scratch As String

RowNum = 4
Sheets("LegislatureVotes").Select
BillNum = "Start"

Do Until BillNum = ""
BillNum = Cells(RowNum, 1).Value2
WebAddress = WebAddress & BillNum & "/"

    Sheets("Scratchpad").Select
    ActiveWorkbook.Queries.Add Name:="Table 0", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Web.Page(Web.Contents(webaddress))," & Chr(13) & "" & Chr(10) & "    Data0 = Source{0}[Data]," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Data0,{{""Column1"", type text}, {""Column2"", type date}, {""Column3"", type text}, {""Column4"", type text}})" & 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 0"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Table 0]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_0"
        .Refresh BackgroundQuery:=False
    End With
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
    Loop
End Sub

Sub Macro6()
'
' Macro6 Macro
'

'
    ActiveWorkbook.Queries.Add Name:="Table 0", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Web.Page(Web.Contents(""https://legislature.idaho.gov/sessioninfo/2023/legislation/H0001/""))," & Chr(13) & "" & Chr(10) & "    Data0 = Source{0}[Data]," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Data0,{{""Column1"", type text}, {""Column2"", type date}, {""Column3"", type text}, {""Column4"", type text}})" & 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 0"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Table 0]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_0"
        .Refresh BackgroundQuery:=False
    End With
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
End Sub