Excel question - importing data from a web app

[bored]

A question for Excel experts:

What is the best way to dynamically import external data (1-2 tables) from a web app into Excel 2007? If possible I'd like to avoid macros (i.e. just use External Data/Refresh All).

Also, what is a good way to do that if the table is generated by a command-line app? Currently I write the table into a .CSV file then import it via an ODBC .csv datasource. It takes a while to set it up though, is there any better way?

[xoggoth]

Not using macros, dunno but if nobody has a non macro solutio I would suggest a freeware file ripper thing URL2FILE.EXE that can be called from a macro. More details if you need it. Works well.

[bored]

Thanks, that's a nice idea, I tried importing data directly via a URL with various options but it never worked well. I can get the web app to generate a .csv file, download it using wget (same thing as url2file) from a macro and then use an ODBC datasource to import the data. Seems a bit too complicated though, there must be a better way.

[xoggoth]

Another macro answer, but this is really easy, took about 10 secs to check it out in outline, stick a webbrowser on sheet and copy contents to the sheet.

Macro is:

Private Sub Worksheet_Activate()
WebBrowser2.Navigate "http://www.bondriver.co.uk"
a = WebBrowser2.Document.Body.innerHTML
Range("G8") = a
End Sub

When you go to the sheet from another, G8 now contains all the contents of the body part of the file. I expect you can extract it from that.works in 2000 anyway. You can use innerText to get just the visible text without all the HTML tags.

I think you can get individual table contents in similar way if you know the ID. You won't find any documentation for browser in vba, look on net for VB6 webbrowser, is same object.

[NickFitz]

Rather than using the webbrowser control, which is basically firing up a whole instance of Internet Explorer to retrieve a plain text file, it would make more sense to use MSXML.XmlHttp, usually known as XmlHttpRequest.

You can probably get away with using it synchronously, if you don't mind locking up if the web site is down or otherwise unreachable:

Dim xhr As Object
Dim url As String, csvText As String

url = "http://example.com/data.csv"
Set xhr = MSXML2.XmlHttp
xhr.Open "GET", url, False
xhr.Send ""
' should really check that response status=200 here
csvText = xhr.responseText

It's years since I've used VBScript/VBA, so don't be overly surprised if there are syntax errors in there

[xoggoth]

That's neat! Never 'eard of it but I'm a dinosaur. That code didn't quite work but there's a ref here.

http://scriptorium.serve-it.nl/view.php?sid=40

Cut it down a bit and it works!

Private Sub CommandButton1_Click()
Dim sURL As String, sHTML As String
Dim oHttp As Object
sURL = "http://www.bondriver.co.uk"
On Error Resume Next
Set oHttp = CreateObject("MSXML2.XMLHTTP")
If Err.Number <> 0 Then
Set oHttp = CreateObject("MSXML.XMLHTTPRequest")
MsgBox "Error 0 has occured while creating a MSXML.XMLHTTPRequest object"
End If
If oHttp Is Nothing Then
MsgBox "For some reason I wasn't able to make a MSXML2.XMLHTTP object"
Exit Sub
End If
oHttp.Open "GET", sURL, False
oHttp.Send
sHTML = oHttp.responseText
Range("C10").Value = sHTML
End Sub

[bored]

Thanks.

I've just tried to use Get External Data from Text, Get External Data from Web and some other options but they all seem flawed. The first one prompts me for the filename every time I hit Refresh All, even though the filename is saved in the properties of the connection. The second one can only read HTML files and not XML files, how dumb is that. No option to get CSV files from the web either. Microsoft , guess the whole web services thing just slipped by them.

The data in question is a 1MB CSV file and growing, downloading it via XmlHttpRequest and then parsing it is just not worth it I guess since the wget solution will work fine. I think might just not bother with it at all and redo the spreadsheet using something more appropriate than Excel.

[NickFitz]

The data in question is a 1MB CSV file and growing, downloading it via XmlHttpRequest and then parsing it is just not worth it I guess since the wget solution will work fine. I think might just not bother with it at all and redo the spreadsheet using something more appropriate than Excel.

You might be able to work something out using XHR and then using ADO to treat the CSV as an ODBC data source.

Actually doing that is left as an exercise for the reader

[d000hg]

Not sure if this is helpful, but in IE you can interact with Excel from a web page using Javascript... I assume thrugh an ActiveX object.

[Spacecadet]

Not sure if this is helpful, but in IE you can interact with Excel from a web page using Javascript... I assume thrugh an ActiveX object.

I've done that before when building web apps and it's not too difficult, you can even do some cell formatting.

Better option though is to provide a - "open this in excel link" which opens the data as an excel file using the meta tags to specify the application

Still, neither of these is the solution that bored is looking for

IMO - a far better solution is to grab the data from the website on a regular basis, store it in a database application or a formatted plain text file then access the database or text file from Excel.
Sounds like more work, but it will be a lot easier to manage in the future and avoids having to use VBA