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

re: Import different excel-files in one access-table

What is the final objective?

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

re: Import different excel-files in one access-table

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:

re: Import different excel-files in one access-table

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:

re: Import different excel-files in one access-table

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

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

re: Import different excel-files in one access-table

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:

re: Import different excel-files in one access-table

Indeed, all in the same file...

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

re: Import different excel-files in one access-table

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

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

re: Import different excel-files in one access-table

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

re: Import different excel-files in one access-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?

re: Import different excel-files in one access-table

they have unique names, so 884 different names

re: Import different excel-files in one access-table

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...