VBA import data from URL

[novitaun]

Hello all. I'm the new official noooob

Any idea of how to retreive information from a page using excel macros? The connection is done via SSL with login and pasword. The url is something like: http://..........&NQUSER=xxx&NQPASSWORD=XXX
I'd need to import for each of 18 different url's 9 different tables





Things I already tried:

• Get external data from Web. I tried this enhancement already but faliled to retreive all the information. I have to say it works fine for 1 or 2 queries. Non the less, when further queries are done the older get jammed up. So you get a message which reads something like "failed to retreive data from data source"<QUERY datasource from data retreive to failed>. If you reedit the old queries, the newer URLs have replace old ones and so the query fails.
• I also tried to use the Oracle BI plugin for Microsoft Office. It works partialy right. When retreiving data from a pivot table, it fails, and preprocessed figures aren't calculated the way they should, so numbers just don't match.

So in the end I decided to make my own macro to be able to retreive this information by myself. The problem is I don't know where to start from. Is there a way to download the html source from a page with macros? If there is I'd need to parse the html code in order to get each of the different tables held in the page.

I have already done som reaserch on this area. This is what I found:

Function GetSourcePage(ByVal url As String) As String
Dim IEapp As Object
Set IEapp = CreateObject("InternetExplorer.Application")
IEapp.Navigate url
While IEapp.Busy
    DoEvents
Wend
GetSourcePage = IEapp.Document.DocumentElement.outerHTML
IEapp.Quit
Set IEapp = Nothing
End Function

I have to say I've some experience with vba excel macros. I'd say I'm a normal vba user. I also have some vb.net knowledge

 

[novitaun]

Forgot to say my version is office 2007.