• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • SALE 20% Off All Courses
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member
  • Login

Get Data from Website that need login using VBA Excel|VBA & Macros|Excel Forum|My Online Training Hub

You are here: Home / Get Data from Website that need login using VBA Excel|VBA & Macros|Excel Forum|My Online Training Hub
                    BLACK FRIDAY SALE 20% OFF ALL COURSES
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumVBA & MacrosGet Data from Website that need log…
sp_PrintTopic sp_TopicIcon
Get Data from Website that need login using VBA Excel
Avatar
Audrian Herdy
Member
Members
Level 0
Forum Posts: 8
Member Since:
July 7, 2020
sp_UserOfflineSmall Offline
1
July 7, 2020 - 9:23 am
sp_Permalink sp_Print

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? 

sp_AnswersTopicSeeAnswer See Answer
Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1563
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
2
July 7, 2020 - 3:17 pm
sp_Permalink sp_Print

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

Avatar
Audrian Herdy
Member
Members
Level 0
Forum Posts: 8
Member Since:
July 7, 2020
sp_UserOfflineSmall Offline
3
July 8, 2020 - 12:44 am
sp_Permalink sp_Print sp_EditHistory

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". 

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1563
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
4
July 8, 2020 - 9:28 am
sp_Permalink sp_Print

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

Avatar
Audrian Herdy
Member
Members
Level 0
Forum Posts: 8
Member Since:
July 7, 2020
sp_UserOfflineSmall Offline
5
July 8, 2020 - 10:19 am
sp_Permalink sp_Print sp_EditHistory

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
Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1563
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
6
July 8, 2020 - 1:08 pm
sp_Permalink sp_Print

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

Avatar
Audrian Herdy
Member
Members
Level 0
Forum Posts: 8
Member Since:
July 7, 2020
sp_UserOfflineSmall Offline
7
July 8, 2020 - 1:45 pm
sp_Permalink sp_Print

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

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1563
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
8
July 8, 2020 - 3:50 pm
sp_Permalink sp_Print sp_EditHistory

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 

Avatar
Audrian Herdy
Member
Members
Level 0
Forum Posts: 8
Member Since:
July 7, 2020
sp_UserOfflineSmall Offline
9
July 8, 2020 - 4:47 pm
sp_Permalink sp_Print

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

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1563
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
10
July 9, 2020 - 12:53 pm
sp_Permalink sp_Print

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

sp_AnswersTopicAnswer
Answers Post
Avatar
Audrian Herdy
Member
Members
Level 0
Forum Posts: 8
Member Since:
July 7, 2020
sp_UserOfflineSmall Offline
11
July 9, 2020 - 6:40 pm
sp_Permalink sp_Print

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

Avatar
Audrian Herdy
Member
Members
Level 0
Forum Posts: 8
Member Since:
July 7, 2020
sp_UserOfflineSmall Offline
12
July 10, 2020 - 11:33 am
sp_Permalink sp_Print sp_EditHistory

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

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1563
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
13
July 10, 2020 - 12:32 pm
sp_Permalink sp_Print

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

Avatar
Audrian Herdy
Member
Members
Level 0
Forum Posts: 8
Member Since:
July 7, 2020
sp_UserOfflineSmall Offline
14
July 10, 2020 - 5:40 pm
sp_Permalink sp_Print

Dear Philip

Thank you for your help

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Wang, Jude Livingston, Melanie Toye, Richard Cook
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
Catalin Bombea: 1864
SunnyKow: 1432
Anders Sehlstedt: 886
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 241
Hans Hallebeek: 225
Jessica Stewart: 219
A.Maurizio: 213
Newest Members:
Robert Corbyn
Tamara Nelson
Phil Collins
Robert Moran
Mark Niemiec
Michael Groh
Paula Holbrook
Russ Cockings
Huub Poppelaars
Pieter Brandsen
Forum Stats:
Groups: 3
Forums: 24
Topics: 6639
Posts: 29131

 

Member Stats:
Guest Posters: 49
Members: 33081
Moderators: 2
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Jessica
Moderators: Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel Office Scripts
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook

Sign up to our newsletter and join over 400,000
others who learn Excel and Power BI with us.

 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate
  • Sponsor Our Newsletter

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.