Excelを使用してフォルダからファイル名のリストを取得します（VBAありとなし）

小さなコンサルティング会社での初日、私はXNUMX日間の短期プロジェクトを割り当てられました。仕事は簡単です。

ネットワークドライブには多くのフォルダがあり、それぞれに数百のファイルがあります。

私はこれらのXNUMXつのステップに従わなければなりませんでした：

1. ファイルを選択し、その名前をコピーします。
2. 名前をExcelのセルに貼り付けて、Enterキーを押します。
3. 次のファイルに移動して、手順1と2を繰り返します。

シンプルに聞こえますよね？

シンプルで非常に時間がかかります。

正しいテクニックを知っていれば、XNUMX日間でやったことは数分でできます。

このチュートリアルでは、プロセス全体を超高速かつ超簡単にするためのさまざまな方法を紹介します（VBAの有無にかかわらず）。

このチュートリアルに示されているメソッドの制限：以下に示す手法を使用すると、ホームフォルダ内のファイルの名前のみを取得できます。メインフォルダのサブフォルダにあるファイルの名前は取得できません。PowerQueryを使用してフォルダとサブフォルダからファイル名を取得する方法は次のとおりです

FILES関数を使用して、フォルダーからファイル名のリストを取得します

それについて聞いたFILES機能？

そうしなくても心配しないでください。

これは、子供の頃のExcelスプレッドシート（​​バージョン4の数式）からのものです。

この数式はワークシートセルでは機能しませんが、名前付き範囲では機能します。このファクトを使用して、指定したフォルダーからファイル名のリストを取得します。

ここで、デスクトップに「」というファイルがあるとします。 テストフォルダ 」と入力し、そのフォルダ内のすべてのファイルのファイル名のリストを取得します。

このフォルダ内のファイル名を取得する手順は次のとおりです。

1. セルA1に、完全なフォルダーアドレスに続けてアスタリスク（*）を入力します。
   • たとえば、フォルダがCドライブにある場合、アドレスは次のようになります。
     C：\ Users \ YOUR NAME \ Desktop \ Test Folder \ *
   • フォルダアドレスの取得方法がわからない場合は、次の方法を使用してください。
     • • ファイル名を取得するフォルダーで、新しいExcelブックを作成するか、フォルダー内の既存のブックを開いて、任意のセルで次の数式を使用します。この式は、末尾にアスタリスク（*）が付いたフォルダアドレスを示します。これで、このアドレスをブック内のファイル名が必要な任意のセル（この例ではA1）にコピーして貼り付ける（値として貼り付ける）ことができます。

         = REPLACE（CELL（ "filename"）、FIND（ "["、CELL（ "filename"））、LEN（CELL（ "filename"））、 "*"）

         [上記の式を使用してフォルダアドレスを取得するためにフォルダに新しいブックを作成した場合、そのフォルダ内のファイルのリストに含まれないように削除する必要がある場合があります]

2. [式]タブに移動し、[名前の定義]オプションをクリックします。
3. [新しい名前]ダイアログで、次の詳細を使用します
   • 名前：FileNameList（お好きな名前を自由に選択してください）
   • 範囲：ワークブック
   • 参照先：= FILES（Sheet1！$ A $ 1）
4. 次に、ファイルのリストを取得するために、INDEX関数で名前付き範囲を使用します。セルA3（または名前のリストで開始するセル）に移動し、次の数式を入力します。

   = IFERROR（INDEX（FileNameList、ROW（）-2）、 ""）

5. 下にドラッグすると、フォルダ内のすべてのファイル名のリストが表示されます

特定の拡張子を持つファイルを抽出したいですか？ ？

特定の拡張子を持つすべてのファイルを取得する場合は、そのファイル拡張子でアスタリスクを変更するだけです。たとえば、Excelファイルのみが必要な場合は、*の代わりに* xls *を使用できます。

したがって、使用する必要のあるフォルダアドレスはC：UsersSumitDesktopTestフォルダー* xls *

同様に、Word文書ファイルの場合は* doc *を使用します

これはどのように作動しますか？

FILES式は、指定されたフォルダー内の指定された拡張子を持つすべてのファイルの名前を取得します。

INDEX式では、ファイル名を配列として指定し、ROW関数を使用して、XNUMX番目、XNUMX番目、XNUMX番目などのファイル名を返します。

私が使用したことに注意してくださいROW（）-2、XNUMX行目から始めます。したがって、行番号が4の場合、ROW（）-2は最初のインスタンスでは1、2番目のインスタンスではXNUMX、というようになります。

VBAを使用してフォルダからすべてのファイル名のリストを取得します

さて、上記の方法は少し複雑です（多くのステップがあります）。

ただし、これを手動で行うよりもはるかに優れています。

ただし、VBAの使用に慣れている場合（または、以下にリストする正確な手順に従うのが得意な場合）、すべてのファイルの名前を簡単に取得できるカスタム関数（UDF）を作成できます。

ユーザー定義関数（UDF）を使用する利点是あなた可將関数個人用マクロブックに保存して、これらの手順を何度も繰り返すことなく簡単に再利用できます。アドオンを作成して、この機能を他の人と共有することもできます。

まず、Excelのフォルダからすべてのファイル名のリストを取得する関数を作成するVBAコードを紹介します。

