8

これから転記のシナリオを作るかたや、転記目的で、コピペ、変数を経由した値の取得/設定、繰り返しを使ってるシナリオを運用中のかた向けです。
Excel操作(範囲転記)を作りました。
お裾分けです。

このへんのトピックに、軒並み適用できる想定です。

https://winactor.com/questions/?q=Excel|CSV+転記

長いです。ミニマムで★だけで試せます。
ライブラリ加工の練習にも適したレベル感です。
もし値だけの転記で充分ならお試しください。

  • 書式とかは大抵は運用でカバーできるはず（予め指定しておくとか、条件付き書式とか）

　

転記作業に対する思い

「世の中から転記作業なんて無くなれば良いのに...」と日々思ってます。

  • 元データが使い易ければ良いだけの話（元データをそのまま使えるのが理想）
  • システムとか、データの作り手が、初めからデータを使うユーザーの要望も聴く
    • ユーザー部門がシステムの計画段階から一枚噛む
    • 働き方改革の時代、これが当たり前になってくるはず
    • 転記しなきゃいけない時代はいずれ終焉
    • もうしばらくは辛抱が必要なのかも
  • システムの入出力（入力/取得/抽出の類）は、CSVインポート/エクスポートとかAPIとかに対応していないと、RPAによる転記も検討せざるを得ない
  • 非システム、特にExcel周りの転記は、ラストワンマイルとしてRPAが担いがち
    • セル/行/列/のコピペ、変数を経由した値の取得/設定、繰り返しは、RPAらしい反面、効率が気になる
    • せめて手作業より効率的にしたい
    • Excel操作(範囲転記)はここにフォーカス

　

考えかた

  • 値の一括代入が速い（きっと最速かと）
    • 最下部のリファレンスサイト参照
    • 転記のトピックで同じ手法を紹介済み（既成ライブラリの組み合わせで）

　

★ベースライブラリ

  • Excel操作(書式を含めたコピー)内、Excel操作(範囲コピー)

　

Excel操作(範囲転記)のカバー範囲（ベースライブラリに準ずる）

  • Excel ⇔ CSV
  • Excel ⇔ Excel
  • CSV ⇔ CSV

　

★加工のポイント

  1. ファイルの処理を複製
    1. 先頭からメイン処理のこの行↓の手前までを複製
      worksheet.range(range).COPY
    2. 複製したほうのすべての変数の末尾に1を追記
      変数がオブジェクトと同じ表記みたいなワナがあるので、取り違えに注意
      range(range)
      この場合は、カッコの外側はオブジェクト、内側が変数
  2. 変数の表示名を修正
    1. 変数の表示名の案
      転記元ファイル名
      転記元シート名
      転記元開始セル
      転記元終了セル
      転記先ファイル名 ← ここから複製したほう
      転記先シート名
      転記先開始セル
      転記先終了セル
    2. コメントやエラーメッセージの扱い
      変数の表示名を踏まえて変更しておくのがベター
  3. メイン処理を値の代入に差し替え
    1. この行↓をコメントアウト（行頭にシングルクォーテーションを追加）
      'worksheet.range(range).COPY
    2. 代わりにこの行を挿入
      worksheet1.range(range1).Value = worksheet.range(range).Value
  • 不要な行をコメントアウト
    'worksheet.Activate
    'worksheet1.Activate
    　
  • お作法に準拠
    終了処理にも、末尾1の変数分を複製しといたほうが無難
    　
  • 補足
    加工箇所はまあまあ多いものの、実質3.の1行だけ
    ほぼベースライブラリのままなので、権利に配慮してスクリプト丸出しは見送り

　

気づき

  • .xlsxのほか、.xlsmでも.csvでも成功する
  • .csvから.csvへの転記にも成功する
  • 総当たりは未試行
  • いずれも元ライブラリの仕様に準ずるはず
  • .csvの場合は、拡張子なしのファイル名がシート名になる（Excel側の仕様）

　

