Import different excel-files in one access-table

Hello,

I need to import a sheet of 884 different excel-file with same lay-
out.
The sheet name is 'Totaal' and is the same in all different files.

Is there a script (module) in order to:
1. create a table with all the different names of the 884 files
2. create the import queryfor the 884 different files, but also
mentioning the file name of the excel-file in a colomn

Can anybody help? I tried a little bit, but it is not really
working...

M

What is the final objective?

On Mar 26, 3:22 pm, "MD" <deboism...@hotmail.comwrote:

To analyse the data, combine it with other data and to run queries on
it in access.

On 26 mrt, 21:31, "DavidB" <j...@yahoo.comwrote:

forget excel.move your data to access.
before I was trying ti use excel for everything. But, as soon as
discovered the power of access there is no way back

MD wrote:

You want all 884 excel workbook in one table in access?

On Mar 26, 3:37 pm, "MD" <deboism...@hotmail.comwrote:

he was asking how to import the excel into access! DOH. Its kind of
stuipid to re-enter 884 workbooks worth of data thats already in excel
if it can be imported! :)

On Mar 26, 3:53 pm, "Aivars" <aiva...@apollo.lvwrote:

Indeed, all in the same file...

On 26 mrt, 21:59, "DavidB" <j...@yahoo.comwrote:

I didnt ask same file I asked same TABLE... :)

On Mar 26, 4:01 pm, "MD" <deboism...@hotmail.comwrote:

Sorry, i ment same table
so yes: in 1 table

On Mar 26, 4:22 pm, "MD" <deboism...@hotmail.comwrote:OK so we basically have a whole boat load of identically formatted
excel workbooks that you want to import into a single access table...
Are the excel files all names the same or do they have unique names?

they have unique names, so 884 different names

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" <deboismarc@hotmail.comwrote in message
news:1174941731.709968.156650@l77g2000hsb.googlegr oups.com...