関数GetFileNames（ByVal FolderPath As String）As Variant Dim Result As Variant Dim i As Integer Dim MyFile As Object Dim MyFSO As Object Dim MyFolder As Object Dim MyFiles As Object Set MyFSO = CreateObject（ "Scripting.FileSystemObject"）Set MyFolder = MyFSO。 GetFolder（FolderPath）Set MyFiles = MyFolder.Files ReDim Result（1 To MyFiles.Count）i = 1 For Each MyFile In MyFiles Result（i）= MyFile.Name i = i + 1 Next MyFile GetFileNames = Result End Function

上記のコードは、ワークシートで使用できる関数GetFileNamesを作成します（通常の関数と同じように）。

このコードをどこに置くか？

以下の手順に従って、VBエディターでこのコードをコピーします。

この機能の使い方は？

ワークシートでこの機能を使用する手順は次のとおりです。

• 任意のセルに、ファイル名を一覧表示するフォルダーのフォルダーアドレスを入力します。
• リストが必要なセルに、次の数式を入力します（セルA3に入力しました）。

  = IFERROR（INDEX（GetFileNames（$ A $ 1）、ROW（）-2）、 ""）

• 数式をコピーして下のセルに貼り付けると、すべてのファイルのリストが表示されます。

XNUMXつのセルにフォルダの場所を入力してから、ファイル名の取得このセルは数式で使用されます。次のような式でフォルダアドレスをハードコーディングすることもできます。

= IFERROR（INDEX（GetFileNames（ "C：\ Users \ YOUR NAME \ Desktop \ Test Folder"）、ROW（）-2）、 ""）

上記の式では、ROW（）-2を使用し、XNUMX番目の行から開始します。これにより、下のセルに数式をコピーすると、数式が1ずつ増加します。列の最初の行に数式を入力すると、ROW（）を使用できます。

この式はどのように機能しますか？

GetFileNames式は、フォルダー内のすべてのファイルの名前を含む配列を返します。

INDEX関数は、セルごとにXNUMXつのファイル名を最初からリストするために使用されます。

IFERROR関数は、＃REFの代わりに空白を返すために使用されます。数式がセルにコピーされたが、リストするファイル名がなくなったときにエラーが表示されます。

VBAを使用して、特定の拡張子を持つすべてのファイル名のリストを取得します

上記の式は、Excelのフォルダからすべてのファイル名のリストを取得する場合に役立ちます。

しかし、ビデオファイル、Excelファイル、または特定のキーワードを含むファイルの名前だけを取得したい場合はどうでしょうか。

この場合、少し異なる機能を使用できます。

以下のコードを使用すると、特定のキーワード（または特定の拡張子）を含むすべてのファイル名を取得できます。

関数GetFileNamesbyExt（ByVal FolderPath As String、FileExt As String）As Variant Dim Result As Variant Dim i As Integer Dim MyFile As Object Dim MyFSO As Object Dim MyFolder As Object Dim MyFiles As Object Set MyFSO = CreateObject（ "Scripting.FileSystemObject"）Set MyFolder = MyFSO.GetFolder（FolderPath）Set MyFiles = MyFolder.Files ReDim Result（1 To MyFiles.Count）i = 1 For Each MyFile In MyFiles If InStr（1、MyFile.Name、FileExt）<> 0 Then Result（i） = MyFile.Name i = i + 1 End If Next MyFile ReDim Preserve Result（1 To i-1）GetFileNamesbyExt = Result End Function

上記のコードは、ワークシートで使用できる関数を作成します。」 GetFileNamesbyExt "（通常の関数と同じように）。

この関数は、フォルダーの場所と拡張キーワードのXNUMXつのパラメーターを取ります。指定された拡張子に一致するファイル名の配列を返します。拡張子やキーワードが指定されていない場合は、指定されたフォルダ内のすべてのファイル名が返されます。

構文：= GetFileNamesbyExt（ "Folder Location"、 "Extension"）

このコードをどこに置くか？

以下の手順に従って、VBエディターでこのコードをコピーします。

• [開発者]タブに移動します。
• VisualBasicボタンをクリックします。これにより、VBエディターが開きます。
• VBエディターで、作業中のブック内の任意のオブジェクトを右クリックし、[挿入]に移動して[モジュール]をクリックします。プロジェクトエクスプローラーが表示されない場合は、キーボードショートカットのControl + Rを使用します（Ctrlキーを押しながら[R]キーを押します）。
• モジュールオブジェクトをダブルクリックし、上記のコードをコピーしてモジュールコードウィンドウに貼り付けます。

この機能の使い方は？

ワークシートでこの機能を使用する手順は次のとおりです。

• 任意のセルに、ファイル名を一覧表示するフォルダーのフォルダーアドレスを入力します。これをセルA1に入力しました。
• セルに、すべてのファイル名にする拡張子（またはキーワード）を入力します。これをセルB1に入力しました。
• リストが必要なセルに、次の数式を入力します（セルA3に入力しました）。

  = IFERROR（INDEX（GetFileNamesbyExt（$ A $ 1、$ B $ 1）、ROW（）-2）、 ""）

• 数式をコピーして下のセルに貼り付けると、すべてのファイルのリストが表示されます。

あなたはどうですか生活を簡素化するために使用するExcelのトリック。私はあなたから学びたいです。コメントセクションで共有してください！