Automated import Excel --> Access

[john]

I have 400 different Excel-spreadsheetfile s, 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

[pietlinden]

john wrote:

I have 400 different Excel-spreadsheetfile s, 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 ImportFilesInDi rectory()
Dim strPath As String
Dim myFile As String

strPath = BrowseFolder("S elect a folder to process")

myFile = Dir(strPath & "\*.txt")
Do While myFile <""
DoCmd.TransferT ext acImportDelim, "MySpecificatio nName",
"DestinationTab le", myFile, False
myFile = Dir
Loop
End Sub

[john]

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 importspecifica tion? and where are they stored? If I
import via the wizard there is no option to store the importspecifica tion.

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 ImportFilesInDi rectory()
Dim strPath As String
Dim myFile As String

strPath = "C:\temp"

myFile = Dir(strPath & "\*.txt")
Do While myFile <""
DoCmd.TransferT ext acImportDelim, "MySpecificatio nName",
"DestinationTab le", 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********@hot mail.comschreef in bericht
news:11******** **************@ m79g2000cwm.goo glegroups.com.. .

>
john wrote:

>I have 400 different Excel-spreadsheetfile s, 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 ImportFilesInDi rectory()
Dim strPath As String
Dim myFile As String

strPath = BrowseFolder("S elect a folder to process")

myFile = Dir(strPath & "\*.txt")
Do While myFile <""
DoCmd.TransferT ext acImportDelim, "MySpecificatio nName",
"DestinationTab le", myFile, False
myFile = Dir
Loop
End Sub