Web to access table

[dupes]

Hello Friends,


I am trying to save the sheet from following page to an access table
Any ideas on how to achieve this?

Web Page: https://docs.google.com/spreadsheets...=0&single=true

I am able to download it to a Text box "Text3 " using following code but is there a way to get the sheet as a table?



Dim IE As Object
Set IE = CreateObject("Internetexplorer.Application")


IE.Visible = False
IE.NAVIGATE "https://docs.google.com/spreadsheets/d/e/2PACX-1vTeVs6qiVE6YQ1MUeK_9NHa-URkdR7D6KEr4y6Sj-EtQuyBRf-3s7rfSI0iO3mzgUut6pwBxi-3chO9/pubhtml?gid=0&single=true"
Do While IE.BUSY Or IE.READYSTATE <> 4


Loop


Dim PAGE As String
Dim PG1() As String


PAGE = IE.Document.BODY.INNERTEXT


Me.Text3 = PAGE
Refresh

[June7]

Scraping web page gets complicated. Got the following to work:

Sub test()
Dim htm As Object
Dim Tr As Object
Dim Td As Object
Dim Tab1 As Object
Dim Web_URL As String
Dim ie As Object
Dim iTable As Integer'Replace the URL of the webpage that you want to download
Web_URL = "https://docs.google.com/spreadsheets/d/e/2PACX-1vTeVs6qiVE6YQ1MUeK_9NHa-URkdR7D6KEr4y6Sj-EtQuyBRf-3s7rfSI0iO3mzgUut6pwBxi-3chO9/pubhtml?gid=0&single=true"
'open Internet Explorer and go to website
Set ie = New InternetExplorer
ie.Visible = False
ie.Navigate Web_URL
Do While ie.ReadyState <> READYSTATE_COMPLETE
    'Application.StatusBar = "Trying to go to webpage ..."
    DoEvents
Loop
'Create HTMLFile Object
Set htm = ie.Document
Set ie = Nothing
'Loop through each table
For Each Tab1 In htm.getElementsByTagName("table")
    With htm.getElementsByTagName("table")(iTable)
        For Each Tr In .Rows
            For Each Td In Tr.Cells
                If Td.ClassName Like "S*" Then
                   CurrentDb.Execute "INSERT INTO Table1(WebTest) VALUES('" & Td.innertext & "')"
                End If
            Next Td
        Next Tr
    End With
    iTable = iTable + 1
Next Tab1
End Sub

Alternative is to use Excel's Get External Data to import from web page. That worked very nicely. AFAIK, equivalent is not available in Access.

[dupes]

Thanks,
U are rite

Is there a way to download the web page as html then using VBA and then use a Get external data to fetch the content?
I was trying this unfortunately couldnt get through to save it to a local drive

[dupes]

Managed to get it in a better way
brought in the data in a csv format into a text box with ";" and then appended it to a table, "&output=csv" made the difference in the url

URL = "https://docs.google.com/spreadsheets/d/e/2PACX-1vTeVs6qiVE6YQ1MUeK_9NHa-URkdR7D6KEr4y6Sj-EtQuyBRf-3s7rfSI0iO3mzgUut6pwBxi-3chO9/pub?gid=0&single=true&output=csv"
Set HTTP = CreateObject("MSXML2.ServerXMLHTTP.6.0")
HTTP.Open "GET", URL, False
HTTP.Send
CSV = HTTP.ResponseText
DataRows = Split(CSV, Chr(10)) ' split the CSV into rows
Me.Text3 = CSV


Refresh

Thanks for your help

[June7]

Wow, that is nice! And fast! Glad you got it figured out.

Guessing don't need to do conventional web page scraping because of the data source.

[isladogs]

@Dupes
For info, the approach you used can also be very easily adapted for importing XML or JSON files if needed