ファイル名を取得後に拡張子と分離する方法は?(関数 InStr、Left、Mid の使い方)
Question 48.3   Previous Next
いつも当ホームページ及び500連発を、大変参考にさせて頂いております。 早速ですが、次のことが出来ないのでお尋ねしたいと思います。
ファイル名を取得後、拡張子と分離をする方法についてですが、
  (例) FFF.xls を 、 FFF と xls (又は、.xls)
良い方法がありましたらご指導下さい。宜しくお願い致します。
Answer   Copyright (C) 2001.8.7 永井善王
詳細は こちら 「Excel VBAマクロ 500連発」を参考にしていただいているとのこと、ありがとうございます。
ご質問に良く似た内容のマクロが「500連発・第2弾」の150番に「指定文字の前後で分割して別のセルに格納する」として掲載されています。これは、私と一緒に執筆活動された井領氏の作品ですので、参考にさせていただきながら回答をまとめます。
VBAには、ある文字列の中から、指定した文字(列)を検索して、最初に見つかった文字位置を返してくれる文字列処理関数 InStr があります。質問の例ではピリオド(.)の前後で分割することになるので、この関数を利用して、先頭から何文字目にピリオドがあるのかを取得します。

InStr関数の構文
InStr([検索の開始位置, ]検索対象となる文字列式, 検索する文字列式[, 比較モード])

使用例
    ピリオド位置 = InStr(1, 取得したファイル名, ".", vbTextCompare)
これにより、質問の例にある FFF.xls が変数「取得したファイル名」に格納されている場合は、変数「ピリオド位置」には 4 が返ることになります。よって、拡張子を除いたファイル名の長さは、ピリオド位置より 1少ない 3文字となります。

InStr関数の引数 compare(比較モード)の定数
定数 説明
vbUseCompareOption -1 Option Compareステートメントの設定を使用して比較を行う
vbBinaryCompare 0 バイナリモードの比較を行
vbTextCompare 1 テキストモードの比較を行う
vbDatabaseCompare 2 Microsoft Accessの場合のみ有効、データベースに格納されている設定に基づいて比較を行う

次に、文字列の先頭(左端)から指定した文字数の文字を取り出す関数 Left を説明します。

Left関数の構文
Left(文字列式, 文字数)

使用例
    ファイル名 = Left(取得したファイル名, ピリオド位置 - 1)
これにより変数「ファイル名」には FFF が返されます。ピリオド位置- 1 の意味は理解できますね。
続いて、文字列から指定した文字数分の文字列を取り出す関数 Mid を説明します。

Mid関数の構文
Mid(文字列式, 取り出し位置, 文字数)

使用例
    拡張子 = Mid(取得したファイル名, ピリオド位置 + 1)
これにより変数「拡張子」には xls が返されます。この使用例では、構文にある3番目の引数「文字数」を省略していますので、「取り出し位置」から後のすべての文字が返されます。なお、「ピリオド位置」に +1 しなければ「拡張子」には .xls が返されます。

まとめると次のようなマクロになります。
Sub ファイル名を取得後に拡張子と分離する()
    取得したファイル名 = "FFF.xls"              'これはテスト用のコードです
    ピリオド位置 = InStr(1, 取得したファイル名, ".", vbTextCompare)
    ファイル名 = Left(取得したファイル名, ピリオド位置 - 1)
    拡張子 = Mid(取得したファイル名, ピリオド位置 + 1)
End Sub
【参考】
文字列中のアンダーバーの前後の文字列を取得するには? (InStrRev関数の使い方)

Excel VBA Macro