このスレッドの進化版です。先に概要や弱点を掴んでください。

https://winactor.com/questions/question/転記用にexcel操作範囲コピーを拡張してみた/

　

Excel操作(範囲転記)の弱点を補完して、Excel操作(範囲スピル転記)を作りました。
Excelの新機能「スピル」を使います。
スピルの対応バージョン等の情報は、最下部のリファレンスサイト等で確認してください。

　

（以下、前スレッドと重複することがらは、一部省略）

　

ベースライブラリ

• Excel操作(数式の設定)

　

加工のポイント

1. ファイルの処理を複製
   1. この行から
      指定されたファイルを開く
      この行の手前までを複製
      '変数の値をセルに書き込む
   2. 複製したほうのすべての変数の末尾に1を追記
2. 変数の表示名を修正
   1. 変数の表示名の案
      転記元ファイル名
      転記元シート名
      転記元セル位置
      転記先ファイル名 ← ここから複製したほう
      転記先シート名
      転記先セル位置
   2. コメントやエラーメッセージの扱い
      変数の表示名を踏まえて変更しておくのがベター
3. メイン処理を転記元範囲の参照（スピル対応）に差し替え
   1. 変数の値をセルに書き込むの直下に4行追加
      'フォルダパスとファイル名を分割
      pos = InStrRev(absname, "\")
      PathName = Left(absname, pos)
      FileName = Mid(absname, pos + 1)
   2. この行をコメントアウト
      'cell.formula = formula
   3. 代わりにこの2行挿入
      formula2 = "='" & PathName & "[" & FileName & "]" & sheetName & "'!" & cellAddress
      cell1.formula2 = formula2
   4. 6行追加
      'スピル範囲を値で上書き
      cell1.Select
      Set WshShell = WScript.CreateObject("WScript.Shell")
      WshShell.SendKeys "^a" 'スピル非対応バージョンでは全セル選択になってしまうと思われる
      'WScript.Sleep 1000 '範囲の大きさによっては、待機が必要になるかも（未検証）
      cell1.CurrentRegion.Value = cell1.CurrentRegion.Value

• 不要な行をコメントアウト
  'formula = !設定値!
  'worksheet.Activate
  'worksheet1.Activate
  　
• お作法に準拠
  終了処理にも、末尾1の変数分を複製しといたほうが無難
  　
• 補足
  加工箇所はまあまあ多いものの、実質は3.内、formula2関連の2行だけ
  ほぼベースライブラリのままなので、権利に配慮してスクリプト丸出しは見送り

　

メリデメ

※ Excel操作(範囲転記)との差分のみ

◎ 転記先の最終行/最終列を気にする必要がない
▲ スピル範囲が空欄でないと、#SPILL!エラーになる
　 誤って上書きにならない機能っぽい
　 #SPILL!エラーの対策は見送り

　

パフォーマンス実測

• Excel操作(範囲転記)とほぼ同等と見込まれるため、実測は見送り

Excel操作(範囲転記)との差分

• スピル範囲の上書きに、スピル範囲の選択を要する（ほかにもやりかたがあるのかも）
• スピル範囲の選択に、Ctrl＋Aを採用した
• Ctrl＋Aの所要時間は、範囲の大きさに比例する模様

　

更なる拡張案

• スピル未対応バージョンユーザー向けの配慮
• 試せる環境があれば作りたい

　

リファレンス

Office TANAKA - Excel VBA Tips[フルパスをパスとファイル名に分ける]
http://officetanaka.net/excel/vba/tips/tips78.htm

動的配列数式と、こぼれた配列動作 - Office サポート
https://support.microsoft.com/ja-jp/office/動的配列数式と、こぼれた配列動作-205c6b06-03ba-4151-89a1-87a7eb36e531

【RPA】WinActor_「スピル」機能の凄さ紹介
https://note.com/works_id/n/nb8f07fac338e

Office TANAKA - Excel 2016レビュー「Excelの使い方が激変する「スピル」」
http://officetanaka.net/excel/excel2016/13.htm

スピルについて｜エクセル入門
https://excel-ubara.com/excel1/EXCEL628.html

SendKeys (WshShell オブジェクト) - WSH@Workshop
http://wsh.style-mods.net/ref_wshshell/sendkeys.htm

Sleep (WScript オブジェクト) - WSH@Workshop
http://wsh.style-mods.net/ref_wscript/sleep.htm

Office TANAKA - セルの操作[セル範囲の取得]
http://officetanaka.net/excel/vba/cell/cell10.htm

　

ユーザーフォーラムのサークル機能を軸に、アウトプットを促進する運動を展開してます。
お時間のあるときにでも覗いてみてください。

https://winactor.com/questions/circle/