Word VBA and Web Pages

[John]

Hi,

I have a question I can't even figure out how to approach... any ideas would
be helpful.

I've got a letter that I write every week based on the contents of several
web pages.

The letter is in Word.

Is there any way to use VBA to have Word automatically go & get the
information off the website? I'm not claiming that the information is
mine... in fact I give full credit where credit is due, so there's no
copyright violations... I just want my secretary to be able to push a button
& then collect the letter from the printer.

It's just that I can't figure out how to get Word 2000 to get the info from
a web page... or even pull the info from a web page into a document. I know
if I go to IE and save the web page to a file, I can go from there... but
that's too many steps. I'm looking for the one (or maybe two?) button(s)
solution.

Maybe it can't be done... but there's some other way? VB perhaps? Some third
party product?

If anyone has any suggestions, that'd be great...

Cheers,

John

 

[Malcolm Smith]

John

Yes it can be done. It will need some amount of bespoke VBA development
and it could take a couple of days to write as you have to parse the web
page and pull down the stuff that you want.

Now, bear in mind that the web site could move, change shape or whatever
so the parser may break.

If you want someone to do this then find your local and friendly VBA
developer or drop me a mail via my website.

- Malc
www.dragondrop.com

 

[John]

Hi Malcom

Well, thanks for the confirmation that I'm not mad in trying to do it, and
that
it can be done... but as for paying you to do it, that wouldn't be half as
much
fun as doing it myself.

I appreciate the time though & wish you the best of luck with browsing
newsgroups for paying customers. Hope that works out for you.

Meantime, if you wouldn't mind, or if anyone else wants to chime in,
a high level overview of the steps to be taken would be great!

Thanks,

-John

 

[Malcolm Smith]

John

Actually I am no longer actively looking for VBA work these days as I am
having more fun earning money by other means. What I meant was that if
you got stuck for a developer then I'd leap into the breach but I am
trying to limit my VBA consultancy significantly.

You could have a form with the WebBrowser control and use that to get the
web page and when the DocumentComplete() event is raised then you can get
hold of the HTML code and then parse that.

Alternatively you could use the INet control to grab the web page and,
again, you parse the HTML.

If you need any more pointers then let me know and I will add more
pointers.

- Malc
www.dragondrop.com

 

[Jonathan West]

Hi John

Take a look at http://www.mvps.org/vb/samples.htm and scroll down to the
Slurp.zip sample. This will do what you need, though it will probably need a
bit of work to adapt it to VBA rather than VB.

 

[John]

Malcolm

My apologies for my part in that misunderstanding, and thanks for the
pointers
thus far.

Yes, I'd like a few more if you don't mind...

I've got the UserForm now with a WebBrowser control and a TextBox... not so
sure how to link the last two or Navigate to the URL.

-John

 

[Malcolm Smith]

John

Here is an example of one of my routines.

There are two components on this form which are of interest. The first is
a combo box into which one types the URL of the site to vit and the other
component, brwWebBrowser, is the web browser.

In the Form_Load() event you can see that I prime the Web Browser control
with the target URL. This means that when the form is created it is
looking already at my default URL. Since my work is to do with the Sport
of Kings my code points at my homepage.

There are two event handlers which deal with the combo box at the top and
these are below the Form_Load() event. I've stripped them out to give you
an idea of what they do. But basically, if one adds a new URL to the
combo box at the top and then hits enter the page should be navigated to.

I've added a form resize event as well; this makes the form look like a
proper browser so if the form is made bigger or smaller then the size of
the various controls change. Excuse me if this code looks a little
crufty; this was copied from the MS site a long time ago and I have never
got around to making it look pretty or half way presentable.


Now, to get the code from the web page. When the document is loaded the
DocumentComplete() event is fired. Now, bear in mind that if the page if
filled with frames then what happens is that each bit in a frame is a
document. So, if the thing has three frames then this routine fires three
times.

Anyway, this is a straight copy from my code and you will see that the
whole document object is passed to this routine. So, by going through the
DocumentObject (always a wierd model in my view) you can pick out the
code.

You can see me pulling the HTML into the string called sHTML and then it
is after this point that I check to see that I am dealing with the page
that I want.

If it is then it's a case of lots of Mid$() and InStr() function calls to
get the stuff that I want out of it.

And, er, that's it really.

Hope that this helps and, perhaps by now you've worked out why I don't do
much VBA consultancy any more other than for fun (I actually like some of
my old clients) as I make more money from backing and laying horses.

Righto; if you have any questions then please come back to me. If you're
stuck then I am sure that I can mock something up for you.

Pip! Pip!
Malc
www.dragondrop.com



Const DEFAULT_URL As String = "http://www.ukhorseracing.co.uk"


Private Sub Form_Load()

On Error Resume Next

Me.Show
tbToolBar.Refresh
Form_Resize

cboAddress.Move 50, lblAddress.Top + lblAddress.Height + 15
cboAddress.AddItem DEFAULT_URL
cboAddress.Text = DEFAULT_URL

If Len(sStartingAddress) > 0 Then
cboAddress.Text = sStartingAddress
cboAddress.AddItem cboAddress.Text
'try to navigate to the starting address
timTimer.Enabled = True
Me.brwWebBrowser.Navigate DEFAULT_URL
End If

End Sub

Private Sub Form_Resize()

On Error Resume Next

cboAddress.Width = Me.ScaleWidth - 100
brwWebBrowser.Width = Me.ScaleWidth - 100
brwWebBrowser.Height = Me.ScaleHeight - (picAddress.Top +
cboAddress.Height + picAddress.Height) '- 100

End Sub

' Handles the user adding a new URL to the combo box
Private Sub cboAddress_Click()
brwWebBrowser.Navigate cboAddress.Text
End Sub


Private Sub cboAddress_KeyPress(KeyAscii As Integer)
On Error Resume Next
If KeyAscii = vbKeyReturn Then
cboAddress_Click
End If
End Sub



Private Sub brwWebBrowser_DocumentComplete(ByVal pDisp As Object, URL As
Variant)

Dim sHTML As String


On Error Resume Next


sHTML = pDisp.Document.documentElement.innerHTML

If Len(Trim$(sHTML)) = 0 Then
Exit Sub
End If

 

[John]

Hi Jezebel,

Thanks for that, but I think there are some licensing issues. I've got Inet
on my
machine because I've got MS Office Developer Edition, but on another machine
with just Windows 2000, it complained about me not having a license to be
able
to use it.

Anyone have any ideas if it's free from MS somehow?

Thanks

John

 

[Steve R]

John

You didn't mention whether you'd solved your problem. If not, the code at
the link below uses API to copy web page source.

http://www.vbusers.com/code/codeget.asp?ThreadID=111&PostID=1&NumReplies=0

Steve