Conversion Excel Spreadsheets to One Access File

I was wondering if anyone could help me convert several excel spreadsheets into one access database. There are several sheets because the data is voluminous (over the 66,000+ limit for excel.)

Thanks in advance for any help on the subject

Hi,

Can't promise anything, but if you attach a small sample of the data (which I'm hoping that although it's over a number of spreadsheets, it's consistent), I'll try and do something for you in Access 2003.

One question if I may, do you have any working knowledge of Access?

Kind regards,

Robert

Robert,

Thanks for the response. I have little working knowledge of Access. I tried using the import wizard but when I tried to import the second worksheet to the same Access worksheet I received an error message with no reason as to the error or how to fix it. In the access help section it mentioned that it might not be able to be done with spreadsheet files.
I have attached a sample excel file with 2 worksheets as you mentioned. I am trying to incorporate them into one access worksheet.
Thanks for any help you can provide.

After uploading the sample data file to this thread, i retried the process using the import wizard and was successful creating one access sheet with the 2 excel worksheets. I am assuming when I tried it with the much bigger files earlier either 1) the data of the 2 excel worksheets were not formatted or organized the same way or 2) there were too many lines of data in the excel files. With respect to possible problem 1, obviously I can attempt to fix that. However, with respect to possible problem 2, do you know how many lines of data can be imported into one access worksheet?

Hi,

I have created the attached database which will import excel data based on the example you provided. To import data and use the database, note the following 6 steps:

1. The field names in the 'tblTradeData' table mirrors the titles in row 1 of your workbook. Simply change these (and the table name) if required.

2. As the 'TransferSpreadsheet' action only imports individual sheets, you'll have to make stand alone spreadsheets from each of your workbooks (I made two separate spreadsheets from your attached workbook, saved them into my C drive and named them 'Sample Data - Sheet1.xls' and 'Sample Data - Sheet2.xls'). Note down how many records are in each one.

3. Open the 'mcrImportSpreadsheetData' macro in design mode and change the existing directory path in the 'File Name' field to where your first import file you created in Step 2 is located. Save the change (Ctrl + S or press the Save icon from the Macro Design toolbar) and run it by either clicking the red exclamation icon on the Macro Design toolbar or by clicking Run from the Run menu item.

4. Repeat Step 3 for each of your import files. When you have finished, save and close the macro.

5. Open the 'tblTradeData' table and reconcile the number of records in the table from Step 2.

6. That should be it!

You can then either enter new data directly into the 'tblTradeData' table or import it via step 3 above.

Let me know how it goes and/or if you have any issues.

Kind regards,

Robert

Robert,

Thanks for your time and help! Looks great. good to see there are still people out their willing to spend time helping others.