Excel for mac 2011 VBAで，Dir関数っぽい動作をさせるプログラム例

mac

MacのVBAでは，Dir関数が動きません，どうすれば？という質問はネット上に色々と上がっているのですが，こうすると上手く行きました！という具体的な報告をしているページにはなかなか巡りあえず...。

また，MacScript関数を使えば良いらしいということが分かっても，これまでAppleScriptに触れたことがなかったため，具体的なイメージがつかめず，手こずった経験から，このエントリを作りました。

環境

・Microsoft Excel 2011 for Mac
・Snow Leopard

やりたかった事

  1. 指定したフォルダ内にあるExcelファイルの数を調べる。
  2. その値をプロシージャー内で使う。*1

解法

MacScript関数（VBAからAppleScriptを呼ぶ関数）を使う。*2

コード

仮に，指定するフォルダのパス（Path）を，「Macintosh HD:Users:（ユーザー名）:Documents:DataHoge」としたとき。

'変数名は各々のお好みで
Dim folder As String
'指定したいフォルダのパス
folder = "Macintosh HD:Users:（ユーザー名）:Documents:DataHoge"

'AppleScriptを記述するための変数。名前は何でもOK。
'この変数に，AppleScriptを使って行わせたい動作を書きこんでいく。
Dim scriptToRun As String

'以下、少しでも見やすさを確保するため、1行ずつ分けて記述。
'scriptToRun = scriptToRun & "文字列（AppleScript）"となっています。　
scriptToRun = ""
scriptToRun = "set aFol to " & Chr(34) & folder & Chr(34) & Chr(13)
scriptToRun = scriptToRun & "tell application " & Chr(34) & "Finder" & Chr(34) & Chr(13)
scriptToRun = scriptToRun & "tell folder aFol" & Chr(13)
scriptToRun = scriptToRun & "set indList to a reference to (every file whose name extension is " & Chr(34) & "xlsx" & Chr(34) & ") " & Chr(13)
scriptToRun = scriptToRun & "end tell " & Chr(13)
scriptToRun = scriptToRun & "set numList to name of indList " & Chr(13)
scriptToRun = scriptToRun & "set num to length of numList " & Chr(13)
scriptToRun = scriptToRun & "end tell " & Chr(13)

'欲しかった、Excelファイルの数を入れるための変数。
'もちろん、もっと前の段階で定義しておいてもOK。
Dim i as integer

'MacScript関数を使って，変数「scriptToRun」に記述したAppleScriptを実行する。
i = MacScript(scriptToRun)

「scriptToRun」の簡単な説明 *3

まず全てに共通なのは，

  • Chr（34）は「"」（ダブルクオテーション，ダブルコーテーション）を表している
  • Chr（13）は「改行」を表している
  • 見やすくするために，scriptToRun = scriptToRun & "~~~" という書き方になっている

ということです。


続いて，各行のコードについてです。

  1. 取りあえず，変数を初期化。
  2. 「aFol」はAppleScript内でのフォルダ名を入れる変数。*4「aFol」に，初めに宣言した指定したいフォルダ名「folder」がセットされる。
  3. Finderに指令
  4. Finderで，フォルダを指定。2行目で指定したフォルダを対象にして作業してね，というイメージか。
  5. ここで，「indList」という変数に，拡張子が「xlsx」のファイル名などの情報が全て入る。
  6. 動作終わりの呪文
  7. 「numList」に「indList」で入った.xlsxファイルのファイル名が入る。
  8. 「num」に「numList」の要素数が入る。つまり，ここでやっと，指定したフォルダにxlsxファイルがいくつあるか，という情報が得られた。
  9. 呪文ふたたび

といった具合で，力技のような感じもしますが，目的を達成できました。

あとがき

Windowsでは，Dir関数を使って，指定したフォルダ内に目当てのファイルがあるかどうか，ワイルドカードなども使いつつ，簡単に判定できるのですが，MacではDir関数を使えません...。

「Mac VBA Dir関数」というワードで検索をかけると，Dir関数っぽいことをさせたいのですが？という質問が日本語でも英語でも見受けられますが，なかなか，実際にうまく動作したコードが書かれているサイトがなかったので，参考例として，エントリを作りました。

VBAは1年くらい，AppleScriptは急場しのぎの3時間程度の経験しかないので，迂遠なことをやっているかもしれませんが，どなたかのお役に立てれば，幸いです。

*1:ここでは，例としてフォルダを決め打ちで書いていますが，実際は，フォルダ名にも変数が入っており，コロコロ変わります。そして，フォルダ内のExcelファイル数も一定ではない，という状況でした。

*2:ネット上の質問アンサーによく載っているMacIDも試してみたけれど，上手くいかなかったので

*3:AppleScriptの雰囲気は，このサイト（AppleScriptの構造を上手に調べる）でお勉強しました。また肝となるAppleScriptのコードは，このサイト（指定フォルダ内の指定種別（kind）のファイル一覧を取得）を参考にさせていただきました。

*4:名前は何でもOK（なはず）。