メリデメ

  • ◎ 1パーツだけで転記が完了する
  • ◎ 一括処理（繰り返さないで済む）
  • ◎ 値用の変数が不要
  • ◎ クリップボード不使用
  • ◎ 一瞬*
  • ▲ 設定項目数が元ライブラリの倍に増える
    　 転記先を指定するから当然
    　 コピペ、変数を経由した値の取得/設定、繰り返しのアプローチ等の設定より圧倒的にラクなはず
  • ▲ 値の代入に特化しているので、形式を選択して貼り付けのオプションには非対応
    　 範囲どうしは掴めてるので、きっと少々の加工で対応できちゃうとは思われる
  • ▲ 最終行/最終列とかを加味するのが面倒
    　 ほかのライブラリと同じように、手前でうまく変数に掴んでおく
    　 1列まるっとみたいな横着でも充分速い
    　 もっと横着する方法もありそう

　

*パフォーマンス実測

  • 10文字 × A1:Z1000 ＝ 6秒（繰り返しだと、、行ごと転記で1秒/行だとしても1000秒 ≒ 17分）
  • 大抵の場面は、1秒未満 ≒ 一瞬と思われる

  • いずれも体内時計で計測
  • Excel操作(範囲転記)自体は、ファイルが開いてなければ開くところから始めてくれる（ベースライブラリの仕様）
  • ファイルの格納場所がネットワークフォルダだったり、10MB以上の巨大ファイルを開いてない状態からだと、開く分が所要時間に加わる
  • 今回は、転記元/転記先ファイル共に開いた状態で計測

　

希望

  • どこかで誰かが使って「ラクできた」みたいな声が耳に入ると嬉しい
  • ここがイマイチとか、もっとこうなら良いのにとか、フィードバックをいただけると嬉しい
  • スイートライブラリやプチライブラリ、あわよくば標準ライブラリに採用されたら嬉しい
    既に同じコンセプトのものがあるのだろうか、、
  • このへんのサンプルシナリオのアップデートに繋がればなお嬉しい

CSVファイルの指定した列を文字列としてExcelファイルへ転記するサンプルシナリオ
https://winactor.biz/samplescenario/2019/11/15_1475.html

　

更なる拡張案

  • ValueをNumberFormatLocalに置き換えたら、セルの書式設定 > 表示形式 の転記に成功した
    • 設定タブにプルダウンを2つ設けて、ValueとNumberFormatLocalをそれぞれオンオフできるようにするとか
    • 両方ともオフだと想定外になりそうなので、1つのプルダウンで、各のみと併用の計3つの選択肢でも
    • ニーズと時間見合いで
    • きっと誰かが「できたよ」と叶えてくれるはず
  • NumberFormatLocal以外にも拡張の余地があるのかも

　

リファレンス

Office TANAKA - セルの操作[セルのコピー]
http://officetanaka.net/excel/vba/cell/cell09.htm

Office TANAKA - VBA高速化テクニック[値貼り付けは遅い]
http://officetanaka.net/excel/vba/speed/s13.htm

クリップボードを使わないセルのCopy｜VBA技術解説
https://excel-ubara.com/excelvba4/EXCEL254.html

セルの書式（表示形式,NumberFormatLocal）｜VBA入門
https://excel-ubara.com/excelvba1/EXCELVBA331.html

（ここから未踏）

  • Textで表示されてるままの文字列、Value2でシリアル値、Fomulaで数式を扱えるという情報

Office TANAKA - Excel VBA Tips[セルのValue2プロパティ]
http://officetanaka.net/excel/vba/tips/tips115.htm

  • 無限に広がりそうな情報

Rangeオブジェクトのプロパティ一覧｜Excelリファレンス
https://excel-ubara.com/EXCEL/EXCEL906.html

誰かがダイブして、どこかでクールな解きかたに繋げていただければ

　

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

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

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