By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,989 Members | 1,279 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,989 IT Pros & Developers. It's quick & easy.

Import different excel-files in one access-table

P: n/a
MD
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

Mar 26 '07 #1
Share this Question
Share on Google+
11 Replies


P: n/a
What is the final objective?

On Mar 26, 3:22 pm, "MD" <deboism...@hotmail.comwrote:
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

Mar 26 '07 #2

P: n/a
MD
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:
What is the final objective?

On Mar 26, 3:22 pm, "MD" <deboism...@hotmail.comwrote:
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- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

Mar 26 '07 #3

P: n/a
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:
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:
What is the final objective?

On Mar 26, 3:22 pm, "MD" <deboism...@hotmail.comwrote:
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- Tekst uit oorspronkelijk bericht niet weergeven -
- Tekst uit oorspronkelijk bericht weergeven -
Mar 26 '07 #4

P: n/a
You want all 884 excel workbook in one table in access?

On Mar 26, 3:37 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:
What is the final objective?
On Mar 26, 3:22 pm, "MD" <deboism...@hotmail.comwrote:
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- Tekst uit oorspronkelijk bericht niet weergeven -
- Tekst uit oorspronkelijk bericht weergeven -- Hide quoted text -

- Show quoted text -

Mar 26 '07 #5

P: n/a
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:
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:
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:
What is the final objective?
On Mar 26, 3:22 pm, "MD" <deboism...@hotmail.comwrote:
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- Tekst uit oorspronkelijk bericht niet weergeven -
- Tekst uit oorspronkelijk bericht weergeven -- Hide quoted text -

- Show quoted text -

Mar 26 '07 #6

P: n/a
MD
Indeed, all in the same file...

On 26 mrt, 21:59, "DavidB" <j...@yahoo.comwrote:
You want all 884 excel workbook in one table in access?
Mar 26 '07 #7

P: n/a
I didnt ask same file I asked same TABLE... :)

On Mar 26, 4:01 pm, "MD" <deboism...@hotmail.comwrote:
Indeed, all in the same file...

On 26 mrt, 21:59, "DavidB" <j...@yahoo.comwrote:
You want all 884 excel workbook in one table in access?- Hide quoted text -

- Show quoted text -

Mar 26 '07 #8

P: n/a
MD
Sorry, i ment same table
so yes: in 1 table

Mar 26 '07 #9

P: n/a
On Mar 26, 4:22 pm, "MD" <deboism...@hotmail.comwrote:
Sorry, i ment same table
so yes: in 1 table
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?
Mar 26 '07 #10

P: n/a
MD
they have unique names, so 884 different names

Mar 26 '07 #11

P: n/a
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

Mar 28 '07 #12

This discussion thread is closed

Replies have been disabled for this discussion.