Convert Microsoft Excel to Microsoft Access

Asked By 40 points N/A Posted on -
qa-featured

I am using Microsoft office 2007.

My company have a worksheet to maintain their accounts.

Now they want to convert it to Access. Have any easy way to convert all data to access without losing any data?

 

If anybody work in this type of work please help me to solve this problem.

SHARE
Best Answer by Albert123
Best Answer
Best Answer
Answered By 0 points N/A #115876

Convert Microsoft Excel to Microsoft Access

qa-featured

To convert your existing worksheet of Excel to Access 2007, follow the steps given below:

1. Create a new Blank Database in Microsoft Access

2. Access will by default open a new blank table. Just Close that Table and do not save it.

3. Now On the Access Ribbon Click on the "External Data" Tab.

4. Click on Excel in Import Category

5. The above will open up a wizard that will import data from Excel. You Just go through the process.

6. Use the Browse Button to Specify Path to the Excel File.

7. Click on the Radio Button below to specify in which database you want the data to be imported. For the database created click on Import the source data into a new table in the current database and Click on OK button to continue.

8. Now Select the name of  Worksheet You want to import. and Click Next

9. Now on the next screen put a check that your first row in excel sheet contains the field names, If your sheet don't have field names then remain it uncheck. it will assign default names to fields, and you can change them later. Press the Next Button

10. Now is the time to assign data types to fields, select the field and put the type of data that is inserted in that field. After assigning a data type to all fields press Next

11. Now assign a field as a primary key if you want to, The field that contains Unique contents and Press Next

12. Now give the Table Name in the text box and press finish.

13. Now if you have Multiple sheets and have same process, save the steps you have done by checking the check box and this will save your time in future.

14. Click Close, You are all done.

Answered By 0 points N/A #115877

Convert Microsoft Excel to Microsoft Access

qa-featured

First you need to remove any data that you don't need from your worksheet by selecting the area, then right click your mouse and press cut/delete.

.

Open a new access workpage,

 

Select file and go to external data, import then switch the file type to excel(xml file) and then choose the worksheet to import.

Automatically access will create a Primary Key , this helps when combining the rows and columns.

 

Another way to do it is:

Highlight the area of the worksheet in excel you want to change to access, then right click your mouse and select copy.

Go to Access and form a new work area then right click your mouse and press Paste.

Answered By 0 points N/A #115878

Convert Microsoft Excel to Microsoft Access

qa-featured

Mr Paolitaviena, this is a very simple problem to solve and it is good you asked it since I will give you a step by step solution on which you should never have a problem.

  • You need first to open the Ms Excel and where the worksheet is. This you will do by going to Start->All Programs > Microsoft Office > Ms Excel
  • Once you open excel and the worksheet with the data,go to Ms Access and open.
  • Open Ms Access and create a new blank database where you will transfer the work.
  • Ensure you have your data organized in a consistent manner in rows of cells so that results for each potential field line up in database line under each other in the spreadsheet.
  • Rows of data should have a heading at the top. Follow good naming conventions
  • Once you have data setup, set a database range that covers all fields and all rows that you want to import for particular collection of data.
  • You may then import manually as shown in the diagram,but the method doesn't always usually work. Another method, is that you have to transpose the cells;
  • Select the cells you want to transpose.
  • Click Ctrl+C to copy
  • Select the upper left cell of paste area. Paste area must be outside of copy area
  • On edit menu click Paste Special
  • Select transpose check box
  • Use import command button from file menu in access.
  • Use linked tables. Access can link to a name range in a spreadsheet and will allow you to manipulate the data as if it were a table.

You need to use the transfer spreadsheet method in Visual basic or in macros.

This should be able to convert the files.

Hope this helps.

Answered By 0 points N/A #115879

Convert Microsoft Excel to Microsoft Access

qa-featured

Hi,

Friends I am telling you some easy setups to convert Microsoft Excel 2007 Data in Microsoft Access without losing your Excel Data. Please follow these steps:

1. Open Your Microsoft Access 2007 Database Document 
 

IM1
 

2. Click on blank database folder 
 

      
 

3. Choose Blank database file name and click on create option and create blank database file.  
 


 

4. Go to access menu bar and click on External Data menu option.
 


 

5. After, select and click Excel menu option. 
 


 

6. See This Dialog Box and click on file Browse Option.
 


 

7. Browse your Excel Data file you want to convert in access data.

 


 

8. See and check your browser name and click on ok button.
 


 

9. See and select your Excel worksheet name and click on Next button
 


 

10. Click on [ / ] first row contains column heading box and after click on Next button.
 


 

11. Here you can change you file name if you don’t want change your file click on Next button.


 

12. Your next Setup is that you can change your file row and columns style by selecting any primary key. If you want  to change it you can click on any key. if you don't want any style please click on next button.
 

    

13. It is your final setup click on finish button on this dialog box.
 


 

14. Your Data has been converted in Excel to Access. click on the right side sheet 1
 

 

15. And you can see Your Excel 2007 Data has been converted in Access 2007.

 

 

Thanks.

Vksdone

Login/Register to Answer

Related Questions