Importing data from the web (script page)

  • Thread starter Thread starter Wesslan
  • Start date Start date
W

Wesslan

Hi,

I have spent most of today reading different threads on the matter of
pulling data from the web to Excel. Many use the approach of opening
an IE window and then copying all data into Excel. Others use web
query through Excel and specify which table they would like to
download. As I have understood it the IE approach is useful if the
page is of HTML or similar format with frames etc. But the page I want
to pull data from is not a straightforward page but rather is a script
page.

I therefore tried the Web query which Excel provides and then managed
to get the table I want (in the query request) but when Excel is to
import the data I get the message "Only one top level element is
allowed in an XML document".

The page I want to download data from is the following:

"http://hcm.handelsbanken.se/struktureradeprodukter/kapitalskydd/
Kurser/Aktieindexobligationer/"

and I would like to pull the data which is presented in the middle of
it (SHBO 1003.. SHBO 1004 and so on)..

Any guesses on how I could do this (automatically of course :)?

Many thanks,
 
This is the sort of thing I do for my clients on a daily basis. This should
do it

Sub GetMyData()
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://hcm.handelsbanken.se/struktureradeprodukter/kapitalskydd" _
, Destination:=Range("A5"))
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "1"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub
 
Don,

Many thanks for your quick reply! It does exactly what I am asking for
(but for some reason) the link in my first post was incorrect. The
page from which I want to pull the data is "http://
hcm.handelsbanken.se/struktureradeprodukter/kapitalskydd/
Kurser/Aktieindexobligationer/" (please observe the /Kurser/
Aktieindexobligationer)

So is it possible to do the same but for that page? Cause if I
understand your code correctly you take the first webtable and pull
that data out (which is perfect for the "http://hcm.handelsbanken.se/
struktureradeprodukter/kapitalskydd/" but not for the link I actually
wanted (apologize for that the link somehow became broken in the first
message).

If you have a solution for this you will make my day! In the meantime,
I will play around with the WebTables and see if any number works for
the link.. ;)

Regards,
 
I gave you the method to do this.
Surely you can paste in the correct url and re-do or contact me for custom
work.
 
I just put in the url below and got the same
hcm.handelsbanken.se/struktureradeprodukter/kapitalskydd/Kurser/Aktieindexobligationer/"
hcm.handelsbanken.se/struktureradeprodukter/kapitalskyddKurser/Aktieindexobligationer
 
Hi

I need to import a table from a authenticted internal webpage, (ie i have to
give in user name and password to access it). Is there a way to do it.
 
Back
Top