CSS Selector invalid use of Null tables length property

[YasserKhalil]

Hello everyone

In my case, I have a HTML local file which has a table. Here's a link of the contents of HTML http://try.jsoup.org/~LsArJXLumBWg2HOgluVzOXm9bRs When manually using this selector table[width='100%'] table:first-child I could get results with no problem at all. In my excel VBA code, I got these two lines

Please Login or Register  to view this content.

Now I got an error Invalid use of NULL at this line For i = 89 To tables.Length - 17 Step 26 and I have used the immediate window to see tables.Length and I got NULL. The code was working before without any problem but now it throws this error. I have inspected the local window and found that the tables is an object like that

LA1Tr.png

But when trying to expand the variable in the immediate window, excel hangs and exits.

This is how I deal with the local html file

Please Login or Register  to view this content.

[Marc L]

Hi,

as on my side querySelectorAll is a mess so try another way …

[YasserKhalil]

The weird is that it was working before with no problems at all.

[Marc L]

I never had any success with it on several computers with different Excel & Windows version …

[YasserKhalil]

So what is the best alternative to using the querySelectorAll?

[Marc L]

As usual one of the classic getElements by Class, Tag, … according to the webpage logic.

[YasserKhalil]

I have put a link to html sample. Can you please have a look at it as it is complicated and there are stuff of nested tables? I have no clue except to use querySelectorAll.

[karedog]

Maybe :

Please Login or Register  to view this content.

[YasserKhalil]

Hello my tutor. Thanks a lot for sharing.
I have tested the code and I got an error `Subscript out of range` at this line

Please Login or Register  to view this content.

Inspecting the z.Count in the immediate window, I got 0 value.
I have followed the code and in the line

Please Login or Register  to view this content.

I suspect it is working as the condition doesn't meet at all on my side.

[YasserKhalil]

By the way, I put this line

Please Login or Register  to view this content.

and all I got is NULL in the immediate window.

[karedog]

Hi Yasser,

Does it worked for the file "Test.html" in attached zip file ?

[YasserKhalil]

I have just tested the files you attached. I didn't test on my files yet.

[karedog]

Ok, I found something on web : https://www.w3schools.com/jsref/met_...tattribute.asp
--> var x = document.getElementsByTagName("H1")[0].getAttribute("class");

Here the code use "class" insted "classname" (probably different ie version, mine is ie8, yours must be higher).

Please try change the code to :

Please Login or Register  to view this content.

and see if this changed anything

[YasserKhalil]

Amazing. That trick worked very well.
Thank you very much for the great support.

[karedog]

You are welcome, thanks for marking the thread as solved and rep.points given.

Regards

[YasserKhalil]

I have tried another HTML file but found the class name is different. It looks like that `ad389714bc-5647-4569-91d1-617650409c43-2`.
Is there a more flexible way to get such an attribute first and make it a variable? I mean that the code should detect that class name automatically.
As I have a lot of HTML files and it will not be acceptable to change the code for the class name each time.

[karedog]

Is this a good one ?

Please Login or Register  to view this content.

[YasserKhalil]

Thanks a lot. This version works partially (I didn't get the second and third columns). Also the data I think needed to be cleaned.

[karedog]

Since the webpage is generated by javascript (so it is always changed), the solution is trivial.
I try to just using the classname begin with "ad", and it is good for the initial file (don't know for your other files), if this doesn't give expected result, I don't know anything else to do.

Please Login or Register  to view this content.

[YasserKhalil]

I got invalid use of NULL at this line

Please Login or Register  to view this content.

The previous code was working except for the second and the third column (doesn't return any values)

[karedog]

Sorry, it should be "class" (ie9++) not "classname" (my side, ie8), same as before, I forget to change it to ie9 style.

But after trying more, just using the div alone (not checking the classname), with using filtering using number of columns < 10 in a row, also give a good result, please try :

Please Login or Register  to view this content.

[YasserKhalil]

It seems there are data for the second and third column but the data doesn't appear for me directly (I have to widen the height of the rows a lot to see them)
Last point as for the last code, how to clean the data so as to get rid of the extra spaces and I don't need the apostrophe.

[AliGW]

Thread marked as solved ...

[YasserKhalil]

Yes solved with 95% and this is an excellent solution.

[karedog]

Add clean up code before writing back to sheet :

Please Login or Register  to view this content.

I still place the apostrophe on the date column (column 7), since if regional setting is not matched this will give a problem as usual.

[YasserKhalil]

Thank you very much. In the fourth column for names as example I have noticed there is empty line and I tried to change this line

Please Login or Register  to view this content.

and I used Replace and tried Chr(10) and Chr(13) but I got the same output.

[YasserKhalil]

I have used the replacement for both Chr(10) and Chr(13) and everything is OK now.

Before running the code, I have formatted all columns as text as I need to deal with the data as Text only not as dates nor numbers.
Thank you very much for the incredible help in this topic.

[karedog]

You should examine what character(s) that make the new line (if chr10 and chr13 not worked, probably another unicode character(s)) and then remove them.

As for second and third column not appeared, you can try and change this value to bigger value (for example : 20) and see what happened

Please Login or Register  to view this content.

The table is very badly formed, each cell in a row not having a same "Top" property.

Sorry, I have go back to work now, I'll check later if I have a time to do so.

[YasserKhalil]

Thank you very much. Now it is solved completely
Best and Kind Regards my tutor.

[karedog]

You are welcome, thanks for re-marking the thread as solved.

Regards

[YasserKhalil]

My tutor. I apologize for making the thread like that.
Now I can use the code you posted but I noticed that the files sometimes are grabbed twice ( I am not sure what is wrong with my code)
Can you please have a look? I have three HTML local files and I attached the code in a text file

[karedog]

Variable a and p need to reset too :

Please Login or Register  to view this content.

[YasserKhalil]

Thanks a lot. When I added another folder which contains other HTML files, I didn't get all the results.

[karedog]

Because cnt is always set to 1 (cnt = 1) everytime Sub ParseMOETable is called (every subfolder), so for everytime subfolder is called, the position of writing of array is always reset to row 1.

Define cnt in the caller sub (Sub Pasre_All) and then pass this var as an argument to Sub ParseMOETable, so it won't be reset between every call (or you can also define this var as a module public variable)

Please Login or Register  to view this content.

[YasserKhalil]

Thank you very much, my friend. You are so awesome.

[karedog]

You are welcome, glad to help.

Regards

[YasserKhalil]

Hello my tutor. Can you have a look at this link https://www.excelforum.com/excel-pro...x-pattern.html it has a solution with a different approach (if you have a good idea about regex) as I noticed all the data is in the element `table[id='main']` ..

[YasserKhalil]

Here's a solution using regex idea (Maybe someone is interested)

Please Login or Register  to view this content.

Here's the regex pattern demonstration
https://regex101.com/r/w66yf1/1