1

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

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/

質問の編集

回答とコメントは、会員登録(無料)で閲覧できるようになります。
会員登録(無料)する