copy table from website and past into table (1 Viewer)

M

megatronixs

Registered User.
Local time
Today, 12:21
Joined
Aug 17, 2012
Messages
719
Hi all,

I made some code work to login into a intranet site and then after login in go to the page after filling in some fields there, I get a results in a new page. I want to get the data from there. So far I made it work with Excel, but the idea is to have Access for this.
This is the code part from Excel:
Code: 
   'copy the tables html to the clipboard and paste to teh sheet
    If Not ieTable Is Nothing Then
        Set clip = New DataObject
        clip.SetText "<html>" & ieTable.outerHTML & "</html>"
        clip.PutInClipboard
        Sheet1.Select
        Sheet1.Range("A1").Select
        Sheet1.PasteSpecial "text"
    End If

How to do this in Access?
This is the code so far for access:
Code: 
Private Sub btn_open_and_get_data_Click()
Dim ie As Object
    Dim Form As Variant, button As Variant
    Dim Str As String
    Dim objIe As Object
    Dim xobj
    Dim vfrr As HTMLTextElement
    Dim HTMLdoc As MSHTML.HTMLDocument
    Dim FinalRow As Long, i As Long
    Dim Client As String
    Dim Account As String
    Dim PDF As String
    Dim ieDoc As Object
    Dim ieTable As Object
    Set ie = CreateObject("InternetExplorer.Application")
    ie.Visible = True
    ie.navigate "[URL]https://my[/URL] intranetsite"
    While ie.ReadyState <> 4
        DoEvents
    Wend
While ie.Busy Or ie.ReadyState <> READYSTATE_COMPLETE: DoEvents: Wend
Set HTMLdoc = ie.Document.Forms("Home").Document
HTMLdoc.getElementById("txtUserName").Value = Me.txt_user.Value
HTMLdoc.getElementById("txtPassword").Value = Me.txt_password
HTMLdoc.getElementById("login").Click
While ie.Busy Or ie.ReadyState <> READYSTATE_COMPLETE: DoEvents: Wend
While ie.Busy Or ie.ReadyState <> READYSTATE_COMPLETE: DoEvents: Wend
Set HTMLdoc = ie.Document.Forms("analystSearchForm").Document
HTMLdoc.getElementById("showID").Click
While ie.Busy Or ie.ReadyState <> READYSTATE_COMPLETE: DoEvents: Wend
Set HTMLdoc = ie.Document.Forms("analystSearchForm").Document
HTMLdoc.getElementById("initiationTest").Value = "Test 3"
Set HTMLdoc = ie.Document.Forms("analystSearchForm").Document
HTMLdoc.getElementById("initiationTest").Value = "Test 1"
While ie.Busy Or ie.ReadyState <> READYSTATE_COMPLETE: DoEvents: Wend
Set HTMLdoc = ie.Document.Forms("analystSearchForm").Document
HTMLdoc.getElementById("unassignedInitiations").Checked = True

While ie.Busy Or ie.ReadyState <> READYSTATE_COMPLETE: DoEvents: Wend
Set HTMLdoc = ie.Document.Forms("analystSearchForm").Document
HTMLdoc.getElementById("assignedInitiation").Checked = False
While ie.Busy Or ie.ReadyState <> READYSTATE_COMPLETE: DoEvents: Wend
Set HTMLdoc = ie.Document.Forms("analystSearchForm").Document
HTMLdoc.getElementById("searchButton").Click
'
While ie.Busy Or ie.ReadyState <> READYSTATE_COMPLETE: DoEvents: Wend
    'get the table based on the table’s id
    Set HTMLdoc = ie.Document
    HTMLdoc.getElementById ("tableGrid")
    Set ieTable = HTMLdoc.all.Item("tableGrid")
    'copy the tables html to the clipboard and paste to the table
    If Not ieTable Is Nothing Then
    End If
End Sub

I hope some one can help :)

Greetings.
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:21
Joined
May 7, 2009
Messages
19,233
you can still use your excel code in access, just make a reference to Microsoft excel xx.x object library.
 
M

megatronixs

