99.1% Of all customers recommend us, we're so confident about our results we publish all reviews and statsView Live Stats View Reviews
Importing Excel Data Into Access
Wed 29th December 2010
So it is pleasing to know that within Microsoft Office compatibility is king. You want to embed an Excel worksheet into a Word document? No problem. You think that Excel pie chart might add visual impact to your PowerPoint presentation? Consider it done. You want to use Word as your email editor in Outlook? You're only a few keystrokes away. Yes, Office applications really do get on very well with each other. It's like the high-tech equivalent of an episode of Friends.
Among all of this camaraderie, however, there are some inter-application tasks that cannot be performed as you might imagine. For example, one thing that you cannot do in Office is copy your Excel data and paste it directly into an Access database. This does not mean, however, that they are incompatible; it's just that Access must import the data rather than it being pasted. But this is a straightforward process as you can see below.
To import your Excel data into Access you must first open your Excel worksheet to make sure that the data to be transferred meets the following criteria.
1) The data is in list format - i.e., columns and rows and not in the form of a chart.
2) Each column has a label,
3) There are no blank columns or rows within the data to be exported, and
4) Your first label is in cell A1.
If you have ticked all four of these boxes then close down the worksheet. If you do not have some sample data that you could use to try this out, just fill a few rows with names and numbers and save it. But, and this is very important, your columns must contain similar data. You should not have a mixture of names and numbers within the same column so even if you are just knocking up a quick random list be wary of this rule. Close down your Excel worksheet and move on to Access.
For the purpose of this demonstration it would probably be best to create a new database but if you wanted to import Excel data into an existing database then you would simply open that one. So with your database open and ready to import, here is what you should do.
For pre-2007 versions of Access, go to the File menu and select Get External Data. From the options that appear click on Import and this will open up a dialog box. In the Files of Type box select Microsoft Excel and in the Look In box, browse for the file you wish to import and double click it. The Import Spreadsheet Wizard will offer you a few prompts and your Excel data will now be waiting for you as an Access database.
For Access 2007 and beyond, open your database, select the External Data tab from the ribbon and click on the Excel icon. In the File Name box browse for the Excel file you wish to import and click on it. Check the radio button to Import the source data into a new table in the current database, and click OK. Again the Wizard will activate to guide you through the rest of the process.
This is just the straightforward import of basic Excel data and there are many more aspects to it, some of which I will cover in detail in another article. But I hope this introduction has shown you that, although you can't copy and paste directly from Excel into Access, the ease of use when importing demonstrates that these two Office siblings really are quite compatible.
Original article appears here:
London's widest choice in
dates, venues, and prices
On-site / Closed company:
I thought initially the pace could have been quicker, but I think this is likely due to the fact I've had excel training before. I thought Richard was great in attending to individual questions - e.g. going through INDEX/MATCH and finer detail in MACROs.
The second half of the course I found the pacing great - I would have selected a more moderate answer for 'pacing' if there was one.
The course touched on a lot of topics that I was interested me in, and really impressed on me how useful Excel can be. I hope to go on the next course soon!
Deluxe 142 Ltd
Sarah was a great teacher made sure there was a good pace to the session and made sure everyone was keeping up! :)