Results 1 to 4 of 4
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    read web page from Excel (Excel 2000/2002)

    Hi all,

    A co-worker asked me if there is a way in Excel to get it to extract data from a web page. My immediate answer was no Excel can launch a web page but it can't read its contents. They should go write a Perl script to read the web page, get the data and then use the spreadsheet module to write it out... but I wanted to ask the EXPERTS <img src=/S/grin.gif border=0 alt=grin width=15 height=15> here in this lounge in case I'm overlooking something.

    Here's the ideal plan... <img src=/S/cool.gif border=0 alt=cool width=15 height=15> The Excel file would contain a set of hyperlinks which display a web page containing data for # of hits and visitors for some given start/end date. The magic Excel code would then know how to read this web page and extract out just the fields they want and dump it back into a spreadsheet. Right now, this web page usage report is exportable to Excel (from a button on the web page) but you get all the fields and they want to have Excel automatically extract only some of those fields for their review. This task gets done on a regular basis so it'd be great if they didn't have to constantly edit the output report for their own data.

    Is this possible through some magic .dll that I can reference? <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>

    Thnx, Deb

  2. #2
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: read web page from Excel (Excel 2000/2002)

    Try Data > Import External Data from within Excel. That will enable you to generate a file like the attached...

    StuartR

  3. #3
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: read web page from Excel (Excel 2000/2002)

    Great idea! Actually since I want a web page extraction, I used the menu Data/Import External Data/New Web Query not 'import external data' since it's not a database I want to read from.

    I then entered the URL I wanted (after going to the source web page and entering the report criteria and copying the URL from the address field) and selected the table that contained the data. Unfortuantely it only lets me select all the fields in a given table not a sub-set of fields (columns). Of course once that data is into Excel I can filter/delete whatever cols I don't want.

    I then put each report on its own worksheet since that seems to be how the queries are saved. I stumbled on that fact when I right-clicked on one of the output report fields (data cell). It has a menu item to 'refresh' the query or to 'edit' the query. I will then write a few lines of code at each worksheet_activate event to do this refresh and then delete the rows I don't want.

    Thanks for reminding me of this really cool feature. <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35> I tried it in Excel 2002 but I also have Excel 2000 and am guessing it works the same.

    Deb <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: read web page from Excel (Excel 2000/2002)

    Just an FYI, there is a 'magic' .dll that can handle this.

    Public Declare Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" _
    (ByVal pCaller As Long, ByVal szURL As String, ByVal szFileName As String, _
    ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long

    That will let you download a URL to a file. To use this in VBA,

    Check for the existance of your temp file. Kill it if it's there.
    Download URL to temp file.
    Read the temp file, in as a string.
    Find your values
    Kill the temp file.

    Pretty simple.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •