Importing contents of an entire folder

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Peter - 30 Apr 2004 17:13 GMT

Is there a way to import the contents of an entire
folder. All files are excel and same format. Is there a
way to set up a macro for transfer spreadsheet and write
something in the file name field like "C:\foldername\*.xls?
any direction anyone could give me would be terrific.
enjoy the weekend all.
peter

Reply to this Message

Stuart - 30 Apr 2004 18:22 GMT

Something like

Private Sub btnImportAllFiles_Click()

'How to Import all Files in a Folder:
'Procedure to import all files in a directory and delete them.
'Assumes they are all the correct format for an ASCII delimited import.

Dim strfile As String

ChDir ("c:\MyFiles")
strfile = Dir("FileName*.*")
Do While Len(strfile) > 0
DoCmd.TransferText acImportDelim, "ImportSpecName", "AccessTableName",
_
"c:\MyFiles\" & strfile, True
'delete the file (consider moving it to an Archive folder instead.)
Kill "c:\MyFiles\" & strfile
strfile = Dir
Loop

End Sub

Regards

> Is there a way to import the contents of an entire
> folder. All files are excel and same format. Is there a
[quoted text clipped - 3 lines]
> enjoy the weekend all.
> peter

Reply to this Message

peter - 30 Apr 2004 19:02 GMT

Will I be able to do this with excel files?

>-----Original Message-----
>Something like
[quoted text clipped - 36 lines]
>
>.

Reply to this Message

Stuart - 30 Apr 2004 19:44 GMT

There are many more qualified to help, than me (and hopefully
someone will jump in) .......however, here goes:

Make sure all your testing is done with COPIES of your data!

> Will I be able to do this with excel files?

the line: strfile = Dir("FileName*.*")
defines the filetype to be copied (in this case any file,
as intended, because of the use of wildcards).
For Xl, files you could use
strfile = Dir("FileName*.xls") ie any file in the folder
with an xls extension.

Look in Access Help (VBA) for the use of 'TransferSpreadsheet'
and associated required/optional arguments etc. There are several
examples.

Perhaps a suitable Google search might elicit further examples?

You must consider whether you will use Access VBA to import
from Excel, or Excel VBA to export to Access. Whichever, I believe
ADO is the preferred method (rather than DAO).

Hope this is a help (expect others to correct my advice, if need be).

Regards.

> Will I be able to do this with excel files?
> >-----Original Message-----
[quoted text clipped - 47 lines]
> >
> >.

Reply to this Message