Importing Webpage Hypertext into Excel as Text using VBA???

GaryTheGolfer · Mar 19, 2006

Hi there,
This my first post after spending hours and hours browsing this exellent forum. I am in effect a VBA virgin and find the learning curve daunting....In other words...........Help.....please!

What I'm trying to do in WinXP :
Update an Excel 2003 spreadsheet with selected share prices from a portfolio website. My (existing) spreadsheet then manipulates and redisplays the share values and other assets as I want. Ideally I would like to import specific cellls (specific share names, price etc)....just as you would under a web query. Being unable to figure out how to do that, I decided to simply copy & paste the whole webpage on to an excel worksheet and then pick out the data from there.

[I used to have a nice little macro that worked well. It used what I think is called Web Query. Then the website added a log-in page with UserName/Password. So I started on the slippery VBA slope.]

I read the forum and "borrowed" suggested code [Thanks to contributors].

I have shown my current code below. It opens the webpage, logs on (with the correct password!), copies the webpage (as HTML). I can't get it to paste (or pasteSpecial) the webpage contents AS TEXT. It seems that it can only paste as HTML????? This is annoying given that a manual copy and pastespecial from the webpage works perfectly.....ie pastes as text not HTML

If I use just ActiveSheet.Paste, the whole webpage (with Hypertext/VBA code?) is copied into the spreadsheet....and is a real mess....trying to delete all those floating graphics seems to be a nightmare.

I am happy that I will be able to manipulate the webpage from inside the worksheet but only if it can be imported/converted to text.

Any ideas/help will be appreciated.
Thanks in Advance

Code:

' REMEMBER TO ENABLE <TOOLS> <REFERENCES> <MICROSOFT INTERNET CONTROLS> !!!!!!
' WebOpenPW Macro
' Macro recorded 16/03/2006 by G.L.Goodchild
' (Activate with CONTROL+w)
Sub myWebOpenPW()

' Dim IE As New InternetExplorer
Dim IE As Object
Set IE = CreateObject("InternetExplorer.application")

'Make Internet Explorer visible and go to Website
IE.Visible = True
IE.Navigate "https://www.iii.co.uk/auth/portfolio?orig_url=/portfolio&orig_server=www.iii.co.uk&orig_method=GET&"
Do
    If IE.ReadyState = 4 Then
        IE.Visible = False
        Exit Do
    Else
        DoEvents
    End If
Loop

'   Log on to Website
Application.Wait (Now + TimeValue("0:00:3"))
IE.Visible = True
Application.Wait (Now + TimeValue("0:00:1"))
'   Enter Website password (Userame is remembered)
SendKeys "123456", True
'   Not the real password !!!
SendKeys "{ENTER}", True
'   Wait for IE to load
Application.Wait (Now + TimeValue("0:00:10"))

'   Select All Intenet Explorer data and then Copy to the clipboard
IE.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DODEFAULT
IE.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT

Range("B17").Select
'   THIS IS THE BIT THAT DOESN'T WORK :
    ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
        False
Range("B2").Select
' Close Internet Explorer
IE.Visible = False
End Sub

just_jon · Mar 19, 2006

This thread by NateO sounds like what you're looking for, but not the stuff I'd recommend for a novice...

http://www.mrexcel.com/board2/viewtopic.php?t=185951

Legacy 98055 · Mar 19, 2006

If you can provide a temporary UN and PW, and the exact step by step, including the specific cells in your tables, I can help you out. Excel's web query's smallest import denominator is a table. If you want specific cells you will need to provide something more customized.

The code you posted does work by selecting the text on the web page and pasting it into a spreadsheet. You must set a reference to Microsoft Internet Controls or the constants will mean nothing to VBA. At least it worked on my machine.

Replace the constants with their values and try again or set the correct reference.

Replace:

IE.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DODEFAULT
IE.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT

With :

IE.ExecWB 17, 0
IE.ExecWB 12, 0

Make sure you scroll down into your worksheet. Depending on how the page is rendered, the text may be a good ways down. This is a very dirty import, though your code looks familiar

. Another method I have used, when applicable, is to use VBA to navigate to the correct page, save the HTML doc to disk, and then query the local html page using excel's built in query. Download the example file below with your procedure modified to save the file to disk, open it in Excel's web query, and then import it. If it appears to work for you, record a macro by creating a new web query, querying the file that you saved to disk. Implement this into your current code and post back if you need further assistance.

This downloadable example uses this technique of downloading a remote file to disc, querying the local file, importing a table from the local into your activesheet, and finally deleting the temp file.

RemoteToLocalQuery.zip

