HERRICK BROWN & Company Ltd.
This MS Access 2000 procedure reads data from Excel worksheets in a
directory structure and inserts it in to a Table.
Sub BurgessBS()
'Prepared for Burgess Building Services by Herrick Brown & Company Ltd.
'Both companies have intellectual property rights to this software
'Other parties must respect these rights.
Dim strFFN As String
Dim mypath As String
Dim myname As String
Dim myL1path As String
Dim myL1name As String
mypath = "S:\Projects\"
myname = Dir(mypath, vbDirectory)
Do While myname <> ""
If myname <> "." And myname <> ".." Then
If (GetAttr(mypath & myname) And vbDirectory) = vbDirectory Then
Debug.Print mypath & myname
myL1path = mypath & myname & "\"
Set fs = Application.FileSearch
With fs
.NewSearch
.LookIn = myL1path & "materials & plant\orders\"
.FileName = "*.xls"
.SearchSubFolders = False
.MatchTextExactly = False
.FileType = msoFileTypeBinders
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
strFFN = .FoundFiles(i)
'
MsgBox strFFN & " strFFN"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
"NewTable", strFFN, False, "sage export sheet!A2:H2"
Next i
'
Debug.Print .FoundFiles.Count
End If
End With
End If
End If
myname = Dir
Loop
End Sub
'Prepared by : George Herrick, Herrick Brown & Company Ltd.
'PO Box 21, Eccles, Manchester. M30 7BN. 0161 925 0600.
'e-mail : mail<at>herrick-brown.co.uk where <at> = @