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

Automated import Excel --> Access

P: n/a
I have 400 different Excel-spreadsheetfiles, same structure, all with only
one record in it, and all residing in the same folder. Every now and then
new Excel files are being added. In my Access file I have a form and via a
button on that form I would like to periodicly automatically import all of
those (new) Excelfiles in an Access table with an identical structure.
What's the easiest way of making such an automated import? Is this doable?
Thanks,
john
Jul 20 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a

john wrote:
I have 400 different Excel-spreadsheetfiles, same structure, all with only
one record in it, and all residing in the same folder. Every now and then
new Excel files are being added. In my Access file I have a form and via a
button on that form I would like to periodicly automatically import all of
those (new) Excelfiles in an Access table with an identical structure.
What's the easiest way of making such an automated import? Is this doable?
Thanks,
john
Definitely doable.

First thing to do is to create an import specification where you map
the fields in the spreadsheet to types and fields in your table. (I do
it because then Access won't make some stupid proxy decision for me and
screw up.) then you could use something like the BrowseFolder API to
select a folder (at www.mvps.org/access) and then loop through the
Excel files in the folder, importing them through code.

Something like this should work

Public Sub ImportFilesInDirectory()
Dim strPath As String
Dim myFile As String

strPath = BrowseFolder("Select a folder to process")

myFile = Dir(strPath & "\*.txt")
Do While myFile <""
DoCmd.TransferText acImportDelim, "MySpecificationName",
"DestinationTable", myFile, False
myFile = Dir
Loop
End Sub

Jul 20 '06 #2

P: n/a
Thanks Piet. Right now this seems over my head. I have some programming
experience but not in VB. I do want to try to make it work though, so a few
questions:

1. How do i save an importspecification? and where are they stored? If I
import via the wizard there is no option to store the importspecification.

2. In a button I put the following code but the debugger says it can't
compile from the DoCmd part on, because of a syntax error.

Option Compare Database

Public Sub ImportFilesInDirectory()
Dim strPath As String
Dim myFile As String

strPath = "C:\temp"

myFile = Dir(strPath & "\*.txt")
Do While myFile <""
DoCmd.TransferText acImportDelim, "MySpecificationName",
"DestinationTable", myFile, False
myFile = Dir
Loop
End Sub

The BrowseFolder gives a compile error (sub of funtion is not defined).
I probably put the code in the wrong place...

Btw: I'm using Access 2002.
john

<pi********@hotmail.comschreef in bericht
news:11**********************@m79g2000cwm.googlegr oups.com...
>
john wrote:
>I have 400 different Excel-spreadsheetfiles, same structure, all with
only
one record in it, and all residing in the same folder. Every now and then
new Excel files are being added. In my Access file I have a form and via
a
button on that form I would like to periodicly automatically import all
of
those (new) Excelfiles in an Access table with an identical structure.
What's the easiest way of making such an automated import? Is this
doable?
Thanks,
john

Definitely doable.

First thing to do is to create an import specification where you map
the fields in the spreadsheet to types and fields in your table. (I do
it because then Access won't make some stupid proxy decision for me and
screw up.) then you could use something like the BrowseFolder API to
select a folder (at www.mvps.org/access) and then loop through the
Excel files in the folder, importing them through code.

Something like this should work

Public Sub ImportFilesInDirectory()
Dim strPath As String
Dim myFile As String

strPath = BrowseFolder("Select a folder to process")

myFile = Dir(strPath & "\*.txt")
Do While myFile <""
DoCmd.TransferText acImportDelim, "MySpecificationName",
"DestinationTable", myFile, False
myFile = Dir
Loop
End Sub

Jul 20 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.