Is it possible to Control an Intranet Page using VBA

[RECrerar]

Hi,

I am wondering if it is possible to automate adding data to an Internet/Intranet (this is on the companay Intranet) if you know that all Key presses required?

Specifically I want to pull information off of our time booking system, to do this you have to open the web page then enter information such as the project code, and time range into a series of text boxes and then press enter. All this can be can with the Keyboard. I would like access to do this automatically every week.

Apparently (from the guy next to me) this is possible in Excel as long as the key order doesn't change and therefore I was wondering if it is also possible in Access and if so if someone could point me towards some resources on how to get started or useful areas of the help system?

Thanks, Robyn

 

[RECrerar]

By the way I have now found the ActiveX Microsoft web brouser control, so I now have the page I want opening in a form, whish seem slike a good start.

Am now looking into how to control it using VBA. I am assuming now that this is possible, please correct me if I'm wrong.

Also the report when run extrnally to Access generates the data in an Excel format. I will want to import that data into a table. Will I be able to do this through the mocrosoft web browser page control?

 

[RECrerar]

Okay, just a quick further update.

I have been looking into the SendKeys method, but primarily I can't get this to work (although it does work when I create it as a macro but not in VBA) and having a look round the internet it appears to be a very buggy method, but I haven't found any sort of viable alternative yet.

Does anyone have any ideas on how to enter data into a web sheet remotely or am I wasting my time trying. I would really like this to be an automatic featuer, but I guess it won't bee too much of a problem if it is not.

 

[georgedwilkinson]

Hi Robyn,

You can do almost anything that needs to be done with the Internet Explorer ADO object(s). I tried the sendkeys() method and got so frustrated that I eventually figured out how to bend the Explorer object to my will. That was many years ago and I don't know where my old code is. I can barely remember how to do anything with it now. But there are lots of samples on the inter-web-thingy.

 

[RECrerar]

Ahh, excellent. I don't know anything about that yet, but the sendkeys are all ready driving me up the wall so I will look into it now (well probably monday as it is very nearly hometime, yey!), sounds much more promising. Thanks

 

[georgedwilkinson]

I'm gonna drag out an old computer at home that might have some sample code. Another poster needed something similar yesterday and now the problem is driving me crazy.

If you still need help after the weekend, just PM me with a link to this thread and we'll see if I found anything.

 

[RECrerar]

Hi again all,

So I have found two methods of doing this, the simpler but less reliable is using Sendkeys and I have this working. The other method is using ADO objects as shown in this ling (the linked code is for Excel but is a good starting point for figuring out how to do it in Access) http://www.tek-tips.com/faqs.cfm?fid=6399

I do have a problem with it though, I can open up the wedbrowser and enter values into the text fields, however there are a few option buttons that I need to change the values of. The code suggested used the Element_ID, however when I run the code to get the information from the web page, the option buttons (radios) do not have element IDs, is it therefore not possible to change their value using code except for using sendkeys?

I know this is probably not the easy to understand but it should make sense if you look at the link.

 

[RECrerar]

Okay, I have now managedt o write the code that enters the values into the web page, all of this is now done without having to rely on sendkeys. I also found it was quicker to do it directly through IE so am no longer using a form in Access but connecting directly.

Option Compare Database
Option Explicit
Dim objParent As Object
Dim objInputElement As Object
Dim rb As Object
Sub GetTabsData()
Dim ieApp As New InternetExplorer
'Connect to the website
ieApp.Navigate ("[URL]http://www-mms[/URL].......") 'this is the web address
ieApp.Visible = True
'Allow time for page to load
Do While ieApp.Busy
    DoEvents
Loop
Do While ieApp.ReadyState <> READYSTATE_COMPLETE
    DoEvents
Loop
Set objParent = ieApp.Document.all
'Enter the values in the text boxes on the page
'The names of the boxes were get using the Excel code
objParent.Item("fldProject").Value = "A11111"
objParent.Item("fldChargeNo").Value = "*"
objParent.Item("fldDept").Value = "*"
objParent.Item("fldWeekFrom").Value = "0726"
objParent.Item("fldWeekTo").Value = "0832" 'Make this a variable
'Set the values of the option buttons.
Set objInputElement = objParent.tags("INPUT").Item("grpPeriod")
For Each rb In objInputElement
    If rb.Value = "P" Then
        rb.Checked = True
    Else
        rb.Checked = False
    End If
Next
Set objInputElement = Nothing
Set objInputElement = objParent.tags("INPUT").Item("grpTsht")
For Each rb In objInputElement
    If rb.Value = "all" Then
        rb.Checked = True
    Else
        rb.Checked = False
    End If
Next
Set objInputElement = Nothing
'Click the commandbutton called "Excel Version"
Set objInputElement = objParent.tags("INPUT").Item("btnRun")
For Each rb In objInputElement
    If rb.Value = "Excel Version" Then rb.Click
