スキルアップ・問題解決なら、モーグにおまかせ！

こんにちは
 
ブック内のシート名一覧を抽出するために
いろいろしらべて
2019までは
名前に　=GET.WORKBOOK(1)
と入力して
シートには　=REPLACE(INDEX(シート名,ROW(A1)),1,FIND("]",シート名),"")
 
として、シートの数だけ下へコピーして一覧を求めていました
 
ですが、2021/365ではエラーになって求められません。
2021/365でシート名一覧を抽出するには、どうしたら求められますでしょうか？
よろしくお願いいたします。

VBAを使うしかないんじゃないですかねぇ・・
VBAは禁止ってことだと厄介な話になりそう。

半平太様
 
ありがとうございます！
VBAは使えないのと諸事情で禁止となっております。
数式でなんとかしたいです
2021/365で急にできなくなって困ってしまって・・

関係ないかもしれませんが、セキュリティの向上のため4.0マクロは既定では無効になるような方針になりました。
有効にするには
　ファイル
→一番左下のオプション
→トラストセンター
→トラストセンターの設定
→マクロの設定
→VBAマクロが有効な場合にExcel4.0のマクロを有効にする　にチェックを入れる
ただし、今回の問題の解決にはならないような気がします。
 
 
【代替案�@】
 
Sheet1〜Sheet5が左からこの順に並んでいるとして、
 
1.　Sheet1〜Sheet5をグループ化した状態でK1を選択して以下の式を入れる
 
=REPLACE(CELL("filename",K1),1,FIND("]",CELL("filename",K1)),)
 
　　→対象シートすべてのK1にシート名が入ります。
 
2.　どこかのシートをクリックしてグループ化を解除し、任意のセルに
 
=FILTERXML("<y><x>"&TEXTJOIN("</x><x>",TRUE,Sheet1:Sheet5!K1)&"</x></y>","//x")
 
　　これでシート名一覧が縦に並びます。
 
  
1　Sheet1
2　Sheet2
3　Sheet3
4　Sheet4
5　Sheet5

【代替案�A】
  
●どこかのセルに
=SUBSTITUTE(LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1))-1),"[",)
→上のセルを選択した状態で、数式バーの左の名前ボックスに次の名前を入れる
Fname
   
●
　データ
→データの取得
→その他のデータソースから
→空のクエリ
→詳細エディター
→「let ...　in ..」の部分を全選択して削除
→以下の文字列を貼り付け
   
let
    Fnm = Excel.CurrentWorkbook(){[Name="Fname"]}[Content]{0}[Column1],
    Src = Excel.Workbook(File.Contents(Fnm), null, true),
    Flt = Table.SelectRows(Src, each ([Kind] = "Sheet")),
    Del = Table.SelectColumns(Flt,{"Name"})
in
    Del
  
→閉じて読み込む
 
シート名が変わったら　データ→すべて更新

んなっと様、いつもありがとうございます！！
 
オプションを変更しても変わらなかったので
VBAの記述とかまったくわからないので
代替案�@でうまくできました！
 
しかし、"<y><x>"とか"</x><x>"はどういう意味になるのでしょうか？
以前、サイトを教えてもらったのですが、
さっぱりわからなくて・・
ヒントだけでも教えていただけないでしょうか？(´;ω;｀)

代替案�Aが基本だと思いますよ。もう一度コピー貼り付けして試してください。

んなっと様
 
ご返信ありがとうございます！
HTMLは難しいので、
代替案�Aを使うことにしました、うまくできました！
本当にありがとうございました/

閉じるのをわすれました