Multiple excell files import to Access

[LarsGe]

Is there a helpfull friend that can help me with this ?
From a folder (with sub folders) I need a batch code which selects all excel files (Each is a customer price lists, without me having to pick or name them individually) and imports them into an access tbl. The number of files to import is above 100 and growing. The tbl will be basis for a pricing management tool. This will be a rutine code to run, to run on open the db, by deleting the Tbl and rerun the code, I guess. The excell files have all the same lay out and contains empty rows, merged cells and similar.
The cell format in Excell is text and numbers all over the place.
As I not am very strong in VBA all help is appreciated

Thanks

 

[Blorf]

The excel files all have the same layout? They all have only one tab with the same name, IE sheet1? All named Sheet1?

Do they all have column heads also?

ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

 

[PHV]

Have a look at the Dir function and at the DoCmd.TransferSpreadsheet method.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[LarsGe]

Thanks Guys for trying to help me out.

BLORF, they all have the same lay out, all data on one sheet, ie Sheet one. There are several sections on the sheet which each has it own set of column headers. The number of columns varies from section to section. The sections goes under each other, not next to each other on the sheet, ie all data is in column A-H (or K), Section 1 is row 9-70, section2 is row 83-145, etc.
There is also some (few) fields without column headers with general information like Customer name, adress, date, payment term ,etc.etc.

PHV, Any chanse that you can point me in direction of where i can find some more detail as my VBA not is that good ?

 

[PHV]

Hopefully your Excel sheets use named ranges to distinguish the sections.
To find some more details on VBA topics play with the Object browser (F2) and the Help (F1) when in VBE (Ctrl+G).

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886