We help IT Professionals succeed at work.

Extract data from several different webpages to populate cells in excel 2000 spreadsheet using VBA

franklee168
franklee168 asked
on
I like to create a button in a spreadsheet that would extract data from internet webpages and populate cells in spreadsheet.  I want to automate the following steps:
1.  type in a URL in IE
2.  Save the source to a temp file
3.  run a "function" to extract data from the temp file and populate a specific cell in a specific excel file or a table in an access db.
4.  repeat steps 1 to 3 for all another 12 URLs.

I know enough to do step 3, but not how to incorporate 1 and 2 in the "function".

[Excel spreadsheet is not an absolute requirement.  Using Access is an acceptable substitute.]

Comment
Watch Question

Commented:
Frank

Include the Microsoft Internet Controls in your project reference.

Add Inet into your form (e.g. Inet1)

To get the source file of the URL.

Dim cSrc as string
Dim cURL as string
Dim nCnt as integer

'cSrc : Variable to store the source file of the URL page
'cURL : URL that you want to save
'nCnt : Loop Counter
nCnt = 1
While nCnt < 13
   if nCnt = 1 then
      cURL = "http://www.nba.com"
   else
      cURL = "http://www.nfl.com"
   End If
   cSrc = FrmMain.Inet1.OpenURL(cURL)
   'Extract Data
   nCnt = nCnt + 1
Wend

The assumption made is that you are able to extract the data.  However, if you need help you can indicate here.

Hope this helps
Ivanc
Richie_SimonettiIT Operations
CERTIFIED EXPERT

Commented:
Searching...
Richie_SimonettiIT Operations
CERTIFIED EXPERT

Commented:
Set a reference to microsoft Internet controls

dim withevents IE as internetexplorer

sub form_load()
set Ie = new Internetexplorer
with ie
     .navigate "about:blank"
     .visible = True
end with

sub Form_Unload
on error resume next
ie.quit
set ie = nothing
end sub

'in Documentcomplete event of ie

if (pdisp is ie) then
   dim i as integer
   i= freefile
   open "c:\" & ie.document.title for input as #i
        print #i, ie.document.documentelement.innerhtml
   close #i

end if

If you need more help, just ask.
Cheers.
CERTIFIED EXPERT
Author of the Year 2009

Commented:
Hi franklee168,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Accept ivanc's comment(s) as an answer.

franklee168, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you.  DO NOT accept this comment as an answer.

EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
==========
DanRollins -- EE database cleanup volunteer
Per recommendation, force-accepted.

Netminder
CS Moderator

Explore More ContentExplore courses, solutions, and other research materials related to this topic.