Registered User.
Local time
Today, 12:21
Joined
Aug 17, 2012
Messages
719
Hi all,

after your tips, still nothing :-(
I simply can't get the content of the table.

Greetings.
 
M

megatronixs

Registered User.
Local time
Today, 12:21
Joined
Aug 17, 2012
Messages
719
Hi all,

I get in access the error: compile error, user defined type not defined
with the "Dim clip As DataObject"
It works in excel, but maybe not in access.

Greetings.
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:21
Joined
May 7, 2009
Messages
19,233
DataObject can be found in MS Form 2.0, make reference to this object. If you cannot find it in the list use "browse", on windows\system32, you will find fm20.dll choose this one.
 
M

megatronixs

Registered User.
Local time
Today, 12:21
Joined
Aug 17, 2012
Messages
719
HI arnelgp,

BIG thanks :)
the only thing now I have is that it tries to get the html format and I can't get this in the table.
I need a way to loop inside the table for all the rows and then put them in the access table.
Any clue if that is possible?..
Greetings.
 
M

megatronixs

Registered User.
Local time
Today, 12:21
Joined
Aug 17, 2012
Messages
719
Hi all,

I found some intersting code in this link:
http://stackoverflow.com/questions/16840897/get-data-from-an-html-table-into-an-access-database

and was hoping that the code cold be modified to be just doing what I would really need. I tried my luck to pass the table from the website into it, but just no luck. Can some one set me up in the right direction?

Code: 
rstHtml.Open "SELECT * FROM [tableGrid]", con
Code: 
Sub ImportFromHtml()
Const LocalTableName = "DataFromHtml"
Dim con As Object, rstHtml As Object, fld As Object, _
        cdb As DAO.Database, rstAccdb As DAO.Recordset, _
        recCount As Long

Set con = CreateObject("ADODB.Connection")
con.Open _
        "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=C:\Users\Gord\Documents\table.htm;" & _
        "Extended Properties=""HTML Import;HDR=YES;IMEX=1"";"
Set rstHtml = CreateObject("ADODB.Recordset")
rstHtml.Open "SELECT * FROM [Test Data]", con

Set cdb = CurrentDb
cdb.Execute "DELETE FROM [" & LocalTableName & "]", dbFailOnError
Set rstAccdb = cdb.OpenRecordset(LocalTableName, dbOpenTable)

recCount = 0
Do While Not rstHtml.EOF
    recCount = recCount + 1
    rstAccdb.AddNew
    For Each fld In rstHtml.Fields
        rstAccdb.Fields(Trim(fld.Name)).Value = Trim(fld.Value)
    Next
    Set fld = Nothing
    rstAccdb.Update
    rstHtml.MoveNext
Loop

rstAccdb.Close
Set rstAccdb = Nothing
Set cdb = Nothing

rstHtml.Close
Set rstHtml = Nothing
con.Close
Set con = Nothing

Debug.Print recCount & " record(s) imported"
End Sub

Greetings.
 
M

megatronixs

Registered User.
Local time
Today, 12:21
Joined
Aug 17, 2012
Messages
719
Hi all,

I guess it is not possible and I will not have the possibility to make it work.
Such a shame, was so close to get it.

Greetings.
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:21
Joined
May 7, 2009
Messages
19,233
i dont know but have you inspected the "page source" of the html, and investigate what is the name of the table, and the field names in it?
 
M

megatronixs

Registered User.
Local time
Today, 12:21
Joined
Aug 17, 2012
Messages
719
Hi,

I did, but have problems putting it all together. from the above example I found in the link, they have the text htm page saved on disk, I was hopping that I could pass the table name to it and make it work like that.

Greetings.
 
You must log in or register to reply here.

Similar threads

W
Help filling in a web form from access.
Replies
12
Views
524
Edgar_
E
W
Converting IE access VBA code to work with Edge
Replies
4
Views
843
wackywoo105
W
C
Navigating to webpage with VPN
Replies
1
Views
366
D_Walla
D
S
Search HTML for words
Replies
11
Views
655
cheekybuddha
cheekybuddha
C
Insert into syntax error
Replies
8
Views
391
ClaraBarton
C

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom