Import HTML to spreadsheet

[UHsoccer]

I need to import all the data from a local html file to a spreadsheet. If I do it "manually", I do a Select-All, Copy and then paste in Excel.

I found some code "sub websurf" on a three year old entry that was written to do something like that, but the source file was a website URL.

I tried to use it but did have to comment the .ReadyState segment
then it gave an error on the EXECdb : The Object invoked has disconnected from its clients

It does open the file in a new window

Any advice? Here is the code

-------------------------

Sub websurf()
' Original definition
'Const url As String = "http://web1.ncaa.org/d1mfb/Internet/national%20rankings/IA_teamrush.html"

' new entry referencing my html file
Const url As String = "C:\Quote\Imports\Test.html"

Set ie = CreateObject("internetexplorer.application")

With ie
'you can change any of the below settings to your liking except for the navigate line.
.Top = 1
.Left = 1
.Height = 400
.Width = 500
.AddressBar = False
.MenuBar = False
.Toolbar = False
.Visible = true
.Navigate url

' I eliminated the next do-while
'Do While
.ReadyState <> 4 'this makes excel wait for IE to open the page
'DoEvents
'Loop

' the next 2 lines select and copy the entire contens of the web page to the clipboard.
' This fails ---------------
.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DONTPROMPTUSER
.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT

End With

Activesheet.paste

ie.Quit
End Sub

 

[Legacy 98055]

If the format of the web page does not change, you are better of simply parsing the HTML source and then placing the selective data dorectly into your cells. Another possible help is to place this code directly before this line of code:

Set ie = CreateObject("internetexplorer.application")

Place this code before the above.

    On Error Resume Next
    ie.Quit
    Set ie = Nothing
    On Error GoTo 0
    
    Set ie = CreateObject("internetexplorer.application")

On your next post, select "Disable HTML in this post. Post the HTML source within Code tags.

Tom

 

[UHsoccer]

Slow progress

1) it now displays the html document.

2) the " .ReadyState <> 4 " statement appears in red (error) and it is NOT executable

 

[Oorang]

Hey sorry to ressurect a dead thread, but when I try the above code I get the error:
Run-time error '-2147221248(80040100)':
Method 'ExecWB' of object 'IWebBrowser2' failed
When I try to execute line

.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DONTPROMPTUSER

Does anyone have an idea how to resolve this error (Do I need to turn on a library or something?)
Alternatly is there a work around I tried just using send keys to grab the info but it does not seem to work (unles I am doing that wrong too).

 

[savindrasingh]

Its too late but I am looking at this code for the first time. I was in need of similare thing and I had modified the code successfully as:

Sub websurf()
Const url As String = "file:///C:/Documents%20and%20Settings/nbkzpji/Desktop/Test/DSS-Automail.html"
Const OLECMDID_COPY = 12
Const OLECMDID_SELECTALL = 17
Const OLECMDEXECOPT_DODEFAULT = 0
Const OLECMDEXECOPT_PROMPTUSER = 1
Const OLECMDEXECOPT_DONTPROMPTUSER = 2
Const OLECMDEXECOPT_SHOWHELP = 3
Set ie = CreateObject("internetexplorer.application")
With ie
    .Top = 1
    .Left = 1
    .Height = 400
    .Width = 500
    .AddressBar = False
    .MenuBar = False
    .Toolbar = False
    .Visible = True
    .Navigate url
Do While .ReadyState <> 4
    DoEvents
Loop
    .ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DONTPROMPTUSER
    .ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT
End With
ActiveSheet.Paste
ie.Quit
End Sub