Changing Excel spreadsheet into MS Access style database within Excel?

Question

I'm in the process of cutting and pasting some columns and rows of data from MS Excel to Access so it can be presented in a database format.

Is there a feature within Excel that can do this automatically?

I would rather leave the data in Excel as most people don't have MS Access.

Answer 1

You could try building an EXCEL FORM into 1 worksheet, then have it fill in the entered data in the appropriate column of another worksheet. I have never done it, but have seen it done before.
Read up on it in the Helpfiles, or check out the Microsoft Knowledge Base.

Answer 2

Dion;

Yes, you can "Get External Data". The more versatile method would be to "Link" the data.

Create a database. Create a new table. Format the table field names the same way as they are viewed in your spreadsheet. Make sure that there are no spaces in the name:
Correct - MySpreadsheet
Incorrect - My Spreadsheet

Do this on your excel sheet as well. Go to File>Get External Data>Link Tables. Follow the import wizard from there. In 2003 versions you can import one spreadsheet into one table. You may have to go through this process several times to get every thing linked.

This is what I call a "Down and Dirty" database. This keeps the two objects linked. When one updates, the other does as well. If you are emailing this document, make sure that the database table cells point to an ip address or http address that links to the sheet. You may need to email both documents otherwise and then you get a real mess. Speak with your IT person about setting this up.Gets the job done and makes my life a lot easier. The Forms and reports wizards do a good job of creating interfaces and printouts.

More information can be found by searching Access help with "Import or link data and objects".

God Bless

Frank Pytel

Answer 3

Not automatically. Do this:-

Make sure the Microsoft Excel data is in list format: each column has a label in the first row and contains similar facts, and there are no blank rows or columns within the list.

Close the Excel workbook that contains the data you want to use in Microsoft Access.

In Access, open the database where you want to copy the Excel data.

On the Access File menu, point to Get External Data, and then click Import.

In the Import dialogue box, click Microsoft Excel in the Files of type box.

In the Look in list, locate the file you want to import, and then double-click the file.

Follow the directions in the Import Spreadsheet Wizard.

Answer 4

Open Access and create an empty database. Then go up to File, Get External Data. You can then import your spread sheet or, as you want to leave the data in Excel for other people, I suggest rather than import, you link it so as you update the spreadsheet it will automatically update in Access too. If you don't know how to do this, though its almost step by step following instructions, from Excel, type in the Office Assistant export to Access, and you will be able to see a demo,

Answer 5

You could use Excel as a database with 1 table. That table could contain formulas to get values from other sheets with other tables. Word can get data from Excel using the mail-merge functions.

To copy the to Access, I would try to save the file as a CSV-file (comma-separated values), and import that in Access. Or try an external data source like Colin M explained.

Udo