473,320 Members | 1,896 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Import different excel-files in one access-table

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
11 5342
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
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
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
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
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
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
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
MD
Sorry, i ment same table
so yes: in 1 table

Mar 26 '07 #9
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
MD
they have unique names, so 884 different names

Mar 26 '07 #11
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Benny | last post by:
Dear All, I am performing a data import on the SQL server. Due to fact that I use the excel file as a source. Some of cells in excel are actually empty, they become NULL fields after importing...
1
by: Michael Bourgon | last post by:
Howdy. I'm trying to build a query that will take an Excel file and pull a few rows of data from a particular sheet. I'm having a problem with my WHERE clause - I can can tell it to import WHERE...
3
by: Elaksomfan | last post by:
Hi, I am having trouble importing data from an excel spreadsheet into MS SQL Server 2000 using DTS Wizard. The DTS import process is successfull, no errors, but only 50 rows of approx. 1500 rows...
2
by: Sreedhar Vankayala | last post by:
Hi, I joined a project where we need to import data from a excel file into sql server via the asp.net. After doing a long research found that #1. Upload Excel file to the server #2. Load or...
0
by: Jedawi | last post by:
Hi, would anyone be able to point me in the right direction on how to import an image (.jpg file) into an Excel worksheet using VB.Net and Option Strict On. This should be straight forward...
2
by: Problematic coder | last post by:
Hi, I have just been asked to create an app that will import a specific column from an excel spreadsheet, something like sheet1 columnD or a column with the first row with a value of 'ID'...
0
by: MBlock316 | last post by:
Hello everyone, I am trying to import a Microsoft Excel 2007 formatted file into my application. I found on another thread in another forum that I needed to install the 2007 Office System Driver:...
1
by: cindy7 | last post by:
how can i read excel data and import into sql using cfm??? I have a CFM form for user to import excel file into database and save file in the folder at server location. I would like to read that...
7
by: Ivan | last post by:
Hello group! I need to create import utility. My main concern is how will I support multiple file types. XLS, CVS, etc? Is there any libraries I can use and distribute (part of framework?) That...
2
by: thesti | last post by:
hello, how to programmatically from VB .net / php. to import data from an EXCEL file? is there's no programmatic way, how to automated the process. as i search, the process is: Simply...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.