Well, this is not EXACTLY what you are asking for ... it does a bit more
actually.
I had written this to do much the same thing you are doing, but I wanted a
visual reference to confirm that the imports had been done ... along with
quick access to the actual XL file (via a hyperlink field in a subform) if I
wanted to check. Call me paranoid. :)
So what I did was:
1.) Create a table called "tblFileNames"
2.) An unbound form called "frmImportXL Files"
3.) A BOUND (to tblFileNames) subform called "sbfFilesImported"
I'd start by putting all 884 of those XLS files in thier own directory,
seperate from everything else.
================================================== ===================
Private Sub cmdImportMergeXL_Click()
Dim MyDB As DAO.Database
Set MyDB = CurrentDb
Dim rstFiles As DAO.Recordset
Set rstFiles = MyDB.OpenRecordset("tblFileNames")
Dim MyDir As String
Dim MyFile As String
Dim MyPath As String
Dim FileSpec As String
Dim Msg As String
Dim intFC As Integer 'File Counter
intFC = 0
'Start by browsing for the drive/directory containing the XL files
'Requires Terry Kreft's "BrowseFolder" API from
http://www.mvps.org/access/api/api0002.htm
'-----------------------------------------------------------------
MyDir = BrowseFolder("Find the directory containing the desired files")
FileSpec = MyDir & "\*.xls"
MyPath = MyDir & "\" & Dir(FileSpec)
MyFile = Dir(FileSpec)
'This section loops thru the files in the directory one at a time
'and adds the imported filenames to the table
'-----------------------------------------------------------------
Do While Len(MyFile) 0
With rstFiles
.AddNew
!FilePath = "#file://" & MyPath & "#"
'This converts the filepath string to a hyperlink,
'which allows the user to open the XL file from a subform link
.Update
End With
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97,
"tblXLSimport", MyFile, True
MyFile = Dir 'Import the next XL file in the directory.
If Len(MyFile) 0 Then
MyPath = MyDir & "\" & MyFile
End If
intFC = intFC + 1
Loop
Set rstFiles = Nothing
Set MyDB = Nothing
Msg = ""
Msg = Msg & intFC
Msg = Msg & " XL filenames have been imported."
MsgBox Msg
Me.Refresh 'Refreshes the subform that now contains the imported filenames.
End Sub
================================================== ========================
HTH,
Don
"MD" <de********@hotmail.comwrote in message
news:11**********************@l77g2000hsb.googlegr oups.com...
they have unique names, so 884 different names
