MS Access Database with External Data

Author

Import multiple files

Jeff F via AccessMonster.com

2005-09-26, 8:25 pm

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

--
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Fo...aldata/200509/1

Douglas J. Steele

2005-09-26, 8:25 pm

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"

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Jeff F via webservertalk.com" <u10039@uwe> wrote in message
news:54f532ddfbf54@u
we...
> 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
>
>
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Fo...aldata/200509/1

Jeff F via AccessMonster.com

2005-09-26, 8:25 pm

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

Douglas J. Steele wrote:[color=darkred
]
>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"
>
>[quoted text clipped - 6 lines]

--
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Fo...aldata/200509/1

Douglas J. Steele

2005-09-27, 9:24 am

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, _
acSpreadsheetTypeExc
el97, _
"MyTable", _
strFolder & strFile, _
True
strFile = Dir$()
Loop

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Jeff F via webservertalk.com" <u10039@uwe> wrote in message
news:54f72e37bf930@u
we...
> 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
>
>
>
> Douglas J. Steele wrote:
>
>
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Fo...aldata/200509/1

Jeff F via AccessMonster.com

2005-09-27, 1:24 pm

Worked great, thanks

Douglas J. Steele wrote:[color=darkred
]
>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, _
> acSpreadsheetTypeExc
el97, _
> "MyTable", _
> strFolder & strFile, _
> True
> strFile = Dir$()
> Loop
>
>[quoted text clipped - 21 lines]

--
Message posted via http://www.webservertalk.com

Sponsored Links