2020/04/11

あらゆるデータファイルをシートに貼り付ける




１．背景と概要

以前「画像を直接シートに貼り付ける」で、画像をシートに貼り付ける方法について紹介しました。
しかし同時に問題点も明らかになりました。
　①画像であるＰＤＦは貼り付けられない。
　②動画も貼り付けられない。
　③回転させた画像は画像比が崩れる。
この内の①と②の解決案として、ＰＤＦでも動画でもOLEオブジェクトとしてシートに貼る方法を今回紹介します。
もちろん万能ではなく、JPGなど多くのファイル種類では画像では無く「アイコン」の形で貼り付けられます。しかし、クリックする事で専用のアプリ上で閲覧できます。

今回作成したのは図１－１の様なものです。

図１－１

このマクロの取扱説明書を書くとすると、以下のようになります。
＜操作方法＞
１）ダイアログを事前に起動しておく。
２）ワークシート上で「貼り付ける位置とサイズ」を範囲選択する。
３）エクスプローラ等からファイルをダイアログに「ドラッグ＆ドロップ」する。
４）選択セル範囲にファイルが貼り付く。

＜対応しているファイル種類＞
フォルダを除き、ファイルであれば何でも貼れそうです。また、ショートカットも本体が貼られるようです。
尚、貼られたアイコンをダブルクリックにより、対応したアプリ上で開きます。

また、アイコンでは無く「内容が見える形」で貼れるファイル種類もありますが、これは各ＰＣの環境（インストールしているアプリの種類等）によるものと思われます。しかし、アプリが入っているはずのJPGでもアイコンになってしまいますので、「基本はアイコン」と考えた方がよさそうです。
（ちなみに私のＰＣでは、MS-Office(Excel,Wordなど)・CSV・BMP・PDFのファイルが内容確認できる形で貼り付けられました）

２．プログラム

今回のマクロは、全てフォームモジュールに記載しています。標準マクロにも１行記載しますが、これはフォームを起動させるだけのコードで、Excelにアドイン登録時に使用します。

また、ドロップするためのフォームのコントロールには、ドロップファイルの情報を取得できる「ListViewコントロール」「WebBrowserコントロール」などがありますが、今回はどのExcelバージョンでも使用できる（はずの）WebBrowserコントロールを使用します。
ListViewコントロールは複数のファイル情報を受け取れるので利用価値が高いと思いますが、使えないバージョンが有りそうなので今回は割愛します。ListViewに改造したい方は「画像を直接シートに貼り付ける」を参照下さい。

２－１．フォームの作成

２－１－１．WebBrowserコントロールを使うための準備

WebBrowserコントロールは、本来 IE や Edge のように「サイトをExcelフォーム上に表示させる」ものです。しかし、その機能の一つにドロップしてきたファイル名を取得できるものがありますので、今回はそれを活用します。
しかし、WebBrowserコントロールは標準では表示されていませんので、まず図２－１の方法でツールボックスに追加をします。

１）ツールボックス上でマウスを右クリックし、「その他のコントロール」を選択します。
２）リストの中から「Microsoft Web Browser」を選択（先頭にレ点を付ける）しＯＫボタンを押します。
３）ツールボックスにWebBrowserマーク「」が追加されます。


図２－１

２－１－２．WebBrowserコントロールの配置

ツールボックスにWebBrowserが追加できましたら、クリックしてフォーム内に図２－２の様にWebBrowserコントロールを配置します。


図２－２

WebBrowserの範囲は真っ黒となりますが、実行時は白背景になります。

２－２．フォームのInitializeイベントプロシージャ

フォームを起動する時にフォームの初期化をするのが図２－３のInitializeイベントプロシージャです。
特に設定は必要無く、ここではタイトルを表示するくらいです。
  1. '==========　⇩①　フォームの初期設定(WebBrowser用)　=======================
  2. Private Sub UserForm_Initialize()
  3. 　Me.Caption = "OLEトンネル"　　　　　　 ’←フォームのタイトル表示
  4. End Sub
図２－３

ドロップ範囲の外枠線が無くボケて見えてしまうので、本当は「WebBrowserに外枠線の設定」をしたかったのですが、プロパティが見当たらずそのまま使用しています。

２－３．フォームのイベントプロシージャ