Here is an attempt to modify your current code to work using this method:

<table border="1" bgcolor="White"><caption ALIGN=left>Example VBA Code:</caption><tr><td> 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

 ' REMEMBER TO ENABLE <TOOLS> <REFERENCES> <MICROSOFT INTERNET CONTROLS> !!!!!!
 ' WebOpenPW Macro
 ' Macro recorded 16/03/2006 by G.L.Goodchild
 ' (Activate with CONTROL+w)
 Sub myWebOpenPW()

 ' Dim IE As New InternetExplorer
 Dim IE As Object
 Set IE = CreateObject("InternetExplorer.application")

 'Make Internet Explorer visible and go to Website
 IE.Visible = True
 IE.Navigate "https://www.iii.co.uk/auth/portfolio?orig_url=/portfolio&orig_server=www.iii.co.uk&orig_method=GET&"
 Do
 If IE.ReadyState = 4 Then
 IE.Visible = False
 Exit Do
 Else
 DoEvents
 End If
 Loop

 ' Log on to Website
 Application.Wait (Now + TimeValue("0:00:3"))
 IE.Visible = True
 Application.Wait (Now + TimeValue("0:00:1"))
 ' Enter Website password (Userame is remembered)
 SendKeys "123456", True
 ' Not the real password !!!
 SendKeys "{ENTER}", True
 ' Wait for IE to load
 Application.Wait (Now + TimeValue("0:00:10"))

 '' Select All Intenet Explorer data and then Copy to the clipboard
 'IE.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DODEFAULT
 'IE.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT
 '
 'Range("B17").Select
 '' THIS IS THE BIT THAT DOESN'T WORK :
 ' ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
 ' False
 'Range("B2").Select

 'this section downloads the page to your disc and then
 'runs an excel query on it
 Dim TempHtmlPath As String
 TempHtmlPath = ActiveWorkbook.Path & "\temp.html"
 If URLDownloadToFile(0, IE.LocationURL, TempHtmlPath, 0, 0) = 0 Then
 'ok. Run Excel's query on the local file
 Names(CallQuery(TempHtmlPath)).Delete
 Else
 'failed to save page
 MsgBox "Failed to download remote file to disc..."
 End If

 'get rid of the temporary local copy of this webpage
 On Error Resume Next
 Kill TempHtmlPath
 On Error GoTo 0

 ' Close Internet Explorer
 IE.Visible = False
 End Sub

 'you will need to edit this query to suit. You may simply
 'record a macro by importing the local page and then following
 'the rules laid out below of passing the filepath to the connection
 'argument. Note the difference here: "URL;file:///" as opposed
 'to a remote query
 Private Function CallQuery(LocalFilename As String) As String
 ' Macro1 Macro
 ' Macro recorded 3/19/2006 by Thomas and Janis Schreiner
 '

 '
 With ActiveSheet.QueryTables.Add(Connection:= _
 "URL;file:///" & LocalFilename, _
 Destination:=Range("A1"))
 .FieldNames = True
 .RowNumbers = False
 .FillAdjacentFormulas = False
 .PreserveFormatting = True
 .RefreshOnFileOpen = False
 .BackgroundQuery = True
 .RefreshStyle = xlInsertDeleteCells
 .SavePassword = False
 .SaveData = True
 .AdjustColumnWidth = True
 .RefreshPeriod = 0
 .WebSelectionType = xlSpecifiedTables
 .WebFormatting = xlWebFormattingNone
 .WebTables = "10"
 .WebPreFormattedTextToColumns = True
 .WebConsecutiveDelimitersAsOne = True
 .WebSingleBlockTextImport = False
 .WebDisableDateRecognition = False
 .WebDisableRedirections = False
 .Refresh BackgroundQuery:=False
 CallQuery = .Name
 End With

 End Function
</td></tr></table>

GaryTheGolfer · Mar 19, 2006

I've set up Dummy Log-on to website

OK. Thanks for replies. As suggested, I have set up a dummy web account for the portfolio website

(www.iii.co.uk).

https://www.iii.co.uk/auth/portfolio?orig_url=/portfolio&orig_server=www.iii.co.uk&orig_method=GET&

The dummy Username is : GaryTheGolfer
The dummy Password is : 123456

I have set up a representative portfolio for different types of investments (shares, Unit trusts

etc).

Using a web query I was (at one time) able to import the sections as separate tables.My problem

could be solved if I could figure out how to log-on with the password and then use webquery process

for the redirected webpage. I have struggled.

The WebQuery (portfolio.iqy) contains:

WEB
1
http://www.iii.co.uk/reg/wealth/?type=shares_and_funds

