IE Automation with pasting table data to IE form

[JMANTN]

Instead of completely hijacking another thread I figured I'd start my own.

I have a database (access 2007) that tracks training information for my site and to expedite some of the processes we have to follow I've incorporated IE automation in my db. The website I'm connecting to is a third party and does have a password protected site but I doubt the site specifics will be necessary for what I need.

I'm having issues with copying a table in my database ("tblCE_QBTest") and pasting the contents into a form on IE (6.0). I have the correct element ID as I've been able to manually insert text into the form however I can't figure out how to paste the table's data.

My tblCE_QBTest has 3 fields:
-Related Class
-Related Student
-Status

I need the contents of all three fields pasted into the form.

here is my existing code:

Private Sub Mix_Click()

On Error Resume Next

Dim IE As Object
Dim document, element
Dim btn As HTMLButtonElement
Dim rs As ADODB.Recordset
'Dim db As ADODB.Database


Set IE = CreateObject("internetexplorer.application")
IE.Navigate "[URL unfurl="true"]https://www.quickbase.com/db/bc68fkzzg?a=ImportExport"[/URL]
IE.Visible = True

Do While IE.Busy: DoEvents: Loop
Do While IE.ReadyState <> 4: DoEvents: Loop

For Each btn In IE.document.all.tags("Input")
If btn.Value = "ImportClipboard" Then
Call btn.Click
End If
Next btn

    'Loop unitl ie page is fully loaded
    Do Until IE.ReadyState = READYSTATE_COMPLETE
        DoEvents
        Loop
    Do Until IE.document.ReadyState = "complete"
        DoEvents
        Loop

IE.document.all.Item("table").Value = "bc68fkzzi"

Do While IE.Busy: DoEvents: Loop
Do While IE.ReadyState <> 4: DoEvents: Loop


'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
'Section below is the part I'm having issues with as
'it stands it's only pasting one record and not from all 'three
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM tblCE_QBTest", CurrentProject.Connection, adOpenForwardOnly, _
     adLockReadOnly, adCmdText

    Do While Not rs.EOF
    IE.document.all("clipboarddata").Value = rs("Related Class")
    IE.document.all("clipboarddata").Value = rs("Related Student")
    IE.document.all("clipboarddata").Value = rs("Status")
    rs.MoveNext
   
    Loop
    rs.Close


'commented code below works alone to put data in form    
'IE.document.all.Item("clipboarddata").Value = ("hey")
     
     Set rs = Nothing


    'Loop unitl ie page is fully loaded
    Do Until IE.ReadyState = READYSTATE_COMPLETE
        DoEvents
    Loop
    Do Until IE.document.ReadyState = "complete"
        DoEvents
    Loop


    'Commented out until I can get copy/paste working
    'For Each btn In IE.document.all.tags("Input")
    'If btn.Value = "Import Data..." Then
    'Call btn.Click
    'End If
    'Next btn


Do While IE.Busy: DoEvents: Loop
Do While IE.ReadyState <> 4: DoEvents: Loop

Set IE = Nothing
End Sub

 

[JMANTN]

I may be on to something but still need help.

Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM tblCE_QBTest", CurrentProject.Connection, adOpenForwardOnly, _
     adLockReadOnly, adCmdText

    Do While i < 50 And Not rs.EOF
        For j = 0 To rs.Fields.count - 1
        Debug.Print rs.Fields(j).Value,
        Next
        Debug.Print
    
       rs.MoveNext
       i = i + 1
       Loop

This doesn't paste anything into the web form but in the debug window shows perfectly so I'm trying to figure out how to get this to go into the webform.

Any and all help is greatly appreciated.

 

[JoeAtWork]

    Do While Not rs.EOF
    IE.document.all("clipboarddata").Value = rs("Related Class")
    IE.document.all("clipboarddata").Value = rs("Related Student")
    IE.document.all("clipboarddata").Value = rs("Status")
    rs.MoveNext
   
    Loop

What you are doing there is repeatedly overwriting the value of IE.document.all("clipboarddata").Value. I'm guessing what you really want to do is paste in all the values of those 3 fields from all records?

Currently, the only thing you will paste in is the "Status" value of the very last record. If that happens to be a blank string, it would appear as if nothing had happened.

If what you actually want is one long string with all values from all records combined, you will need to concatenate them together.

    Dim CombinedValue As String

    Do While Not rs.EOF
        CombinedValue = CombinedValue & " " & rs("Related Class") & " " & _
          rs("Related Student") & " " & rs("Status")
    rs.MoveNext
   
    Loop

    IE.document.all("clipboarddata").Value = CombinedValue

And by the way, NEVER do this at the beginning of your functions:

On Error Resume Next

What you are telling it to do is if there's an error, ignore it and go on to the next line of code. How can you debug if you never find out what the errors are? Take that line out so that you can find the errors.

 

[JMANTN]

JoeAtWork,

I've thanked you for your posts on here and wanted to say your help was invaluable. I have read about concatenating but I had completely forgot about it. With some minor adjustments with the formatting I was able to finish this aspect of my database and learned some invaluable lessons along the way.

Thanks for the thorough explanation and pointers as well