TransferSpreadsheet lets you move data out of Access by using an Access Macro. Don't worry, it's nothing horribly complicated but if you haven't worked with Access Macros before, you might be a little out of your element. Never fear - it's a very useful technique to learn and use often for basic repetetive tasks!

A macro is essentially just a bunch of Access commands bundled up into one little object. When you start that object, it runs through the commands that you give it in order to complete a task. For instance, I could open up an Excel Workbook and write my name in every cell in column A - all 65,000+ cells. Or I could just have a macro do it for me - saving me time and typing fingers! This is why macros are great!

In this case, we're not going to be looking at data that we're going to move - we're instead going to designate the data to be moved in the macro, and then move it without having to interact with it. Access has built-in Macro actions that allow us to do it easily.

Create a new macro, and in the first action box select "TransferSpreadsheet”.

You'll notice after selecting "TransferSpreadsheet” that you have a few options in the lower portion of the window available to you. Here's a basic run-down of what you're seeing:

TransferType: This is asking what you want to do with the data - are you importing or exporting? In our case, we're exporting because we want to send something to Excel.

Spreadsheet Type: This allows you to select different versions of spreadsheets for formatting purposes. If you're using anything from Excel 97 forward, best to select the most recent option - in this case "Microsoft Excel 8-10” which encompasses Excel 97, 2000, and XP.

Table Name: Don't be fooled, it doesn't have to be a table! In this case, I'm exporting a query called "qsel_Test”.

File Name: While you can use an existing file to transfer this data to, you can also just type a path and filename of a file that doesn't exist and Access will create it for you when the data is transferred over. Try "C:MyTestFile.xls". It will magically appear!

Has Field Names: This option isn't a big deal but can be important if you plan on working with this data much in Excel. Basically, it's asking if you want the column headings in your table or query transferred over. So if you have a table with a field called "SSN”, then "SSN” will appear in Row 1 of the Excel sheet, with data starting in Row 2. If this option is turned off, your data starts in Row 1 and you won't have any headings.

Range: If there is a specific range within the excel sheet you'd like to transfer the data to (for instance, put it in E33:Q79), then you can enter that here. This is generally a rarity as if you're going to be that specific, you'd probably be doing this in VBA Code.

That's it! Save the macro as something like, "mcr_XLExport” and run it by double clicking on it and voila. Check your C: drive and you've got an Excel file there with exactly what was in your query!

Using the macro is a fantastic option if you need to frequently export dynamic data out of Excel. While it's slightly inflexible (you'd have to continually change filenames unless you embellish the macro with more actions or code), it's a great way to consistently handle a repetitive exporting task. You can also quickly assign this action to a button on a form to create a quick and easy export utility of constantly changing data. There's hundreds of possibilities!