Option Explicit

Sub Tester()
GetWeb "http://www.google.com"
End Sub

Sub GetWeb(sURL As String)

Dim twbs As Object

Set twbs = ThisWorkbook.Sheets

Application.DisplayAlerts = False
On Error Resume Next
twbs("Webcopy").Delete
On Error GoTo 0
Application.DisplayAlerts = True

With Workbooks.Open(sURL)
.Sheets(1).Copy after:=twbs(twbs.Count)
.Close False
End With

twbs(twbs.Count).Name = "WebCopy"
End Sub

.... or just use the built-in WebQuery functionality.


--
Tim Williams
Palo Alto, CA


"Necessitysslave" wrote in message
oups.com...
I am writing a macro for my users that grabs info off of a web page.
I want it to open a new worksheet with the info on it so that I can
work on it.

Below is my code it uses *ug* sendkeys and notepad to convert
documentelement.html into a text file is there a way of doing this
without using a program external to excel?

if not is there a way to close notepad without notepad asking if I want
to save?

Dim oIE As New SHDocVw.InternetExplorer
Dim sURL As String
Dim MyAppID As Long
sURL = "http://www.w3.org/2002/ws/" 'the page I'm loading is
'on the intranet but this is good for an
example

'open a new, visible IE window
Set oIE = New SHDocVw.InternetExplorer
oIE.Visible = false

'go to desired page
oIE.Navigate sURL

'wait for page to finish loading
Do Until oIE.ReadyState = READYSTATE_COMPLETE
DoEvents
Loop

MyAppID = Shell("notepad", 1)
DoEvents
On Error Resume Next
AppActivate "microsoft ex"
Application.DisplayAlerts = False

Worksheets("Webcopy").Delete

Application.DisplayAlerts = True
ActiveWorkbook.Sheets.Add
ActiveSheet.Name = "Webcopy"
Range("A1") = oIE.Document.documentelement.innerhtml
Range("A1").Copy
AppActivate "Untit"
DoEvents
SendKeys "^v"
DoEvents
SendKeys "%ea"
DoEvents
SendKeys "^c"
DoEvents
SendKeys "% c"
DoEvents
ActiveSheet.Range("A1").ClearContents
ActiveSheet.Paste
oIE.Quit