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
DoCmd.OutputTo(
ObjectType,
ObjectName,
OutputFormat,
OutputFile,
AutoStart,
TemplateFile,
Encoding,
OutputQuality)
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.
