copying a web page as a txt or xls file

[chrisbrocco]

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

 

[Jaafar Tribak]

You can use the URLDownloadToFile API .

Example from Allapi.net :

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]

Chris

You could use something like this example.

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]

Norie and rafaaj2000 many thanks, used both pieces of code in two simliar projects, many thanks