Import multiple files

[Jeff F via AccessMonster.com]

Thanks in advance for any time and assistance. I have 101 Excel files
located in a folder on an FTP site. All the files have a different name but
are structually the same. Also, they are the only files in that folder.
What I'd like to do is import (or link if impoirting is not practical) all
101 files at once. Is this possible? I am pretty green when it comes to VB
and modules but otherwise am fairly proficient in Access.

Thanks again for any assistance

 

[Douglas J. Steele]

Unfortunately, neither is possible: Access doesn't recognize the ftp
protocol (nor the http protocol, for that matter) for importing or linking.

Your only option is to transfer them somewhere where you can get to them,
and then import from that new location.

There are a couple of possibilities for using ftp from within Access to
transfer the files. See http://www.mvps.org/access/modules/mdl0015.htm and
http://www.mvps.org/access/modules/mdl0037.htm at "The Access Web"

 

[Jeff F via AccessMonster.com]

Ok thanks. I've moved all the files to a location on my harddrive. Any
suggestions on importing all 101 files at once? They are all in 1 folder and
there are no additional files in that folder other than the ones that need to
be imported.

Thanks again

Unfortunately, neither is possible: Access doesn't recognize the ftp
protocol (nor the http protocol, for that matter) for importing or linking.

Your only option is to transfer them somewhere where you can get to them,
and then import from that new location.

There are a couple of possibilities for using ftp from within Access to
transfer the files. See http://www.mvps.org/access/modules/mdl0015.htm and
http://www.mvps.org/access/modules/mdl0037.htm at "The Access Web"

Thanks in advance for any time and assistance. I have 101 Excel files
located in a folder on an FTP site. All the files have a different name

[quoted text clipped - 6 lines]

Thanks again for any assistance

 

[Douglas J. Steele]

You can use the Dir function to create a loop that returns each of the
files, one at a time, and import them that way.

Something along the lines of:

Dim strFile As String
Dim strFolder As String

strFolder = "C:\Work In Progress\" ' The ending slash is important!
strFile = Dir$(strFolder & "*.xls")
Do While Len(strFile) > 0
DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel97, _
"MyTable", _
strFolder & strFile, _
True
strFile = Dir$()
Loop

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Ok thanks. I've moved all the files to a location on my harddrive. Any
suggestions on importing all 101 files at once? They are all in 1 folder
and
there are no additional files in that folder other than the ones that need
to
be imported.

Thanks again

Unfortunately, neither is possible: Access doesn't recognize the ftp
protocol (nor the http protocol, for that matter) for importing or
linking.

Your only option is to transfer them somewhere where you can get to them,
and then import from that new location.

There are a couple of possibilities for using ftp from within Access to
transfer the files. See http://www.mvps.org/access/modules/mdl0015.htm and
http://www.mvps.org/access/modules/mdl0037.htm at "The Access Web"

Thanks in advance for any time and assistance. I have 101 Excel files
located in a folder on an FTP site. All the files have a different name

[quoted text clipped - 6 lines]

Thanks again for any assistance

 

[Jeff F via AccessMonster.com]

Worked great, thanks

You can use the Dir function to create a loop that returns each of the
files, one at a time, and import them that way.

Something along the lines of:

Dim strFile As String
Dim strFolder As String

strFolder = "C:\Work In Progress\" ' The ending slash is important!
strFile = Dir$(strFolder & "*.xls")
Do While Len(strFile) > 0
DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel97, _
"MyTable", _
strFolder & strFile, _
True
strFile = Dir$()
Loop

Ok thanks. I've moved all the files to a location on my harddrive. Any
suggestions on importing all 101 files at once? They are all in 1 folder

[quoted text clipped - 21 lines]