Thread: Initiate Export to Access from Excel

Is it possible to initiate an export from Excel to Access from Excel?

Excel Help does not specify this as an option. I also cannot find it discussed on forums.

Multiple users are using individual, but uniformed spreadsheets for data input. I am attempting to centralize the separate excel files into one access file. Even though the spreadsheet environment is not shared and centralized, the access file is centralized.

It is not feasible for me to run docmd.transferSpreadsheet from Access because there are too many excel files to reference. A better way is for the excel files to include an identical macro that references on Access file.

Secondary question: is it possible to export excel worksheets to other excel files? In this scenario I could centralize all the data into one excel file before importing that file into Access.

I welcome any suggestions on other ways to solve this problem of consolidating multiple uniformed spreadsheets into one file.

Thank you.

ADODB is awesome. That is going to give you the most control, but for larger sheets it could be slow since it uses a cursor. However I could be doing my ADODB wrong

On your secondary question, yes it is possible to normalize your data to one sheet using VBA to grab data from many sheets. This is pretty object heavy and you need to open the files from within Access to do so. On your main data sheet I would make a control sheet, where you list all the filenames/paths of the worksheets your users are updating. Then do a for/each for those.

For each wbPath in wbRange
     Workbooks.open(wbPath)
     ..do stuff to transfer data from the user sheets to your central sheet.
     Activeworkbook.close
Next wbPath

Then you have your data in one spot.

If you can get it to one sheet in a nice table/header format I would recomend using something like..

DoCmd.DeleteObject acTable, "myTable" 'Deletes your old table.
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "myTable", "C:\myWorkbook.xls", -1, "Sheet1!" 'Creates your new table from the data on sheet 1.

This will import a big chunk of data without having to go row by row. I have a report that has a 9k line excel feeder sheet, ADODB takes about a minute and a half to update. This takes about 10 seconds. I would say if you have a smaller recordset use ADODB, it's an amazing skill to learn for the future but a block import might be better for a larger recordset.