Next
Set objInputElement = Nothing
Set ieApp = Nothing
End Sub

The above code fills in all the boxes and elects the right options and presses Enter, this then brings up a message box. I want to from this either select Open or Save (which ever will be the easier option for importing the data into Access) automatically. I now recon I may be able to figure this out myself , but if anyone already knows how to do it, that would obviously be quicker. Thanks, Robyn

 

[georgedwilkinson]

Hi Robyn,

If you still need help on using the inner html to POST, let me know. I'll dig up my old code (just for you!).

 

[RECrerar]

Okay this is where I really show my ignorance, what do you mean by?

inner html to POST

I am still a bit stuck on how to automate the download dialog box. I have it working using Sendkeys and wait statements inbetween, but this isn't ideal. I had a good old troll around the internet and found a fair few threads about this issue but none of them had a conclusive "this is how you do it section". I believe you can use the findwindow and sendmessage commands to tell you when the dialog box has appeared, but you need to know what the window is called and I have no idea on the official name of the dialog box in computer terms.

So if do no anything that may help that would still be appreciated

I'll dig up my old code (just for you!).

Aww you are too kind!

 

[georgedwilkinson]

Sorry, I've been kinda busy since my last post.

A POST is web talk for sending information (like stuff in a form) to a web server for processing. Inner html is a sub-object in the Internet Explorer Active X library. When you get a page that requires you to "POST" data to the web server, you can totally "rip out" the "inner html" of the page you are "POST"ing from with your own inner html (supplied with your data) and POST that. This prevents you having to tab around on a page and send enter keys and so-forth.

This really takes a pretty good understanding of the source code of the web site(s) you are automating and a pretty good understanding of html in general. It is, unfortunately, very specific to each web page's implmentation.

Yell if you need more information/specific help.

 

[georgedwilkinson]

I was able to finally find something I wrote a long time ago to control a web based video game. I looked at it and realized that it wasn't my code (doesn't use any of my naming conventions) but that I had marked it up pretty badly. So, I did a google on some of the unique coding convention stuff and found the source code located at http://www.eggheadcafe.com/articles/20011215.asp.

You'll notice that this is VB Script, not VBA. I had to adapt it to my circumstances. Here is my marked up version (after modifying it enough to protect, well, me):

Sub Submitter(Optional p_strNavigate As String = "about:blank", Optional p_strForm As String = "<Form name=frm1 id=frm1 action=http://www.anunknownsite.com/process_bank.phtml METHOD=POST><input type='hidden' name='type' value='deposit'><b>Amount in FakeMoney</b><input type='text' name='amount' value='100' size=10 maxlength=10><input type='submit' value='Deposit'><input type=submit></form>")
    Dim IE: Set IE = CreateObject("InternetExplorer.Application")
Dim strn As String
Dim shop As String
Dim i As Integer
Dim b As Boolean
Dim name As String
Dim hypr As String
    IE.navigate p_strNavigate '<-- You have to do this first!!!!
    Do While IE.Busy
        DoEvents
    Loop
    DoEvents
    IE.Document.body.innerHTML = p_strForm
    Call IE.Document.frm1.submit
    Do While IE.Busy
        DoEvents
    Loop
    strn = IE.Document.body.innerHTML
    strn = Right(strn, Len(strn) - InStr(1, strn, "Shop Owner"))
    strn = Right(strn, Len(strn) + 1 - InStr(1, strn, "<A"))
    i = 0
    IE.Visible = True
    b = True
    On Error GoTo ErrorHandler5
    Do While i < 20 And b = True
        shop = Left(strn, InStr(1, strn, "</A>") + 3)
        If b = False Then Exit Do
        strn = Right(strn, Len(strn) - InStr(10, strn, "<A"))
        If b = False Then Exit Do
        'If MsgBox(shop, vbOKCancel) = vbCancel Then
        '    Exit Do
        'End If
        hypr = "[URL]http://www.anunknownsite.com/[/URL]" & Mid(shop, 9, InStr(10, shop, "><B>") - 10)
 
        name = Mid(shop, InStr(1, shop, "<B>") + 3, InStr(4, shop, "</B>") - InStr(1, shop, "<B>") - 3)
        Worksheets("Sheet2").Select
        Range("A" & Trim(Str(i + 1))).Select
        ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=hypr _
        , TextToDisplay:=name
        Range("B" & Trim(Str(i + 1))).Value = name
        DoEvents
        i = i + 1
    Loop
 
    '
    'MsgBox IE.document.body.innerHTML
    MsgBox strn
    IE.Quit
    Exit Sub
ErrorHandler5:
    MsgBox Error
    b = False
    Resume Next
End Sub

Thanks to Peter A. Bromberg, Ph.D. for supplying the original source code.