画像ファイルをドロップした時に発生するイベントは、WebBrowserでは「BeforeNavigate2」イベントになります。
その引数の内の「URL As Variant」で、ドロップしたファイルの「パス名＋ファイル名」が受け取れます。
  1. '==========　⇩②　ファイルをドロップした時のイベントプロシージャ（WebBrowser）　===========
  2. Private Sub WebBrowser1_BeforeNavigate2(ByVal pDisp As Object, URL As Variant, Flags As Variant, TargetFrameName As Variant, PostData As Variant, Headers As Variant, Cancel As Boolean)
  3. 　Dim sh_OLE As OLEObject　　　　 ’←貼り付けるOLEのオブジェクトの宣言
  4. 　Cancel = True　　　　　　　　　 ’←WebBrowser内に表示されるのを防ぐ
  5. 　Application.ScreenUpdating = False
  7. 　On Error Resume Next
  8. 　　Set sh_OLE = ActiveSheet.OLEObjects.Add _
  9. 　　　　(Filename:=URL, Left:=Selection.Left, Top:=Selection.Top) 　 ’←OLEｵﾌﾞｼﾞｪｸﾄとして貼り付ける
  11. 　　If Not Err = 0 Then
  12. 　　　MsgBox "オブジェクトではありません"
  13. 　　　Exit Sub
  14. 　　End If
  15. 　On Error GoTo 0
  17. 　With sh_OLE　　　　　　　 '↓選択範囲とOLEｵﾌﾞｼﾞｪｸﾄの形を比較
  18. 　　If (Selection.Width / Selection.Height) / (.Width / .Height) >= 1 Then
  19. 　　　.Width = Selection.Height * (.Width / .Height)　　 ’←サイズ調整
  20. 　　　.Height = Selection.Height
  21. 　　Else
  22. 　　　.Height = Selection.Width * (.Height / .Width)　　 ’←サイズ調整
  23. 　　　.Width = Selection.Width
  24. 　　End If
  25. 　End With
  27. 　Application.ScreenUpdating = True
  28. End Sub
図２－４

このWebBrowserコントロールに「一度に複数ファイルをドロップ」することは可能ですが、６行目のWebBrowserイベントでは１つ目のファイル名しか受け取れません。しかも「ドロップしたファイルは何個か」の情報も受け取る術がありませんので、エラーのコメントを出すことも出来ません。
ですので、ユーザには「１つずつドロップして」と、使い方を周知させる必要があります。

７行目の「sh_OLE」変数は、貼り付けたオブジェクトを１２行目で代入し、その貼り付けたオブジェクトのサイズを変更（２１～２９行目）する為に使用します。

８行目の「Cancel = True」は、「サイトの移動操作を取り消す」という意味です。WebBrowserはサイト等を表示するために存在しますので「ドロップしてきたファイル先に移動（＝表示）」しようとします。しかしコントロール内に表示されても困るので、表示をCancelしています。

９行目の「Application.ScreenUpdating = False」は３１行目で解除するまでの間、画面更新を停止させるものです。今回のマクロでは、一旦オブジェクトをシートに貼り付けた後でサイズを変更していますので、どうしても画面がちらつきます。そのちらつき（≒処理速度低下）を抑えるものです。

１２～１３行目は、実際にファイルをOLEオブジェクトとして貼り付けているコードです。
貼り付けるファイル名は、プロシージャ引数のURLで取得できますので「ActiveSheet.OLEObjects.Add」の引数Filenameに与えます。また、貼付け位置を決める「Left」と「Top」には選択範囲のLeftとTopを与えます。

「OLEObjects.Add」の引数は全部で１１種あり、その中には「Width」「Height」があります。
そこで、図２－５のように「選択範囲に収まるように貼り付ける」指示をしてみます。
  1. 　Set sh_OLE = ActiveSheet.OLEObjects.Add _
  2. 　　　　　(Filename:=URL, Left:=Selection.Left, Top:=Selection.Top _
  3. 　　　　　, Width:=Selection.Width , Height:=Selection.Heigth)
図２－５

するとWidthとHeightは、全く無視をされます。（＝設定しないのと同じ状態）

調べてみると、OLEオブジェクトとは「ActiveXコントロール」または「ワークシートにリンクあるいは埋め込まれているOLEオブジェクト」との説明（Microsoftより）があります。
Addメソッド（オブジェクトを追加）を使用する際、「ActiveXコントロール」では引数ClassTypeを、「リンク・埋め込みオブジェクト」ではFilenameを使うとの事のようです。

そして、Filenameにファイル名を渡す場合、貼り付ける前の段階ではWidthとHeightは未知のために無視する、ということではと推定されます。
そこで図２－６のようなコードを作りPDFファイルをドロップしてみました。動作順に説明すると、Width設定で幅が変更になると共に縦横比を保ったまま高さも変更になります。次にHeight設定で高さが変更になり、同時に縦横比を保ったまｍ幅も再度変更になるのです。結果的には、最後のHeight設定だけが有効になる形です。
尚、JPGファイルで試してみると選択範囲にフィットする画像に変形できます。
  • 　Set sh_OLE = ActiveSheet.OLEObjects.Add _
  • 　　　　　(Filename:=URL, Left:=Selection.Left, Top:=Selection.Top)
  • 　With sh_OLE
  • 　　.Width:=Selection.Width
  • 　　.Height:=Selection.heigth
  • 　End Widh
