Excelのマクロに外部のファイルを読み込ませる¶
ExcelにCSVファイルを読み込ませてデータ処理するVBAマクロを作ったのですが、ついでに、CSVファイルをアイコンにドロップしたら自動的にマクロが処理を始めるようにしてみました。
試した環境は下記です。
Excel 2016
Windows10 および Windows7
目次
方針¶
ExcelのショートカットファイルにCSVファイルをドロップしてもマクロにファイル名を渡す方法がわからなかったので、VBScriptからExcelファイル内のVBAマクロを指定して実行させるようにしました。
今回試したマクロは、以前作ったCSVファイルの特定の列をコピーするマクロです。これをマクロ付きExcelテンプレートに仕込んで、CSVファイルをドロップしたら自動的にテンプレートにデータを書き込んでもらうわけです。テンプレートなら上書きされることもありませんし、職場で使うのにもってこいだと思ったわけです。
マクロを仕込んだファイルはtrial.xltmで、マクロのプロシージャの名前はCsvReadTrialです。
試してみた¶
VBScriptは下記です。これをテキストファイルに書き込んで、拡張子をvbsに変更して保存します。
Dim fn
Dim xls
Dim bk
If WScript.Arguments.Count < 1 Then WScript.Quit '引数が無い場合は終了
fn = WScript.Arguments(0) ' 第１引数を変数に入れる
Set xls = WScript.CreateObject("Excel.Application") ' Excelを呼び出す
xls.Visible = True
Set bk = xls.Workbooks.Open("C:\hogehoge\trial.xltm") ' Excelのブックを開く
bk.Worksheets("Sheet1").Cells(1,1).Value = fn ' A1セルに第１引数を書き込む
xls.Run "CsvReadTrial" ' Excelのマクロを実行する
Set bk = Nothing
Set xls = Nothing
WScript.Quit
WScript.Argumentsプロパティは、スクリプトの引数のコレクションを返すプロパティです。インデックスを0にすると、第１引数が得られます。ショートカットにファイルをドロップすると、そのファイル名が第１引数として設定されるので、その情報を入手するわけです。
WScript.CreateObjectメソッドは、COMオブジェクトを作成するメソッドです。
Workbooks.Openメソッドでブックを開きます。
Application.Runメソッドは、Excelのマクロを実行するメソッドです。このメソッドは第１引数に実行するマクロを指定し、その後に続けてマクロに渡す引数を指定する、とマイクロソフトのサイトの説明には書いてあります。
ただ、私が試したところではうまくいきませんでした。
マクロの側に引数を受け取る設定をすると、外部から実行できないんですよ。私には原因がわかりませんでした。
そこで、シートのA1セルに開くべきCSVファイルのファイル名を書き込んで、実行するExcelマクロがそのセルを読みに行くようにしました。マクロがセルを読み込んで変数に格納したら、そのセルをクリアすれば目的は達成できるわけです。
CSVファイルの特定の列をコピーするマクロのファイル名の定義部分をA1セルを読み込むように修正して、CSVファイルをVBScriptファイルにドロップすると、きちんとExcelにデータが転記されて表示されます。
VBScriptファイルのショートカットを作って、そのショートカットにファイルをドロップしても、同様の動作をします。
これで、共有ドライブにVBScriptファイルとExcelテンプレートファイルを置いておいて、VBScriptのショートカットを配れば、皆で同じマクロを使えるようになるわけです。
公開日