• Visitors can check out the Forum FAQ by clicking this link. You have to register before you can post: click the REGISTER link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. View our Forum Privacy Policy.
  • Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!

Excel question - importing data from a web app

Collapse
X
  •  
  • Filter
  • Time
  • Show
Clear All
new posts

    Excel question - importing data from a web app

    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?

    #2
    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.
    bloggoth

    If everything isn't black and white, I say, 'Why the hell not?'
    John Wayne (My guru, not to be confused with my beloved prophet Jeremy Clarkson)

    Comment


      #3
      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.

      Comment


        #4
        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.
        Last edited by xoggoth; 13 January 2009, 20:00.
        bloggoth

        If everything isn't black and white, I say, 'Why the hell not?'
        John Wayne (My guru, not to be confused with my beloved prophet Jeremy Clarkson)

        Comment


          #5
          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:

          Code:
          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
          Last edited by NickFitz; 13 January 2009, 20:38.

          Comment


            #6
            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
            Last edited by xoggoth; 13 January 2009, 20:52.
            bloggoth

            If everything isn't black and white, I say, 'Why the hell not?'
            John Wayne (My guru, not to be confused with my beloved prophet Jeremy Clarkson)

            Comment


              #7
              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.

              Comment


                #8
                Originally posted by bored View Post
                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

                Comment


                  #9
                  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.
                  Originally posted by MaryPoppins
                  I'd still not breastfeed a nazi
                  Originally posted by vetran
                  Urine is quite nourishing

                  Comment


                    #10
                    Originally posted by d000hg View Post
                    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
                    Coffee's for closers

                    Comment

                    Working...
                    X