図２－６

これだけでは断定できませんが「ActveXコントロール以外は、貼り付けてみるまではファイルの縦横比が変更可能か否かが不明なため、貼り付ける段階ではWidthとHeightは無視する」ということなのかもしれません。

ですので今回は、貼付け時には貼付けサイズを指定せず、あと（２１～２９行目）でWidthとHeightを変更する方法としました。また、縦横比を変更できないオブジェクト（PDFなど）の存在も分かりましたので、指定範囲にピッタリ変形させる（セルフィット）ことは今回はせず、もとの縦横比を維持し指定範囲内に収める形としました。

その貼付け部（１２～１３行目）を、１１行目の「On Error Resume Next」と１９行目の「On Error GoTo 0」で囲っています。
これは「OLEObjects.Add」でエラーが発生した時のエラー処理です。
１５行目でエラー有無を調べ、もしエラーが発生した時には１６行目でコメントを出し、１７行目でマクロを終了させています。

どの様なエラーが考えられるかというと、例えばイベントプロシージャの引数「URL」の指すものが「オブジェクトではない」ために、「OLEObjectsとして」「貼付け（.Add）」られない場合です。
一例を挙げると、フォルダーそのものをドロップした様な時です。

２１～２９行目では、貼り付けたオブジェクトの縦横サイズを変更させています。
今回は、元のオブジェクトの縦横比を維持したままで選択範囲に入るようにサイズを変更しますので、「元オブジェクトの縦横比」と「選択範囲の縦横比」を比較します。
そして図２－７のように、選択範囲の方が横長であれば「Selection.Height」に合わせ、選択範囲の方が縦長であれば「Selection.Width」に合わせてオブジェクトの縦横を決めます。


図２－７

２３～２４行目は「選択範囲の方が横長」の場合の処理で、オブジェクトの高さ（.Height）に選択範囲の高さ(Selection.Height)を与え、オブジェクトの幅（.width）にはオブジェクトの縦横比を維持させる様な比率を指示します。
２６～２７行目は「選択範囲の方が縦長」の場合の処理で、逆にオブジェクトの幅（.width）に選択範囲の幅(Selection.Width)を与えます。

但し注意が必要なのは、「選択範囲の方が横長」の場合で言えば、先にオブジェクトの高さ（.Height）に選択範囲の高さ(Selection.Height)を与えてしまうと、オブジェクトの幅（.width）に与える値が変わってしまうため、図２－７の右側の式のように①→②の順番に実行する必要があります。

２－４．標準モジュール

最後に、フォーム起動用のプロシージャを作成します。
  1. '==========　⇩③　フォーム起動用プロシージャ　===========
  2. Public Sub OLE_Paste()
  3. 　UserForm1.Show 0　　　　 ’←モードレスでUserForm1を表示する
  4. End Sub
図２－８

３４行目は、アドインで起動できるようにPublicプロシージャにします。
また、ダイアログを表示させたまま選択範囲を変更できるように、フォームはモードレスで起動させます。
図２－８を実行すると、図１－１の様にダイアログが立ち上がります。

３．Excelのアドインファイルにする

アドインへの登録方法については、下記を参照願います。ファイル名が異なるだけで手順は全く同じです。
・「セルの罫線を矢印キーで引く」
・「西暦・和暦対照表」


４．最後に

「画像を直接シートに貼り付ける」ではPDFや動画が対応しない事から、その対策として今回のプログラムに着手しました。
結果としてファイルであれば何でも貼れるというのは、結構便利なのかもしれないと思います。しかし一方で、実行ファイル（EXEなど）も貼れてしまうのはセキュリティ上問題があるかもしれない、と心配しています。

Excelファイルにオブジェクト貼付けしたファイルがセキュリティソフトでの検査対象となっているのかも含め、今後なにか分かったら紹介していきたいと思います。とりあえずは安全が担保されている範囲での使用に留めておいた方が良いと思います。


あらゆるデータファイルをシートに貼り付ける（it-024.xlsm）

セキュリティ向上を目的として「インターネット経由でダウンロードしたOfficeファイル（Excel等）のマクロは、既定でブロック」されるようにOfficeアプリケーションの既定動作が変更になりました。（2022年4月より切替開始）
解除の方法については「ダウンロードファイルのブロック解除方法」を参照下さい。