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

Topic

New Reply

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?

Reply | Quote

Replies

Not to my knowledge.

Reply | Quote

Not to my knowledge.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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 Re: Importing from Excel (Ex 2002)) 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…. (fortunately this was a one-time task). Maybe someone more knowledgeable about HTML would have a better solution.

HTH

Reply | Quote