Importing from subfolders

[monrosal]

Does anybody know how I can import excel files from subfolders within a top level folder? What I am trying to do is: a user will choose the folder where the subfolders are, then when a user clicks "import", all the excel files within the subfolders will be imported into Access. I've tried using the DIR function, but I haven't gotten it to work. Thanks for your help

Ramon

 

[tlbroadbent]

Here is a sample module

Sub ImportFiles()

Dim DirPath As String, DirName As String, FileName As String
Dim DirArray(500) As String, i As Integer, j As Integer
Dim FileName2 As String, FolderPath As String

DirPath = "C:\data\excel\"
DirName = Dir(DirPath, vbDirectory) ' Initialize directory
Do Until DirName = "" ' Find all the directory names
If Left(Right(DirName, 4), 1) = "." Or Left(DirName, 1) = "." Then
Else
i = i + 1
DirArray(i) = DirName ' Load array
End If
DirName = Dir() ' get next directory name
Loop

For j = 1 To i
FolderPath = DirPath & DirArray(j) & "\*.xls"
FileName = Dir(FolderPath, vbDirectory)

If FileName <> &quot;&quot; Then
Do Until FileName = &quot;&quot;
FileName2 = DirPath & DirArray(j) & &quot;\&quot; & FileName
DoCmd.TransferSpreadsheet acImport, 8, &quot;TableName&quot;, FileName2, True
FileName = Dir()
Loop
End If
Next j
End Sub
Terry
------------------------------------
Blessed is the man who, having nothing to say, abstains from giving us worthy evidence of the fact. -George Eliot

 

[monrosal]

Thanks a lot for your help

Ramon