Access to web page. 1

[JPMontreal]

Hi All,
We use for reporting a web page with a lot of fields, I have no control on the web. Somebody have to type in the information submit it and do it again. I get all this info in a database. Is there a way from my database to make some VBA code to populate the fields of a webpage?
Thanks.


Jean-Paul
Montreal
To send me E-Mail, remove “USELESSCODE”.
jp@USELESSCODEsolutionsvba.com

 

[CautionMP]

JPMontreal,
The following thread should get you started.
Microsoft: Access Modules (VBA Coding) Forum:
On Error won't work for Loop Code pasting Info on Internet

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[JPMontreal]

Thanks,
It looks interesting, need to analyze more.
Thanks again.


Jean-Paul
Montreal
To send me E-Mail, remove “USELESSCODE”.
jp@USELESSCODEsolutionsvba.com

 

[CautionMP]

JPMontreal,
I've changed computers so here is another example for you. This code runs in Excel (2k SR-1) and will breakdown the form/field information for a web page. I used Excel because I could review and change the data before building a database mapping.
It has two routines:[ol][li][tt]GetIEApp()[/tt]: Which will grab a specific instance of Internet Explorer based on the window title (I used [tt]www.monster.com[/tt] for this example).[/li]
[li][tt]GetFields()[/tt]: This cycles through all the form/input fileds and dumps them in a spreadsheet to help build the field mappings.[/li][/ol]

[b]Function GetIEApp(WindowTitle As String) As Object[/b]
Dim objShell As Object
Dim objWindows As Object
Dim objWindow As Object
Set objShell = CreateObject("Shell.Application")
Set objWindows = objShell.Windows

For Each objWindow In objWindows
  If objWindow.LocationName = WindowTitle Then
    Set GetIEApp = objWindow
    Exit For
  End If
Next
Set objWindow = Nothing
Set objWindows = Nothing
Set objShell = Nothing
End Function

[b]Sub GetFields()[/b]
On Error GoTo GetFields_Error
Dim objIE As Object
Dim objForms As Object, objForm As Object
Dim objInputElement As Object
Dim lngRow As Long

'The following requires that a IE window is open to [URL unfurl="true"]www.monster.com[/URL]
Set objIE = GetIEApp("[i]Monster Jobs - Get work. Network. Build a better career. Today's the day.[/i]")
'Make sure an IE object was hooked
If TypeName(objIE) = "Nothing" Then
  Stop
  MsgBox "Could not hook Internet Explorer object", vbCritical, "GetFields() Error"
  GoTo Clean_Up
End If

'Get the forms object
Set objForms = objIE.Document.Forms
'Test to see if there are forms before proceding
If objForms.Length <> 0 Then
  'Write the header
  lngRow = lngRow + 1
  With ActiveSheet
    .Cells(lngRow, 1) = "Form_Name"
    .Cells(lngRow, 2) = "Form_ID"
    .Cells(lngRow, 3) = "Element_Name"
    .Cells(lngRow, 4) = "Element_ID"
    .Cells(lngRow, 5) = "Element_nodeName"
    .Cells(lngRow, 6) = "Element_Type"
    .Cells(lngRow, 7) = "Element_Value"
  End With
  'End Header
  
  'Cycle through all the forms in the document
  For Each objForm In objForms
    'Cycle through the input elements in the form
    For Each objInputElement In objForm
      lngRow = lngRow + 1
      Stop
      With ActiveSheet
        .Cells(lngRow, 1) = objForm.Name
        .Cells(lngRow, 2) = objForm.ID
        .Cells(lngRow, 3) = objInputElement.Name
        .Cells(lngRow, 4) = objInputElement.ID
        .Cells(lngRow, 5) = objInputElement.nodeName
        .Cells(lngRow, 6) = objInputElement.Type
        .Cells(lngRow, 7) = objInputElement.Value
      End With
    Next objInputElement
  Next objForm
End If

Clean_Up:
Set objInputElement = Nothing
Set objForm = Nothing
Set objForms = Nothing
Set objIE = Nothing
Exit Sub

GetFields_Error:
Debug.Print Err.Number, Err.Description
Resume Clean_Up

End Sub

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[JPMontreal]

Yes CautionMP, very interesting, and when you put the value in the web form you use the innerText property of the element?
I also found few things that could be interesting, just for everybody information:

http://support.microsoft.com/kb/176792/EN-US/

http://www.eggheadcafe.com/ng/microsoft.public.vb.6.webdevelopment/post250713.asp

Thanks a lot.


Jean-Paul
Montreal
To send me E-Mail, remove “USELESSCODE”.
jp@USELESSCODEsolutionsvba.com

 

[CautionMP]

JPMontreal,
I use [tt].Value[/tt] or [tt].Checked[/tt] depending on the Element type.

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[CautionMP]

Since I'm at it, here is the last piece. This will take the spreadsheet created with [tt]GetFields()[/tt], look for new values to transmit (stored in column [tt]H[/tt]) and send them to the web page forms.

I posted it because I had a heck of time getting Radio buttons and Check Boxes to work when I was originally developing this.

'Globals: These are column numbers
Const Form_name = 1
Const Form_Id = 2
Const InputElement_Name = 3
Const InputElements_ID = 4
Const InputElement_nodeName = 5
Const InputElement_Type = 6
Const InputElement_Value = 7
Const InputElement_SendValue = 8

Sub SetFields()
On Error Resume Next
Dim objIE As Object
Dim objInputElement As Object
Dim lngRow As Long

Set objIE = GetIEApp("Monster Jobs - Get work. Network. Build a better career. Today's the day.")
'Make sure an IE object was hooked
If TypeName(objIE) = "Nothing" Then
  Stop
  MsgBox "Could not hook Internet Explorer object", vbCritical, "GetFields() Error"
  'GoTo Clean_Up
End If


For lngRow = 2 To ActiveSheet.UsedRange.Rows.Count
  If ActiveSheet.Cells(lngRow, InputElement_SendValue) <> "" Then
    With objIE.Document.Forms(CStr(ActiveSheet.Cells(lngRow, Form_Id)))
      If ActiveSheet.Cells(lngRow, InputElement_Type) = "radio" Then
        Set objInputElement = objIE.Document.all.Tags("INPUT").Item(CStr(ActiveSheet.Cells(lngRow, InputElement_Name)))
        objInputElement.Item(CLng(ActiveSheet.Cells(lngRow, InputElement_Value))).Checked = True
        Set objInputElement = Nothing
      ElseIf ActiveSheet.Cells(lngRow, InputElement_Type) = "checkbox" Then
        .all.Item(CStr(ActiveSheet.Cells(lngRow, InputElement_Name))).Checked = True
      Else
        .all.Item(CStr(ActiveSheet.Cells(lngRow, InputElement_Name))).Value = CStr(ActiveSheet.Cells(lngRow, InputElement_SendValue))
      End If
    End With
    If Err.Number <> 0 Then
      Debug.Print "Error Writting: Row " & lngRow, ActiveSheet.Cells(lngRow, InputElement_Name), ActiveSheet.Cells(lngRow, InputElement_SendValue)
      Err.Clear
    End If
  End If
Next lngRow
Set objIE = Nothing
End Sub

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[JPMontreal]

Hi CautionMB,
Thanks for the latest information.
Regards.


Jean-Paul
Montreal
To send me E-Mail, remove “USELESSCODE”.
jp@USELESSCODEsolutionsvba.com