Importing excel files into Access with spaces and delimiters

[Adrian Scott]

Here are two sample file
Header

ID DATE AMT DESCRIPTION

02 11/1/11 25 This is my descrption

Header

ID DATE AMT DESCRIPTION
02 11/1/11 25 This is my descrption

In all cases the file begins with a row space. The header begins on line 2 in excel. The field headers begin on line3 and the data begins on line 4 or in some cases line 5. I need to programmatically remove the headers, account for the blank space and load about 20 files into access using either vba code or a macro. Location of the files would be something like
c:\myfiles\my_file_2003.xls There are about 20 files with the same my_file_yyyy or a different year. I want to pull in all twenty without having to pull each individual one

[dsatino]

The first thing you'll have to do is update your reference library by adding the Microsoft Excel Object Library.

From your VBA code, open Excel. Set up a loop to loop through the files in the folder. Within that loop, open each file in the excel application,process the data, close the workbook but not the application.

To process the data:
If each piece of data is in it's own cell, then just loop through the cells. If the data in each row is in just one cell then you'll have to get a little creative on extracting it. You can use a combination of the the InStr() and Mid() functions.