Log In
Register
Home
July 7, 2020
Offline
I want to get data tables from the website http://emonitoring.pu.go.id/ie...../paket2020. I have successfully logged in to the website, but I cannot retrieve data table elements. I have inspected element of the page and found the element id of tag table is "csstab1"
However, when i'm running The code, it allways said Run-time Error '91' Object Variable or with block variable not set. Could you give me some help?
See Answer
October 5, 2010
Offline
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
July 7, 2020
Offline
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".
October 5, 2010
Offline
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.myonlinetraininghu.....g-vba-code
https://www.myonlinetraininghu.....ugging-vba
Regards
Phil
July 7, 2020
Offline
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/ie.....rja_output there is tag and id for the table :
is the code
Set HTMLPage = ieApp.Document
Set HTMLTable = HTMLPage.getElementById("csstab1")
already correct?
Thanx
October 5, 2010
Offline
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
July 7, 2020
Offline
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
October 5, 2010
Offline
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.myonlinetraininghu.....-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
July 7, 2020
Offline
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
October 5, 2010
Offline
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
Answers Post
July 7, 2020
Offline
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
July 7, 2020
Offline
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
October 5, 2010
Offline
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
1 Guest(s)
