I have used this technique quite often for over 15 years. Lately there were some problems due me having 64 bit Windows and 32 bit MS Office, but there is a workaround.

Now I tried the same with Excel 2016 for 1st time. There are new ways to create a query added, but I did find at last the familiar "From MS Query" option too. But whatever way I did choose, no tables were displayed - not for "From MS Query" option, neither for other options.

At last I opened the same file with Excel 2013 - and there were displayed all sheets in workbook, and the fixed named range I created before too (I prefer fixed named ranges as query source table). So the table itself was OK!

Is there some setting to set, or what?