How to mass import workbooks innto ACCESS Please help

[Riley]

I know this is a snap for somone who knows how to do it, but I'm stumped.

I have hundreds of excel workbooks- each has a worksheet called import,
which has a range of cells that I would like to import into access
quarterly.

Each excel workbook is named according to customer. I can copy them into
another folder, then import the range in that import sheet from each
workbook, and kill the file until the folder is empty. I had this working
once in an access database at my old company, but I don't remember how I did
it.

I need the code. I know it wasn't many lines, but I can't figure it out
now.

Thanks in advance

Riley

 

[Guest]

I bet your code had something to do with filesearch, take a look at the
sample in Help for filesearch and that may help

 

[John Nurick]

Hi Riley,

Here's one way (untested air code):

Dim strFN As String
Dim strFolder As string

strFolder = "D:\folder\sub folder\")
strFN = "*.xls"

strFN = Dir(strFolder & strFN)
Do While Len(strFN) > 0
'Import
Docmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"MyTable", strFolder & strFN, True, "Import"
'Delete the file just imported
Kill strFolder & strFN
'get next filenam
strFN = Dir
Loop