copying a web page as a txt or xls file

chrisbrocco

Board Regular
Joined
Mar 31, 2005
Messages
82
I have read lots of post on here but can't quite find what I'm looking for.

I am trying to basically

open webpage
select all
copy
paste into a temp file (as txt or xls)


I would like to do this without the send keys function

any help would be much appreciated
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,254
Office Version
  1. 2016
Platform
  1. Windows
You can use the URLDownloadToFile API .

Example from Allapi.net :


Code:
Private Declare Function URLDownloadToFile _
Lib "urlmon" Alias "URLDownloadToFileA" _
(ByVal pCaller As Long, ByVal szURL As String, _
ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long


Private Function DownloadFile(URL As String, LocalFilename As String) As Boolean
    Dim lngRetVal As Long
    lngRetVal = URLDownloadToFile(0, URL, LocalFilename, 0, 0)
    If lngRetVal = 0 Then DownloadFile = True
End Function

Sub TEST()
    'example by Matthew Gates (Puff0rz@hotmail.com)
    DownloadFile "http://www.allapi.net", "c:\allapi.htm"
End Sub

Just change the extention of the passed LocalFilename prameter to txt to save the page as a text file.

Regards.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Chris

You could use something like this example.
Code:
Sub Test()
    
    Set ie = CreateObject("InternetExplorer.Application")
    With ie
        .Visible = True
        .Navigate "http://data.bls.gov/cgi-bin/srgate"
        Do Until .ReadyState = 4: DoEvents: Loop
        Set myTextField = .document.all.Item("series_id")
        myTextField.Value = "ECU11121I"
        ie.document.forms(0).submit
        Do Until .ReadyState = 4: DoEvents: Loop
        Do While .busy: DoEvents: Loop
        Set doc = ie.document
        GetAllTables doc
    End With
    
End Sub

Sub GetAllTables(d)
 For Each e In d.all
    If e.nodename = "TABLE" Then
        Set t = e
    
        tabno = tabno + 1
        nextrow = nextrow + 1
        Set rng = Range("B" & nextrow)
        rng.Offset(, -1) = "Table " & tabno
        For Each r In t.Rows
            For Each c In r.Cells
                rng.Value = c.innerText
                Set rng = rng.Offset(, 1)
                i = i + 1
            Next c
            nextrow = nextrow + 1
            Set rng = rng.Offset(1, -i)
            i = 0
        Next r
    End If
Next e
End Sub
 

chrisbrocco

Board Regular
Joined
Mar 31, 2005
Messages
82
Norie and rafaaj2000 many thanks, used both pieces of code in two simliar projects, many thanks
 

Forum statistics

Threads
1,137,333
Messages
5,680,881
Members
419,937
Latest member
Talic

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