Scraping text on different tabs with VBA

I need to scrap a portion of the text from a table that is created after I enter an "ID" and and click search on a web page. The table is also located on 1 of 3 tabs that are created after the search is complete. I am currently able to enter the product "ID" and click search creating the table but  am not able to extract the data.
I have been trying to to use:

 Container = IE.document.getElementsByClassName("contId")(0).innertext
Worksheets("Sheet1").Cells(2, 3) = Container 

Open in new window

 

For some reason it seems like VBA doesn't read any of the HTML code on tab-1 which is where the text that I need to scrap is. I think this because as I work my way through the elements of the HTML code using getElementById("").outerHtml or getElementByClassName("").outerHtml I can return the line of code. However when I reach the tab I require "tab-1" it skips past to "tab-2". If I use an element that is unique to tab-1 I get Automation Error: Object variable or With block variable not set.

I have attached the HTML code.

Thanks in advance for your help
HTML.jpg
Chris HAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HainKurtSr. System AnalystCommented:
try

Container = IE.document.getElementsByClassName("contId")(0).innerHTML

Open in new window


what is "contId" by the way? any html code?

also you can try

Worksheets("Sheet1").Cells(2, 3).Value = Container

Open in new window

Chris HAuthor Commented:
"ContId" is the class name for the element with the text I require. I have attached the HTML code for the page to the initial question.
HainKurtSr. System AnalystCommented:
is there a way to change the html and make it like

<a class="contId" href="#">
  <span>span text</span>
  some text 324234
</a>

Open in new window


>>>

<a class="contId" href="#">
  <span>span text</span>
  <span>some text 324234</span>
</a>

Open in new window


then can use

Container = IE.document.getElementsByClassName("contId")(0).children(1).innerHTML

Open in new window

Price Your IT Services for Profit

Managed service contracts are great - when they're making you money. Yes, you’re getting paid monthly, but is it actually profitable? Learn to calculate your hourly overhead burden so you can master your IT services pricing strategy.

HainKurtSr. System AnalystCommented:
if it is not possible, then, you need to get rid of span element

something like this (not tested):

Container = document.getElementsByClassName("contId")(0).innerHTML
Container = Trim(Right(Container, Len(Container) - InStr(Container, "</span>") - 7))
Worksheets("Sheet1").Cells(2, 3).Value = Container

Open in new window


in excel, if you put

A1="<span>span text</span>
  some text 324234",

then B1

=TRIM(RIGHT(A1, LEN(A1)-SEARCH("</span>",A1)-7))

Open in new window


will give you "some text 324234"
Chris HAuthor Commented:
Unfortunately that doesn't seem to work. When VBA gets to the line: Container = document.getElementsByClassName("contId")(0).innerHTML I get Run-time error '424': Object required.
HainKurtSr. System AnalystCommented:
you mean "document.getElementsByClassName" does not supported?
or did you forget to add IE in front? basically it should be

Container = IE.document.getElementsByClassName("contId")(0).innerHTML
Container = Trim(Right(Container, Len(Container) - InStr(Container, "</span>") - 7))
Worksheets("Sheet1").Cells(2, 3).Value = Container

Open in new window

Chris HAuthor Commented:
Sorry that was just a typo i did put IE.document.getElementsByClassName("contId")(0).innerHTML and unfortunately it doesn't work as VBA cant find the element I am searching for.  

However I have figured out the issue but have know idea on a solution.

So after I click search the page refreshes/loads which provides the text I require on the page but also alters the HTML from the original page. When I use VBA to fetch the element it searches the original HTML data so therefore doesn't find the element I need. I need to work out how to search the new / amended HTML code that is created after I click search.
HainKurtSr. System AnalystCommented:
so is this giving error

IE.document.getElementsByClassName("contId")(0).innerHTML

or returns null?
is there a way to change the html and put the required data into another span element?

<a class="contId" href="#">
  <span>span text</span>
  <span>some text 324234</span>
</a>

Open in new window


on which line do you get error in this code?

Container = IE.document.getElementsByClassName("contId")(0).innerHTML
Container = Trim(Right(Container, Len(Container) - InStr(Container, "</span>") - 7))
Worksheets("Sheet1").Cells(2, 3).Value = Container

Open in new window


and what is exact error message?
Chris HAuthor Commented:
So I unfortunately changing  the HTML is not an option.

The error occurs on line: Container = IE.document.getElementsByClassName("contId")(0).innerHTML

The error occurs because VBA is searching in the original HTML code of the page before the search button is clicked and therefore can not find the element. The HTML code that contains the element with its class name is only created after the search button is clicked.
HainKurtSr. System AnalystCommented:
ok, got it...
so code works, no issue with getElementsByClassName function or string manipulation codes here...
then you should run your code after button click :)
Chris HAuthor Commented:
The code is being ran after the button click. The problem is VBA still searches the old HTML code after the button click event. I need to figure out how to make VBA read the new HTML code produced by the button click.
HainKurtSr. System AnalystCommented:
hmms...
if you can change the code a bit and store some values in a hidden variable, then you may get the value of that element with VBA after button click... not sure even with this approach, you will get initial empty value or updated value though...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
HTML

From novice to tech pro — start learning today.