Importing contents of an entire folder

  • Thread starter Thread starter Peter
  • Start date Start date
P

Peter

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
 
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
 
Will I be able to do this with excel files?
-----Original Message-----
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
Click to expand...
acImportDelim, "ImportSpecName", "AccessTableName",
 
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?
Click to expand...

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.
 
Back
Top