Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Dec 2001
    Location
    Houston
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access-VBA code to read web pages (Access XP or 2K)

    Does Access have any tools or methods for reading web page source code and dropping it into a variable or a table? I am currently doing this in Cold Fusion, but would be interested in porting the application to Access if possible. Is Access yet that web-friendly?

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access-VBA code to read web pages (Access XP or 2K)

    Not to my knowledge.
    Charlotte

  3. #3
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Access-VBA code to read web pages (Access XP or 2K)

    Not sure what you mean by "web page source code" here, but if this means the source HTML for a web page, you can "capture" the HTML source as text file by using the technique illustrated in the attached demo database (A2K format). Demo uses URLDownloadToFile API function to capture HTML source as text file by specifying ".txt" as file extension for the szFileName parameter. The Scripting FileSystemObject (FSO) TextStream object is used to read downloaded text file and copy contents into an Access memo field (requires reference set to Scripting Runtime library). See frmHTML form in attached db. To test, enter source URL and Destination (full path & file name of text file to download to) in the textboxes, then click "Download" button (globe/pc icon). Visual feedback provided while download/import transpires. If you look at GetDownload sub (form module) you'll see it was necessary to curtail importing text if exceeded 64,000 characters (which may often be the case for large web pages), otherwise you'd get the dreaded "Text too long to edit" message when trying to enter the SourceHTML (memo field) textbox on form. FSO TextStream ReadLine method used to read text one line at time & test length. (Supposedly a memo field can store appx 2 billion characters, but only if updated programmatically, if text entered via UI limited to a little over 2^16 or appx 64,000 characters.) The form has visual indicators (warning label, etc) when the limit is exceeded. In actual use you'd want to add code to validate that valid URL address and text file path are entered in the textboxes, etc.

    If by "source code" you mean something else, please explain in more detail. In any event you may be able to adapt the techniques demonstrated here for what you have in mind.

    HTH
    Attached Files Attached Files

  4. #4
    Star Lounger
    Join Date
    Dec 2001
    Location
    Houston
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access-VBA code to read web pages (Access XP or 2K)

    That is exactly what I meant, and this works great. Thank you very much! This allows me to replace a Cold Fusion app with an Access app, since the CF app was crashing the web server ... at least until I figure out what was slamming the server.

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access-VBA code to read web pages (Access XP or 2K)

    Hi MarmD...
    I have donwload your mdb and test it with:

    http://www.gssitaly.com/default.html (is my personal site)

    I have error in download, why?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access-VBA code to read web pages (Access XP or 2K)

    It works for me. What is the exact text of the error message?

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access-VBA code to read web pages (Access XP or 2K)

    I was checking out this post, because of something I was trying to do which is quite similar.

    I have developed an application which - among other things - stores details of possible job prospects. Rather than manually filling in all the job details, or copy-and-pasting from IE into the form, I would like to automate the process.

    Here's the URL of a sample job prospect : http://www.jobserve.com/W4720688FC15CD005.job

    The only way I have found to import this so far is to input the URL, and then use Automation to open Excel, read the web page into Excel, and then search Excel for the appropriate fields. It works - just - but it's not flexible, and I am sure there must be a better way to go about it. I wonder whether XML would help (I'm using Access 2003), but I don't know enough about it to be sure.

    Any thoughts or suggestions would be very welcome

    Thanks

    Nick

  8. #8
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Access-VBA code to read web pages (Access XP or 2K)

    In belated reply, sorry, do not know of a better way to import the data from web page into Access. In a previous thread (see <!post=Re: Importing from Excel (Ex 2002),362428>Re: Importing from Excel (Ex 2002)<!/post>) described method that can be used to import HTML table into Access - you can't do this directly (in A2K anyway), you have to download the HTML page to a local folder, then import the downloaded HTML file. I tried this approach with the web site you referenced (the JobServe site) and did not work - got error msg (see attached text file for sample code used). Apparently the way this HTML is formatted does not allow importing - when viewing HTML source text could not identify a TITLE or CAPTION tag for table that could be used for the TransferText method's HTMLtablename parameter (if left blank, Access tries to import first table found in file - see comments in attached file).

    AFAIK, ACC 2000 does not support XML as an import/export format. I'm not currently using ACC 2002 or 2003 so don't know if XML could be used for this. I recently had to "capture" similar data from a web site that provides "Cost Analysis" data in HTML table format - you input a bunch of parameters & options, submit form, and a HTML page is generated to display results. I wound up cuttin' 'n pastin' results into Excel spreadsheets one at a time.... <img src=/S/frown.gif border=0 alt=frown width=15 height=15> (fortunately this was a one-time task). Maybe someone more knowledgeable about HTML would have a better solution.

    HTH

Posting Permissions

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