The Access Wizard Newsletter Tips, Tricks, and Traps for Access Users and Developers
January 2012

Bringing Access and Excel Together: A Happy Marriage

On New Year's Day, I engaged in a tradition of taking a First Day Hike with my older sister. This walk is a fun annual event that gets a bunch of people out in the woods and gives my sister and me a chance to catch up on each other's lives. On this particular hike, my sister shared some joyous news: Her son had proposed to his longtime girlfriend. He had done this the previous evening, New Year's Eve (a bit of a romantic, that lad.)

When two people come together and things go according to plan, a real synergy takes place; two people join together and the results add up to much more than two individuals apart. This synergy also takes place in software. When Microsoft Access sends data to Excel and Excel sends data Access we get the number crunching power of Excel married to the data manipulation clout of Access.

In this Issue
  • Shameless Self-Promotion Department -I'll be presenting Access Programming Techniques in Connecticut on January 12 at 6:30 PM
  • Sending Data from Excel to Access
  • Porting Data in the Other Direction - From Access to Excel
  • Conclusion
  • Trap of the Month: Beware Blank Rows and Columns in Excel

  • Sending Data from Excel to Access

    There are many ways that you can get data from Excel to Access. By far, the easiest way is to cut and paste.

    Let's say you have the following data in Excel that you would like to get into Access.

    Getting the data to Access is simple. In Excel, highlight and copy all the cells with the data, including the two column headers.

    Open the Access database where you want to insert the Excel data. In the navigation pane, click Tables and Related Views. Right-click any place within the navigation pane and then choose Paste. You will be greeted with the following prompt:

    If your first row contains column headers as I suggested, you will, of course, click yes. Otherwise, click no. Note that, if you click no, your field names will be F1, F2, F3, etc., which is not terribly helpful. Hence my advice to make sure that you include the first row from Excel, which would contain your column headers.

    At that point, unless something unusual happens you'll get the following message:

    A new table will have been created at this point with the same name as the tab from Excel that you copied the data from. If you had not bothered to change the tab name in Excel, your table name will be called Sheet1 or something on that order. At this point simply open the newly created table and you will see something like the following:

    Note that, as expected, the column names carried over from Excel.

    Although there are many other ways you can get Excel data into Access, this is hands-down the easiest. In future Wizards, we will look into some of the alternative methods, which are perhaps more challenging, but also bring more power into the process.

    Porting Data in the Other Direction - From Access to Excel

    There are many ways to get data from Access to Excel. By far the easiest is to simply cut and paste. Open the table or query that contains your data, click on the upper left-hand corner of your data grid (the small square gray box) and do a control C or Edit | Copy.

    This will put your data onto the clipboard. From here, it's about as simple as you can imagine. Just open your Excel spreadsheet, click in the cell that you would like to be the top left of your data and then simply paste your data.


    As we've seen, trading data between Access and Excel can be remarkably simple and efficient. Like my nephew who is going to be joining the happy world of couples who enjoy the synergy of two folks coming together, we can also enjoy the synergy we get when we marry Access and Excel.

    Trap of the Month: Beware Blank Rows and Columns in Excel

    The process we described in this month's Wizard makes it ridiculously easy to get data from Excel into Access. There are some notable issues that you may run into. The most common ones stem from the way you have arranged your data in Excel.

    Excel makes it very easy to have a pleasant appearance in your spreadsheet with blank rows and columns to help with formatting and layout. If you have these blank rows and columns, when you copy this data into Access, you'll find that you end up with blank rows of data and empty fields with cryptic names (F1 for instance).

    Unless you have a lot of data that you are porting from Excel to Access these tend to be more of a nuisance than anything else. On the other hand, if you do have loads of data that you are moving this way, you will probably find it easier to structure the data in Excel so that there are no blank rows or columns prior to copying the data from Excel.

    Shameless Self-Promotion Department -I'll be presenting Access Programming Techniques in Connecticut on January 12 at 6:30 PM

    On Wednesday evening, January 11, 2012 (the day this Wizard is published) I'll be giving a presentation to the Connecticut Access User Group - CTAUG. My talk will focus on how to program efficiently in Access.

    If you're going to be in central Connecticut on the 11th, it will be a good chance to get some pointers on powerful database techniques and rub elbows with the members of CTAUG, an organization that does a great job helping people learn the ins and outs of Access.

    The announcement and details of the meeting are available at the CTAUG site

    Quick Links...

    Custom Software Home

    Access Wizard Archives

    Our Services

    Join our mailing list!

    Custom Software | Copyright Custom Software All Rights Reserved | Westford | MA | 01886