VBA Code to extract HTML Table data to worksheet

[jhurwitz]

I am new to VBA and having a very difficult time getting this code to work. I am trying to extract the data values from the references Bundesbank page and get them into a worksheet so I can manipulate from there. What I have is below, and I'm looking for help in the [bracketed areas].

Thanks

Sub Get_Data()

Dim IE As New InternetExplorer
IE.Visible = False
IE.navigate "http://www.bundesbank.de/Navigation/EN/Statistics/Time_series_databases/Macro_economic_time_series/its_details_value_node.html?nsc=true&listId=www_s201_b9233&tsId=" & "BBK01.ED0439"
Do
DoEvents
Loop Until IE.readyState = READYSTATE_COMPLETE
Dim BubaWebpage As HTMLDocument
Set BubaWebpage = IE.document
IE.Quit
Dim RawData As HTMLTable
Set RawData = BubaWebpage.getElementsByClassName("valueTable")(0)
ThisWorkbook.Sheets.Add After:=Sheets(Worksheets.Count), Count:=1, Type:=xlWorksheet
ActiveSheet.Name = "BubaData"
Dim i As Long
Dim j As Long
i = 1 'rows
j = 1 'columns

    For i = 1 To [Help]
                For j = 1 To 6
                    Sheets("BubaData").Cells(i, j).Value = [SOMETHING LIKE RawData.Rows(i).Cells(j).innerText BUT THIS DOESNT WORK]
                    j = j + 1
                Next
                i = i + 1
    Next


MsgBox "Done"


End Sub

 

[Kyle123]

It uses the xml object to send a web request, the data that's returned is the same data that you see when you right click on the web page and click view source (you don't need to use a web browser to send a web request - only to render the html). This is just a string, so I use a htmlFile object (essentially the underlying web rendering bit in internet explorer) to turn that string into an object that you can iterate through in exactly the same way as Internet Explorer (well not exactly the same, but close enough )

 

[jhurwitz]

@Kyle123

Ah yes, I see now what you mean. I was confused before since the web address itself didn't come across on the thread as text - I didn't realize the forum probably just converted the url string into a hyperlink. This does seem considerably faster, and removes the need for IE references. Thanks for your continued help!

/Solved

 

[Johnnyze]

Hi Taurean,

Thanks for that code. It seems exactly like what I need for something I'm working on.
However, the Url that I am taking data from a website that is user/password protected (I have login credentials)
Because of this, whenever I run the macro, all that it pulls in is the text from the login page.

Any ideas how I can fix this??

Thanks in advance
Johnny

 

[md_iffu]

Hi,

Am using excel 2013 on Windows 7 OS (Desktop PC) - IE
I have gone through this thread and tried all options to extract data, however not good results.

Kindly need your assistance.

Am trying to extract or I would rather say export data from the team room link (can't share the link due to security reasons)
This link can be connected through client VPN.

Request for a macro, so that I can extract this web link data (HTML) and paste it on my excel 2013.

 

[md_iffu]

Hi Kyle,

Need your help here.
Am trying to extract HTML data from the client's URL (connect through VPN) and paste it on worksheet "Sheet2"

I need a MAcro so that it can extract from the web and paste it on the worksheet "Sheet2"

Note: The Macro "button" is in sheet1

my team has run one web query , however we get the error :
Run time error "1004":
Unable to open
URL://*******
Cannot download the information you requested.

 

[md_iffu]

Hi Team,

Am trying to extract data from eRoom (URL)
Kindly need your team assistance VBA code to extra data from this eRoom URL

Am using windows 7 OS (PC) and Excel 2013

Due to security reasons, I wont be able to share the link.
===============================

When we click on that link, it wil prompt for user id and password.
after enter the credentials, it will take the eRoom where we list of information.
We export the information, and file gets download in the .csv format.

Kindly assist.

 

[Kyle123]

You need to open your own thread. Though a word of warning, if you won't share the link, noones going to be able to help you - though they may be able to point you in the direction of some examples that you can adapt

 

[md_iffu]

True Kyle,

Let me check with my Compliance team, if I can share the link.
Till that time, request for example which I posted the query.

 

[Kyle123]

Have you opened your own thread?

 

[md_iffu]

Hi Kyle,

I tried to look out for the option to open a new own thread, however unable to understand how to do..
I have gone through the FAQs