+ Reply to Thread
Results 1 to 38 of 38

CSS Selector invalid use of Null tables length property

  1. #1
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,840

    CSS Selector invalid use of Null tables length property

    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.
    < ----- Please click the little star * next to add reputation if my post helps you
    Visit Forum : From Here

  2. #2
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    6,601

    Arrow Re: CSS Selector invalid use of Null tables length property


    Hi,

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

  3. #3
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,840

    Re: CSS Selector invalid use of Null tables length property

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

  4. #4
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    6,601

    Arrow Re: CSS Selector invalid use of Null tables length property


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

  5. #5
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,840

    Re: CSS Selector invalid use of Null tables length property

    So what is the best alternative to using the querySelectorAll?

  6. #6
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    6,601

    Arrow Re: CSS Selector invalid use of Null tables length property


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

  7. #7
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,840

    Re: CSS Selector invalid use of Null tables length property

    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.

  8. #8
    Forum Guru
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,877

    Re: CSS Selector invalid use of Null tables length property

    Maybe :
    Please Login or Register  to view this content.
    Attached Files Attached Files

  9. #9
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,840

    Re: CSS Selector invalid use of Null tables length property

    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.

  10. #10
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,840

    Re: CSS Selector invalid use of Null tables length property

    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.

  11. #11
    Forum Guru
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,877

    Re: CSS Selector invalid use of Null tables length property

    Hi Yasser,

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

  12. #12
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,840

    Re: CSS Selector invalid use of Null tables length property

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

  13. #13
    Forum Guru
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,877

    Re: CSS Selector invalid use of Null tables length property

    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

  14. #14
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,840

    Re: CSS Selector invalid use of Null tables length property

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

  15. #15
    Forum Guru
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,877

    Re: CSS Selector invalid use of Null tables length property

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

    Regards

  16. #16
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,840

    Re: CSS Selector invalid use of Null tables length property

    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.

  17. #17
    Forum Guru
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,877

    Re: CSS Selector invalid use of Null tables length property

    Is this a good one ?
    Please Login or Register  to view this content.
    Last edited by karedog; 04-09-2021 at 02:08 AM.

  18. #18
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,840

    Re: CSS Selector invalid use of Null tables length property

    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.

  19. #19
    Forum Guru
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,877

    Re: CSS Selector invalid use of Null tables length property

    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.

  20. #20
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,840

    Re: CSS Selector invalid use of Null tables length property

    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)

  21. #21
    Forum Guru
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,877

    Re: CSS Selector invalid use of Null tables length property

    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.

  22. #22
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,840

    Re: CSS Selector invalid use of Null tables length property

    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.

  23. #23
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,135

    Re: CSS Selector invalid use of Null tables length property

    Thread marked as solved ...
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  24. #24
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,840

    Re: CSS Selector invalid use of Null tables length property

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

  25. #25
    Forum Guru
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,877

    Re: CSS Selector invalid use of Null tables length property

    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.

  26. #26
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,840

    Re: CSS Selector invalid use of Null tables length property

    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.

  27. #27
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,840

    Re: CSS Selector invalid use of Null tables length property

    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.

  28. #28
    Forum Guru
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,877

    Re: CSS Selector invalid use of Null tables length property

    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.

  29. #29
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,840

    Re: CSS Selector invalid use of Null tables length property

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

  30. #30
    Forum Guru
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,877

    Re: CSS Selector invalid use of Null tables length property

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

    Regards

  31. #31
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,840

    Re: CSS Selector invalid use of Null tables length property

    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
    Attached Files Attached Files

  32. #32
    Forum Guru
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,877

    Re: CSS Selector invalid use of Null tables length property

    Variable a and p need to reset too :
    Please Login or Register  to view this content.
    Last edited by karedog; 04-10-2021 at 12:31 AM.

  33. #33
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,840

    Re: CSS Selector invalid use of Null tables length property

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

  34. #34
    Forum Guru
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,877

    Re: CSS Selector invalid use of Null tables length property

    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.

  35. #35
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,840

    Re: CSS Selector invalid use of Null tables length property

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

  36. #36
    Forum Guru
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,877

    Re: CSS Selector invalid use of Null tables length property

    You are welcome, glad to help.

    Regards

  37. #37
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,840

    Re: CSS Selector invalid use of Null tables length property

    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']` ..

  38. #38
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,840

    Re: CSS Selector invalid use of Null tables length property

    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
    Last edited by YasserKhalil; 04-10-2021 at 12:16 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Error 381Could not set the List property. Invalid property array index
    By desonny in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-16-2020, 08:33 AM
  2. Replies: 5
    Last Post: 06-02-2020, 04:56 PM
  3. [SOLVED] code for listbox give could not set the list property . invalid property value
    By mazan2010 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-17-2017, 10:22 AM
  4. Runtime error 381: Could not set the list property.Invalid property array index
    By rohith4prithvi in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-23-2014, 10:24 PM
  5. Run-time Error '380': Could not set the RowSource property. Invalid property value
    By Rem0ram in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-26-2013, 03:57 PM
  6. [SOLVED] User form - Can't set ControlSource property. Invalid property value.
    By Aland2929 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 07-26-2013, 05:29 AM
  7. [SOLVED] Excel run-time error '381': Could not set the List property. Invalid property array index
    By eemiller1997 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-20-2012, 12:48 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1