Sub myFiles()
    '指定フォルダ内のファイル名一覧を配列に格納する方法
    '2005/04/29 pPoy
    Dim strFile() As String     '動的配列宣言
    Dim strPath As String       'フォルダのフルパス
    Dim strType As String       'ファイルタイプ
    Dim tmpName As String       'ファイル名
    Dim i As Long               'カウンタ
    
    strPath = "C:\Test\"
    strType = "*.xls"           'Excel形式を指定
    
    '1件取得
    tmpName = Dir(strPath & strType)
    i = 0
    
    If tmpName = "" Then
        MsgBox "ファイルが見つかりませんでした〜"
    End If
    
    'Loop 開始
    Do While tmpName <> ""
        '配列の上限再設定
        ReDim Preserve strFile(i)
        strFile(i) = tmpName
        i = i + 1
        MsgBox i & "件目, " & tmpName       '※
        
        ' 次のファイル名取得
        tmpName = Dir
    Loop
End Sub

• 「※」 の部分のメッセージは、検証用に表示してあります。
  不要な場合は削除、若しくはコメントアウトしてください。
  
  
• このマクロは、Option Base 0 を前提としています。 (規定は 0 です)
  従って配列の添え字の下限は、0 から始まります。
  つまり最初に見つかったファイル名は、strFile(0) に格納されます。
  
  
• このマクロは拡張子に、「xls」 を持つ Excel ファイルを、指定されたフォルダ内から検索し、
  ファイルが見つかった場合のみ配列に追加します。
  
  
• ReDim にキーワード Preserve を使用して、既存の配列内の値を保持したまま､
  配列の最後の次元の要素数を変更しています。
  このキーワードを使用せずに配列の次元を変更した場合は、既存の値はクリアされます。
  
  
• 検索条件の、ファイル名の一部と拡張子を変更することにより、各種ファイルに対応できます。
  
  
• このマクロは Excel 用として書いてありますが、Access でもそのまま使用できます。