Retreiving Website data using VBA (1 Viewer)

ianclegg · Jan 8, 2011

Hi can anyone help or point me in the right direction.

I need to access website information using vba from a button on a form.
eg

www.google.co.uk/news, I then want to extract some of the relevant information from that page ( This will be from a private website using a sign on eventually).

I have read most of the posts on this subject and have tried to implement the suggestions without any success. Can anyone help ? , i currently make up the link into a text box and try to access it using the following code.

dim WSCtrl as commandbutton
dim WebAddress as string

with WSCtrl
.hyperlinkaddress=webaddress
.hyperlink.follow
end with

Regards

Ian Clegg

the_net_2.0 · Jan 8, 2011

Ian,

what version of access do you have? If 07 or later, try putting the webBrowser control on your form and simply browsing the internet that way. It's pretty much JUST like doing it in a browser, and can even be automated by code the same way, but with local referencing of the active x control instead or remote referencing of the browser object.

ianclegg · Jan 8, 2011

I am using access 2003 and IE7

Regards

Ian

the_net_2.0 · Jan 8, 2011

you might be able to download the active x from the web with that version Ian, but I'm not sure.

If you can't, vba still works fine with IE. And so does the ShDocView.dll file, which works specifically with the IE browser object.

Remember, IE is part of windows, so you can use the:

Code:

createobject()

function to create it and work with it. Also, most of the HTML properties and object groups associated with the document object model of a webpage can also be used with the IE object in vba, although the intellisense will be there to help you out unless you specifically use the DLL mentioned above.

LPurvis · Jan 10, 2011

Hello over in Sheffield!

This can be problematic, depending upon the comment:
"...using a sign on eventually".
Ordinarily, you'd just use the MSXML object library to fetch the source content of a specified page. For exmaple the function below.

Code:

Function FirePost(strPage As String, strPost As String)
    
    Dim req As Object 'New MSXML2.XMLHTTP30
    Set req = CreateObject("MSXML2.ServerXMLHTTP")
    
    req.Open "POST", strPage, False
    req.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
    req.Send strPost
    
    FirePost = req.responseText

 
End Function

If the page is secure then the Username and Password can be supplied. But only to verify page access.
If the security is by log on in a loading page then you might have to maniuplate IE to read that (I refer to IE because it exposes an OM to facilitate that).

For example performing a search using Google and retrieving the HTML source of the resulting page:

Code:

Sub GoogleAccessIE()
    Dim objIE As Object
    
    Set objIE = CreateObject("InternetExplorer.Application")
    
    With objIE
        .Visible = True
        .Navigate "[URL]http://www.google.co.uk[/URL]"
        Do While .ReadyState <> 4: DoEvents: Loop
        With .document.Forms(0)
            .q.Value = "Access"
        End With
        .document.f.Submit
        Debug.Print .document.Body.outerText
    End With
    
End Sub

Obviously the former is simpler, and if you can use it as a direct function then even more convenient.
For example:
FirePost ("http://www.google.com","")

Cheers

hk1 · Jan 10, 2011

LPurvis beat me to it. I recommend the same solution. I have used it with success.

ianclegg · Jan 10, 2011

Thanks to everyone who looked and advised, I have nearly completed the task but still have a problem. I have posted a copy of the code that I have used.

Dim ie As Object
Set ie = CreateObject("internetexplorer.application")
ie.Visible = True

ie.Navigate "https://securesite/ExistingJobs_Search.aspx"

While ie.Busy
DoEvents
Wend

jbid = ie.Document.getElementById("ctl00$cphContent$txtJob").Value

Set FormInput = ie.Document.getElementsByTagName("input")

ie.Document.getElementById("ctl00$cphContent$txtJob").Value = "645000171249"

jbid2 = ie.Document.getElementById("ctl00$cphContent$txtJob").Value

'Works upto here !

After I have updated the searchcode to the job that I want eg 64500017124
the cursor seems to disappear from the box, I cannot work out how to submit the form. I need to simulate pressing enter with the cursor in the job number box.

Any ideas ?

Regards

Ian

hk1 · Jan 10, 2011

You can probably use the SendKeys function although I can't really recommend it.

Is this a "post" form instead of a "Get" form? There's no way you can format a URL directly to the page you'd like to go to?

the_net_2.0 · Jan 10, 2011

Purvis' method I've never seen before, but I have no doubt that it works.

I haven't seen many search features that are not inside of forms on the web, Ian. so find the form in the source code, and use its index to submit it. It should be that simple.

don't forget the fact that you can also push buttons and click images, if this thing you're doing is not part of a form, by chance.

search the pages source code for "<form". if it's the first in the list, you can simply write:

Code:

ie.document.forms(0).submit

which is basically what Purvis said already.

hk1 said:
Is this a "post" form instead of a "Get" form?

It doesn't matter, because the submittal is all that is being done.

hk1 said:
There's no way you can format a URL directly to the page you'd like to go to?

if the website was designed with any competence at all, there shouldn't be a way, as this is common security.

LPurvis · Jan 10, 2011

Depending on the type of security you can pass the username and password as part of the MSXML post - rather than as a querystring (which wouldd manifest as the strPost parameter). But I somehow doubt that is the case - and so you are left with the Post method of the IE object model.
I did indeed fail to genercise that Document element (I changed the one above but not the Submit method!)
As you'll have already found and been pointed to - what you require is in the source code of the page you're submitting.

Cheers.

darbid · Jan 14, 2011

Just a couple of comments, which may or may not be helpful.

Using the webbrowser means you have to have it somewhere, ie on a form as active x or use IE. The "Purvis Method" simply does away with needing the webbrowser. Otherwise there is no difference as the resulting HTML page you get from the web is simply text - with a webbrowser it reads the text and put it into a picture form. For your purpose and for VBA the text is important.

I assume before you started you have checked the URL that the website is using. Could you simply for example make a URL that takes you to the right page without all the logging in? (I am guessing not as this would be too easy)

Next I think your google example was just an example...otherwise you could use googles or other websites APIs. These allow you to send and get information withOUT a webbrowser. They are http requests and Google for example will give a resonpse in XML format which you then parse.
http://code.google.com/apis/newssearch/

Otherwise I have never had much success with passing usernames and passwords with MSXML post but i think it depends on the website.

Retreiving Website data using VBA (1 Viewer)

ianclegg

Registered User.

the_net_2.0

Banned

ianclegg

Registered User.

the_net_2.0

Banned

LPurvis

AWF VIP

hk1

Registered User.

ianclegg

Registered User.

hk1

Registered User.

the_net_2.0

Banned

LPurvis

AWF VIP

darbid

Registered User.

Similar threads

Users who are viewing this thread