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

Copy Data from Access to Excel - Easy Techniques

October in New England is our prettiest time of year. The trees are changing in an explosion of color. Having lived here most of my life, I continue to appreciate the magnificent show that nature puts on here in the Northeast.

In autumn, nature goes through a time of transition. The trees are getting ready to move from the heat of summer to the cold of winter.

(Brace yourself for the stretch to tie this into Access.) Just as nature transitions from one season to the next, your data in Access can also transition from Access, which does a magnificent job of manipulating data, to Excel, which does a magnificent job performing mathematical operations.

This month, we will take a look at how to easily transition data from Access to Excel.

Super Simple Copy and Paste

By far the easiest way to move data from Access to Excel is to find the table in the Nav Pane, right-click and choose copy. Then open Excel and give the paste command. It couldn’t be simpler.

You can also do exactly the same thing with a query.

Less Simple, More Powerful: Copying Rows

If you are interested in copying only some rows of a table query, you use a different technique. In this case, open the table or query and select the rows that you are interested in copying into Excel. You do this by clicking on the gray box to the far left hand side of the first row you want to copy, hold down the shift key, and then click on the last row you’d like to copy. Once the rows are highlighted, do a control-c or copy, open Excel and then paste the data. Not only do you get your rows, but the headers as well. Again, this is a very easy technique that works for both tables and queries.

You can do exactly the same thing with columns. Simply highlight the columns of interest by clicking on the column header, press and hold the shift key, and select your last Column and copy.

Note that you can also copy non-contiguous rows or columns; check out the Tip of the Month for details on how to do it.

Copy and Paste On-The-Fly: Tougher But Very Powerful

You can programmatically export an entire table or query to Excel using VBA.

The general syntax is


ObjectType would typically be either acQuery or acTable
ObjectName is the name of your query or table
OutputFormat is an indicator of the type of file you’d like to create
OutputFile is the name of your target file
AutoStart is a true or false value that indicates whether or not you’d like to start Excel
Encoding is the type of character encoding format you want for the output
TemplateFile is used when you have a template that you’d like to use for your output
OutputQuality is a constant, AcExportQuality  that specifies the type of output device to optimize for.

The last three parameters are not typically used when exporting to Excel and are all optional.

Let’s say that we have a query called MyQuery that want to export to Excel. You can use the following code:

Sub ExportQueryOrTableToExcel
   DoCmd.OutputTo acQuery, "MyQuery", "MicrosoftExcel(*.xls)", "", True
End Sub

When you run this code, it will take your query and automatically send it to Excel, giving you the opportunity to provide a name and path on the way. Once you provided the information, Excel will open with your exported data.

Using this technique, you will be able to programmatically send tables and queries to Excel whenever you would like.

Conclusion and Coming Attractions 

This month, I showed you how to transition data from Access to Excel, ranging in complexity from the dead simple to the bit more complicated method of using Visual Basic code.

In the next Wizard, I will show you a technique that will allow you to reach beyond mere tables and queries but also to export any data that you would like using a SQL statement. Stay tuned – it is an astoundingly powerful method and not terribly difficult to set up.

Tip of the Month: Selecting Discontiguous Data

Don’t be turned off by the word discontiguous; it is a fancy way of saying not connected. Just like the state of Alaska is discontiguous from the lower 48 states, your tables and queries may have rows or columns that are also discontiguous and that you’re interested in exporting to Excel as we did in this month’s Wizard.

To select discontiguous data, highlight the first row or column, then rather than holding down to the shift key, hold down the control key instead. Then just choose other rows or columns that are not adjacent to your selection. From there, give the copy command and your data will be on the clipboard ready to be sent out the do

Trap of the Month:
Beware of Cutting Rather Than Copying

If you are using the techniques I showed you in this month’s Wizard, make sure that you don’t choose edit-cut or control X. This command will actually delete data from your table or query.

Luckily, if you use this technique and warnings are set to true, you will get a notice that you are about to delete data. However, if you tend to code your way around Access, and your code turns warnings off, you stand the possibility of losing data if you are not paying attention.

Quick Links:

Custom Software Home

Access Wizard Archives

Our Services

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