Import multiple excel sheets as one access table

[austenr]

Anyone have any ideas how I can accomplish this. I have about 30 spreadsheets that I need to import into Access as a single table. Thanks

[stanl]

I would use Jet 4.0;

1. Set up Connection String [to avoid duplicate entries] as


"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & yourMDB &";Jet OLEDB:Global Partial Bulk Ops=1;"

2. Places workbooks,sheets,ranges into text or INI file to read in
3. For each entry in #2 issue (assumes Access Table Exists)


'INSERT INTO [yourTable] SELECT * FROM [sheetorRange] IN "" [Excel 8.0;Database=' & excelfile & ']'


.02 Stan

[austenr]

Could you put that in a macro? Not sure I follow that. Thanks

[stanl]

Oh; and if the table does not already exist, first use SELECT INTO.. to create a new table, then follow with INSERT INTO. I used to do this regularly to consolidate 75-100 workbooks from car dealerships, along with 75-100 dbf files, so it does work with all ISAMS, including Text. Stan

[austenr]

Thanks stan.

[Norie]

Can't you just use TransferSpreadsheet and specify the table to import to?

[austenr]

What do you mean? :devil2: TransferSpreadsheet whats that?

[XLGibbs]

Austen. If you go to the Macro sub option, and open a new macro in design view (Not Modules, Macros...in Access they mean slightly different things in the User INterface). You can select transfer spreadsheet as one of the options..you specify path and such (which can get tricky).

Best way is to use the import wizard and do them 1 by 1 given your unfamiliarity with some of the things that Access does.

How many are there?

[stanl]

You can select transfer spreadsheet as one of the options..you specify path and such (which can get tricky).


I agree TransferSpreadsheet is an option w/ the above caveat. What you choose will largely depend on your comfort level with DAO or ADO or Wizards [which I am not a fan of]; but more importantly the structure and logic of what your are trying to do. How is the Excel data laid out, 1 workbook, multiple sheets, multiple worlbooks/single sheet; is there a standard naming convention for sheets/ranges; is this a 1 time thing. My experience with situations like these is to always separate code from data - God bless MDAC and OLEDB Providers. Also in situations like you face, I found it easier to build the DB 1 time then create spreadsheets as needed rather than go the other way. Again, my personal inclination is ADO and if you are not too time constrained with your current project it is well worth getting familiar with as you would be surprised the results you can achieve for 3-4 lines of code. .02++ Stan