Hi Audrian,

You had 2 Subs, one for logging in and one for getting the table.  But when the Login sub finished, the GetTable module had no way to get a connection to IE and the HTML document.  The scope of the variables in each Sub ends when that Sub finishes.  So I merged the code from both Subs.

If a HTML element has an ID it should be unique so there should only ever be one table with the ID csstab1.  The code was looping and looking for multiple tables.

Some of the data type weren't correct so I changed these and I simplified the loop.  You can access HTML Table Row and Cell elements using

HTMLTable.Rows(i).innerText

HTMLTable.Rows(i).Cells(j).innerText

Regards

Phil

thank you for trying to solve my problem. Unfortunately when I try to run your VBA code in module 2 pgt-Get_table.xlsm, run time error '91' still appears in the line "For i = 0 To HTMLTable.Rows.Length - 1". 

Hi Audrian,

That would indicate that something is wrong with HTMLTable.Rows.Length.  Which could mean that there isn't a table stored in HTMLTable.

When you step through the code does it work?

Is there a table in HTMLTable?

What value does HTMLTable.Rows.Length have?

https://www.myonlinetraininghub.com/debugging-vba-code

https://www.myonlinetraininghub.com/more-tips-for-debugging-vba

Regards

Phil

Dear Philip

When i step through the code, i think the problem is there is no table that store to variable HTMLTable

but when i'm inspecting the table in the page http://emonitoring.pu.go.id/iemon/kinerja_output there is tag and id for the table :

is the code

Set HTMLPage = ieApp.Document
Set HTMLTable = HTMLPage.getElementById("csstab1")

already correct?

Thanx

Hi Audrian,

No need to click on the Quote icon when replying, it just adds clutter to the thread and will be deleted 🙂 Please just click on the 'Add Reply' button.

Yes those 2 lines of code are correct.  Every time I run my code it works fine.

If you are inspecting the page and can see the table with id csstab1 then the code should find it.

Are you definitely running the exact code I provided?  You haven't got the Login code in a separate Sub?

You should work backwards through your code from Set HTMLTable = HTMLPage.getElementById("csstab1") to see that all objects and variables contain the values you expect.

If you look in your Locals window, is HTMLPage set to anything?

Regards

Phil

Dear Philip

Yes im definitely running the exact code you have provided in module 2 file pgt-Get_table.xlsm

but when i press run sub (F5)  it allways said Run-time Error '91' Object Variable or with block variable not set.

Set ieDoc = ieApp.Document

'fill in the login form – View Source from your browser to get the control names
With ieDoc.forms(0)
.all.user_ieo.Value = "04498650"
.all.pass_ieo.Value = "PJNi"
.submit
End With
Do While ieApp.Busy: DoEvents: Loop
Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop
'now that we’re in, go to the page we want
ieApp.navigate "http://emonitoring.pu.go.id/iemon/kinerja_output"
Do While ieApp.Busy: DoEvents: Loop

Set HTMLPage = ieApp.Document
Set HTMLTable = HTMLPage.getElementById("csstab1")

Worksheets.Add
Range("A1").Value = HTMLTable.tagName  <------------- error run time
Range("B1").Value = Now

For i = 0 To HTMLTable.Rows.Length - 1

Debug.Print vbTab & HTMLTable.Rows(i).innerText

For j = 0 To HTMLTable.Rows(i).Cells.Length - 1

Cells(i + 2, j + 1) = HTMLTable.Rows(i).Cells(j).innerText

Next j

Next i

ieApp.Quit
Set ieApp = Nothing
Set ieDoc = Nothing
Set HTMLTable = Nothing

End Sub

Hi Audrian,

Use F8 to step through the code.  You can see exactly what is happening then, check your variables and objects etc.  Please read those debugging articles I linked to above.

I suspect IE may be reporting that it is ready before the HTML document is fully loaded.  So when VBA tries to Set HTMLPage = ieApp.Document there's nothing there or an incomplete HTMLpage.

Try this: immediately before Set HTMLPage = ieApp.Document insert this

Application.Wait (Now + TimeValue("0:00:10"))

taken from https://www.myonlinetraininghub.com/pausing-or-delaying-vba-using-wait-sleep-or-a-loop

That call to Application.Wait will make the code pause for 10 seconds which should be enough time for IE to load the document completely.

Either way, use F8 to step through the code and check that HTMLPage and HTMLTable are set to something.

Regards

Phil 

dear Phillips

i have try insert Application.Wait (Now + TimeValue("0:00:10")) before 

Set HTMLPage = ieApp.Document 

and then Using F8 to step through the code and it's works !

but when i'm using F5 to running  show new error:

VBA Error:  The object invoked has disconnected from its clients

Hi Audrian,

You need to let me now what line is generating that error.  What line is highlighted in yellow in the debugger?

I'd guess that the VBA has lost the connection to IE.  Had IE closed when that error was generated?

Not sure what else to advise.  The code works fine for me so the issue would appear to be specific to your machine, and only when you run the code.  Stepping through you said it worked fine.  So something is wrong with the execution of the code in real time.  Maybe IE is not behaving as it should.

Have you tried the code on another PC?

What version of Windows, Office and IE have you got installed?

Regards

Phil

Dear philip

Im using windows 10 64 bit, office 365 and IE 11

When im trying in another excel (excel 2010) the code work perfectly

Dear Philip

I have Reset  Factory my Laptop and then reinstal my office....and the code goes perfectly !

Thank you for your help, really apreciate it !

Just one question if you dont mind

i inspected some page, there are 2 table with same id "csstab1" i just want to get second table, but the problem is when i run the code i just get first table in sheet finance that i dont want it

Hi Audrian,

Glad you got it sorted out.

If there are 2 tables with the same id then the HTML is invalid/incorrectly written. An id should be unique.  As it isn't in this case you can't use the id to identify the table.  You'll need to look for something else to uniquely identify the table you want.

Can you save the source code of a page that has 2 tables with the same id, then attach it here and I can take a look to see what can be done.  You may need to zip it up or change the file extension from .htm to .txt so it can be attached.

Regards

Phil

Dear Philip

Thank you for your help