Selection=2,5,7,10
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False

This query selects 4 sections. My interest is in a subset of the query result text:

eg
Share name + Bid price (eg British American Tobacco PLC (BATS.L) + 1450)
Investment name + bid price (eg Jupiter Global Green Investment Trust PLC (JGG.L) + 97)
Unit Trust name + bid or mid price (eg Name + 104.6 and Name + 1097 etc etc)
Offshore UK Authorised name + bid price (eg FPI JF Eastern (RYPFCT)
+ 1.65)

I will be quite happy to paste the entire (text) table and then extract the specific cells within

Excel. So just extracting the tables to Excel will be enough help.

Hope that all makes sense.

Thanks again for your help/interest.
Gary

Legacy 98055 · Mar 22, 2006

Here is my attemp to get the tables directly. It appears to work but my experience programming the DOM is limited. Download the example that I know works and uses early binding for a bit better performance.

CustomWebImport.zip

<table border="1" bgcolor="White"><caption ALIGN=left>Example VBA Code:</caption><tr><td> 'late bind
 Sub CustomWebQuery()
 Dim IE As Object
 Dim ht As Object
 Dim hdoc As Object
 Dim htr As Object
 Dim htc As Object
 Dim x As Integer, r As Range
 Dim tBodyRow As Integer
 Dim RangeRow As Long, RangeCol As Integer
 Dim TablePosition As Integer

 Range("b2:m23").ClearContents
 Application.ScreenUpdating = False
 Set IE = CreateObject("InternetExplorer.Application")

 'Make Internet Explorer visible and go to Website
 'IE.Visible = True
 IE.navigate "http://www.iii.co.uk/reg/wealth/?type=shares_and_funds"

 Do Until IE.ReadyState = 4 And Not IE.busy
 'should provide a timeout
 DoEvents
 Loop

 On Error Resume Next
 IE.document.all("III_username").Value = "GaryTheGolfer"
 IE.document.all("III_password").Value = "123456"
 IE.document.all("submit_button").Click

 Do Until IE.ReadyState = 4 And Not IE.busy
 'should provide a timeout
 DoEvents
 Loop
 On Error GoTo 0

 'check to see if we are on the correct page
 If InStr(IE.locationurl, "shares_and_funds") = 0 Then
 MsgBox "NAV shares_and_funds"
 'we have a problem
 End If

 Set hdoc = IE.document

 '>> 1__################################################################__1 >>
 'check to see if we are on the correct page
 'dirty temp code here
 Dim DirtyTimeout As Date
 DirtyTimeout = Now
 On Error Resume Next
 Set hdoc = IE.document
 Do Until Trim(hdoc.Title) = "Interactive Investor - Portfolio"
 Set hdoc = IE.document
 DoEvents
 If DateDiff("s", DirtyTimeout, Now) > 30 Then
 MsgBox "TIMED_OUT CODEID 1"
 End If
 Loop
 Do Until IE.ReadyState = 4 And Not IE.busy
 'should provide a timeout
 DoEvents
 Loop
 On Error GoTo 0
 '<< 1__################################################################__1 <<

 Set r = [b2]
 TablePosition = 0
 For Each ht In hdoc.getElementsByTagName("TABLE")
 TablePosition = TablePosition + 1
 'if tableposition > highest table souceindex then exit for
 Select Case TablePosition
 Case 2, 5, 7, 10

 r.Value = ht.getElementsByTagName("SPAN")(0).getAdjacentText("afterBegin")

 Set r = r.Offset(1)

 'get table headers
 RangeCol = 0
 For Each htc In ht.tHead.Rows(0).Cells
 r.Offset(, RangeCol).Value = htc.innerText
 RangeCol = RangeCol + 1
 Next

 Set r = r.Offset(1)
 For Each htr In ht.tBodies(0).Rows
 If htr.RowIndex And 1 Then
 r.Value = htr.getElementsByTagName("A")(0).innerText
 Set r = r.Offset(1)
 Else
 RangeCol = 0
 For Each htc In htr.Cells
 r.Offset(, RangeCol).Value = htc.innerText
 RangeCol = RangeCol + 1
 Next
 Set r = r.Offset(1)
 End If
 Next

 End Select
 Next

 ' Close Internet Explorer
 IE.Quit
 Set IE = Nothing
 End Sub

</td></tr></table>

GaryTheGolfer · Mar 22, 2006

Excellent & Working!!!

Excellent !!!! Many thanks Mr Right_Click !!

I was beginning to think you had disappeared....but NO!!

I've tried the pasted code which seemed to work very well. I then tried the CustomWebImport.zip version which also seems to work perfectly well......and the results look great....and importantly delivers each element of data into its own cell. This will make integration into my existing spreadsheets MUCH easier.

For my own benefit I will need to look into your coding (particularly for finding the webpage table names and then fetching those tables) so that I can understand how you have worked this magic. There was no way I could have got from where I was to where you have gone!

When I get a spare half hour I will edit your code to suit my real User ID/Password and actual shares/mutuals etc.

Excellent help.
Thank you very much indeed
Gary

Legacy 98055 · Mar 22, 2006

Well... I'm glad this works for you using a specific example. Note that the structure of this page is fairly complex because of a not readily apparent hierarchy including tables within tables within tables, ect... The parent table positions are hardcoded into your procedure using the same method that Excel uses. Position based indexing. See "Case 2, 5, 7, 10" That is, from the beginning of the page, the second, fifth, seventh, and tenth tables are the parent tables that concern us, but only for the specific page containing the specific data that I was provided with. The line "For Each ht In hdoc.getElementsByTagName("TABLE")" is saying, iterate through each table in this document. From the first to the last in the order in which they are listed. The code is "intelligent" enough to take it from there, but you will need to determine the "parent" tables containing your "child node" data, so to speak. Therefore, if your "real" page contains more or less tables, the 2, 5, 7, and 10 will absolutely not apply and the code, as is, will fail. This is a procedure that lacks a user interface allowing the user to build their web query directly from the web page it'self such as Excel's built in Web Query. Not to worry, please post back or PM me if you need help figuring out the table numbers.

Also, will the dummy account be available indefinitely? Because these types of questions come up so regulary, I'm thinking about creating some sort of addin that will allow users to build their own queries, but with many more allowances than Excel currently offers. Because of the complexity of this page, it would be a good page to test on. Can I get my own account? How much does it cost? Thanks...

EDIT: Missing the obvious here.

You managed to get your table indices before, so I'm sure you can repeat the process. Simply record a macro using Excel's web query, select your tables, and replace the line in the procedure:

Case 2, 5, 7, 10 with the table, positional indices returned in the recorded macro.

GaryTheGolfer · Mar 23, 2006

Thanks Again

Again thank you.

I'm grateful that you took the time to explain the Tables issues....and then added an easy solution (creating dummy recorded macro to find "real" table numbers). If I had tried a "trial & error" approach, it would have taken a long time to find my real table numbers (which turned out to be 2,5,7,16 !!!).

As far as I know, the GaryTheGolfer portfolio should be permanently available. The only danger would be that anyone who knows the password could choose to alter the specific shares listed (changing the Table numbers again and crashing the macro).

However, the parts of the iii.co.uk I use are free

All you need to do is go to iii.co.uk and follow the "Register" link, give them a UserID/password (and a few personal details). It turns out that you don't even need to supply a true name/email. [I gave false name/email when I set up the dummy and it was still usable].

After you register with iii.co.uk you need to log-on and go to the "Portfolio" link. You can then add different shares, mutuals (aka Unit trusts in UK) etc. I chose this website because it covered a few investments that many other websites didn't cover. ie a one-stop shop.

You can also set up multiple "Portfolios" which might be useful for demonstrating/testing programming issues.

The ability to customise the macro to take account changing investments would be very useful since (presumeably) every time you buy/sell an investment and change your iii portfolio, the Table references will change.
It would therefore be almost mandatory for any "Day Traders"

Thanks again
Gary

minhanh0011 · Dec 21, 2015

Tom Schreiner said:
Here is my attemp to get the tables directly. It appears to work but my experience programming the DOM is limited. Download the example that I know works and uses early binding for a bit better performance.

Hi Tom Schreiner ! Help .....
please ! I have web pages https://www.michiganlottery.com/club_keno_info ,
I want to excel on the right side of the page ( " Last Draw " )

Importing Webpage Hypertext into Excel as Text using VBA???

GaryTheGolfer

New Member

Excel Facts

just_jon

Legend

Legacy 98055

Guest

GaryTheGolfer

New Member

Legacy 98055

Guest

GaryTheGolfer

New Member

Legacy 98055

Guest

GaryTheGolfer

New Member

minhanh0011

New Member

Similar threads

Forum statistics

Share this page

Importing Webpage Hypertext into Excel as Text using VBA???

GaryTheGolfer

New Member

Excel Facts

just_jon

Legend

Legacy 98055

Guest

GaryTheGolfer

New Member

Legacy 98055

Guest

GaryTheGolfer

New Member

Legacy 98055

Guest

GaryTheGolfer

New Member

minhanh0011

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock