Extract 2 elements from HTML table (code + site provided)

[efarkouh]

I am trying to extract the LEI and name from the following website:

https://www.gmeiutility.org/search.jsp

As an example, search for "Pafco Insurance Company".

The first column contains LEI and the second contains legal name. I want to copy these first two values from the two columns and paste them into my sheet.

How would I do that?

This is what I have so far.

Please Login or Register  to view this content.

Two other questions (more out of interest than necessity):

1. What's the best way to handle "waiting"? I have

Please Login or Register  to view this content.

but that doesn't feel too elegant.

2. Is there a way to do this without actually opening internet explorer?

[AB33]

Do you want to get all the data on the table or first two rows?

[efarkouh]

Just the first value from each of the first two columns. Only the first value in LEI and the first value in Legal Name.

[AB33]

I have attached both codes, one for all data and second one just the first two.

[efarkouh]

Thanks a ton for this.

I'm not familiar with Option Explicit. So here's my version of the code for anyone who needs it.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

I'll be testing for a bit before I mark as solved.

[AB33]

Option Explicit forces you to declare all your variables. It is not only a good coding practice, but also saves you lots of time by spotting typos.
I did not pay attention to it while I was learning, but now I can not imagine writing a code with out it.

[efarkouh]

Option Explicit forces you to declare all your variables. It is not only a good coding practice, but also saves you lots of time by spotting typos.
I did not pay attention to it while I was learning, but now I can not imagine writing a code with out it.

That clear it up. Thanks again. I'll try to incorporate that into my code more often then.

I'm at home now, but before I left work I was having an issue when I was try to run this in a loop where it would give me a "Permission Denied" error. The error came up when it tried to change the name in the search bar to the next legal name. I'll have more detail about the issue when I get to work tomorrow, but it would be great if you had any idea what this could be.

[efarkouh]

Since I couldn't get it to loop, I tried to do each one by using selection.value in the search_field. But I ran into another issue, which is closing each instance of ie. ie.Quit doesn't work. Is there any other way to do that?

[AB33]

Yes, you can loop.
Attach a sample excel book with values you need to search in column A. I presume these are company names.

[AB33]

I used the company names in column starting from A2.

[efarkouh]

Thanks a lot for this. It just made my life a ton easier.