Automating Microsoft Word and Excel from Access

The Microsoft Office Suite has a number of tools for communicating between its products, the most powerful of which is Office Automation through VBA (Visual Basic for Applications). By exposing the underlying object model for each of its applications, Microsoft made it possible to exercise programmatic control not only within a particular application, but between applications.

In my last article, Easy Excel Charting from Access, I showed how to create Excel chart from Access. The charting capabilities of Access using MS Graph are fairly primitive, so by sending the data to Excel, I can have a much richer set of charting tools. However, what I never covered is what to do with charts once they've been created.

It would be useful to paste these charts into an Access report, but that's not possible. However, it is possible to programmatically create a Word document, write data from your database to it, and then paste the appropriate chart into the document, making Word a very powerful and flexible reporting tool for Access.

Creating the Template

First thing to do is create a Word template. Word templates are Word documents saved with a DOTX extension. When you open a template, Word creates a new document based on the template. So the template itself is never modified. This makes it ideal for my purposes here. I'll create a template with the basic structure of my document and static text. As an additional benefit, an end user can modify the static parts of the document without requiring program modification.

There is no single right way to create the template, but perhaps the easiest is to first create a document that looks like the finished report. Something like Figure 1.

Figure 1: Create a document that will look like the final report.

Most of the information on this report, including the chart, will be filled in from information stored in the database. In order to create places for the information to be inserted, I need to create bookmarks. Bookmarks are placeholders within your Word document.

To create a bookmark, I place my cursor where I want the bookmark to go and click on Insert>Bookmark. A dialog box like Figure 2 will appear. I type in the name for my bookmark (in this case Condition) and click Add. I now have a bookmark. By default, bookmarks are invisible, so in order to see them, I click to Tools>Options on the menu bar and click the Bookmarks checkbox. The bookmark will show up as a gray I-bar. See figure 3.

Figure 2: Bookmark dialog showing the newly created Condition bookmark.

Place the cursor at the spots where the other bookmarks should be created and repeat the operation. When done, it will look like figure 3.

Figure 3: Document with bookmarks created.

All that is left to do is erase the information following the bookmarks and save the document as a Word template.

Word always wants to store templates in the default Templates folder in Program Files. Depending on how Office was installed, this folder could be different places on different machines. Therefore, I prefer to store the template in the same folder as the Access database or perhaps in a subfolder. This has the advantage of allowing the program to always know where the template is because as I will show later, I can programmatically determine the path to the application. So after I select Word Template (*.dot) in the File Type dropdown box, I browse to the correct folder to save the template. Figure 4 shows the completed template.

Once the template is created, I can re-hide the bookmarks. They don't need to be visible to work.

Figure 4: Completed template.

Automation Program Overview

Next, I create the Access subroutine (called ExcelWordAutomation), which automates the process of creating the Word documents. The overall process goes something like this:

1. Open the Word template to create the document.
2. Open the Excel spreadsheet that has pre-created charts for insertion.
3. Open a recordset with data for reports.
4. Insert data from the first record into the first report.
5. Copy the appropriate chart in Excel and past into the Word document.
6. Save the Word document with an appropriate name.
7. Loop to step 4 and repeat until the recordset is finished.
8. Close Excel, Word, and all object variables.

Setting a Reference to Word and Excel

Next, I switch to Access to create the export routine. But before I do that, I need to set a reference to Excel in Access. A reference is a call to an external code library, in this case the Excel Object Model. This will allow me to manipulate Excel objects from within Access.

To set a reference, I open the Visual Basic Editor. Then I go to Tools > References. In the dialog box, scroll down to Microsoft Word 15.0 Object Library and Microsoft Excel 15.0 Object Library (your version numbers may vary). Click the checkbox next to it and click OK. Figure 1 shows what the References Dialog looks like.

 
Figure 1: The References Dialog showing the Word and Excel Object Library reference.

So let's look at the program details. As always, I start the routine with a call to an Error Handler to trap a number of common errors. I'll explain the specific errors later.

Sub ExcelWordAutomation()
On Error GoTo HandleError

Then I need to declare a number of variables. In order to create and manipulate objects in Word or Excel, I need some object variables. The power of Office Automation is that Word or Excel object variables inherit the capabilities of the parent program. Ordinarily, I’d define them here, but for clarity, I’m going to define them throughout the code where they’re needed.

Next, I need some Access object variables.

Dim db As DAO.Database
Dim rsReportData As DAO.Recordset
Dim rsExclusions As DAO.Recordset

And lastly, I need some scalar variables.

Dim strsql As String
Dim strFile As String
Dim conPath As String
Dim X As Long
Dim wdGoToBookmark As Integer

As I mentioned earlier, I need to find the folder where the database resides. This is where the template is stored and also where I'll save the completed documents. Naturally, if I wanted the files stored elsewhere (say a specific directory), I could code that here, too. Fortunately, I can easily find the full path and file name of the database by using the Name property of the database object.

To do this, I'll instantiate a database variable with the CurrentDb function. Then with judicious use of the Mid, Len, and Dir functions, I can return the default folder, that is, the folder where the database resides.

Set db = CurrentDb
conPath = CurrentProject.Path

Since I have a single workbook which contains all the charts I need, each on a separate sheet, I'll open that first. In order to do that, I will instantiate first an Excel application object and then a workbook object.

Dim objXLBook As Excel.Workbook
Set objXLBook = objXLApp.Workbooks.Open(conPath & "\MySpreadsheetChart.xlsx")

In order to be able to watch the process of copying the charts, I'm going to make both Excel and the workbook visible. Technically, this is not necessary. The code will work just as well if Excel remains invisible, but it's interesting to watch the process.

objXLApp.Visible = True
objXLBook.Windows(1).Visible = True

Now, I need to open a recordset containing the data needed for the reports. This information is stored in a table called ReportData (see Figure 5).

Set rsReportData = db.OpenRecordset("ReportData", dbOpenSnapshot)

Figure 5: Table which stores the data to be inserted in each report.

Since each record contains the information for a single report, I'll step through the records one at a time, creating a Word document for each. A Do…While loop will work perfectly for this.

Do While Not rsReportData.EOF

Just as I did with Excel, I have to create a Word object. With Word, however, I only need to create the application object. I'll also open a new document based on the MyWordTemplate.dot template.

Dim objWord As New Word.Application
Set objWord = CreateObject("Word.Application")
objWord.Documents.Add conPath & "\MyWordTemplate.dotx"

And, again, I'll make them both visible. Like the Excel objects, these lines are optional.

objWord.Visible = True
objWord.Windows(1).Visible = True

Now that my document is created, I need to find the appropriate bookmarks and insert information from my recordset. Here, I use the "With…End With" construct to avoid having to repeat the objWord.ActiveDocument.Bookmarks object reference for each bookmark.

With objWord.ActiveDocument.Bookmarks
    .Item("Condition").Range.Text = rsReportData!Condition
    .Item("Medication").Range.Text = rsReportData!Medication
    .Item("Timeframe").Range.Text = rsReportData!TimeFrame
    .Item("Numerator").Range.Text = rsReportData!NumeratorDef
    .Item("Denominator").Range.Text = rsReportData!DenominatorDef
    .Item("Target").Range.Text = rsReportData!Target
End With

'find and write exclusion data
strsql = "SELECT ReportID, Exclusion " & _
    "FROM ExclusionData " & _
    "WHERE ReportID=" & rsReportData!ReportID
  
Set rsExclusions = db.OpenRecordset(strsql)
    Do While Not rsExclusions.EOF
    With objWord.ActiveDocument.Bookmarks
        .Item("exclusions").Range.Text = rsExclusions!Exclusion & vbCrLf
        rsExclusions.MoveNext
    End With
Loop
rsExclusions.Close

To paste the chart into the document, I'm going to use the Windows Clipboard. So I have to switch to my already open Excel workbook, find the sheet matching the medication value in my recordset and copy it to the clipboard.

objXLBook.Sheets(rsReportData!Medication.Value).Select
objXLBook.ActiveSheet.ChartObjects("Chart 1").Activate
objXLBook.ActiveChart.ChartArea.Select
objXLBook.ActiveChart.ChartArea.Copy

Then I return to my Word document, move my cursor to the "Chart" bookmark, and paste the chart into the document. In this case, setting the Word object to visible is mandatory. This method of pasting from the clipboard requires the object to be activated, and in order to be activated, it must be visible.

objWord.Activate
wdGoToBookmark = -1
objWord.Selection.Goto What:=wdGoToBookmark, Name:="Chart"
objWord.Selection.Paste

That's all I need in the document, so I'll save the document in the same directory as the template, naming it after the medication value in my recordset.

objWord.ActiveDocument.SaveAs (conPath & "\" & rsReportData!Medication & ".docx")
objWord.Quit

And then, I return to process the next record, looping until the recordset until done.

    rsReportData.MoveNext
Loop   

After I've processed all the records, I'm done with the Excel workbook, so I'll close it without saving and close Excel.

objXLBook.Close SaveChanges:=False
objXLApp.Workbooks.Close
objXLApp.Quit

Then I add a message box that identifies when the process is done.

MsgBox "Done!" & vbCrLf & vbCrLf & _
    "Look in this directory" & vbCrLf & conPath & vbCrLf & _
    "for your documents."

Lastly, I complete the error trapping. After the ProcDone label, I'll destroy the object variables I've created. I do that here so if there is an error and the routine terminates, no Word, Excel, or Access object will be left in memory.

ProcDone:
' clean up our object variables
Set objXLBook = Nothing
Set objXLApp = Nothing
Set objWord = Nothing
Set rsReportData = Nothing
Set rsExclusions = Nothing
Set db = Nothing
   
ExitHere:
    Exit Sub

There are three main errors that must be handled. Error 432 occurs if the Excel spreadsheet is not found. Since no other objects are open, I just want it to end the routine without doing anything else. Error 5151 occurs if the Word Template does not exist. In that case, I want the routine to close the Open Excel object and end the program. Error 4152 can happen in a variety of circumstances, all of which come down to an error in the file or path. Since both Word and Excel objects are open at the time of the save, I want both objects to close without saving. The last error, 9, can happen when a record has been added to the table without a corresponding spreadsheet in the workbook. This error is handled just like 4152, but displays a different error message.

All other errors will be handled by the Case Else and will result in the error description and number being displayed and the program ending. It's always a good idea to add a generic error handler as users can always find ways to create errors that the developer can't anticipate. Since I don't know what the error might be, I don't know what objects might be open, so I don't attempt to close them.

HandleError:
    'display appropriate error message
    Select Case Err.Number
        Case 5151 'Word template not found
            'Close stranded applications
            objXLBook.Close SaveChanges:=False
            objXLApp.Quit
            MsgBox "Word template not found"
        Case 432 'Excel spreadsheet not found
            MsgBox "Excel spreadsheet not found"
        Case 5152 'Invalid file name
            'Close stranded applications
            objXLBook.Close SaveChanges:=False
            objXLApp.Quit
            objWord.ActiveDocument.Close SaveChanges:=False
            objWord.Quit
            MsgBox "This file or folder does not exist"
        Case Else
            MsgBox Err.Description, vbExclamation, _
             "Error " & Err.Number
             Set objXLBook = Nothing
Set objXLApp = Nothing
Set objWord = Nothing
Set rsReportData = Nothing
Set rsExclusions = Nothing
Set db = Nothing
   
    End Select
    Resume ProcDone
End Sub

Running the Code

Now the code is complete and ready to run. All that's necessary is to call the routine from a button or some other Event Procedure, like so:

Private Sub cmdRun_Click()
  Call ExcelWordAutomation
End Sub

On completion, your folder will have one report document for each record in the ReportData table.

Conclusion

Office Automation is a powerful tool for integrating separate Office applications. By using a single macro language for all of the Office applications and exposing the object models of each, Microsoft made it possible to build custom applications that would be difficult any other way. The possibilities are limited only by the imagination of the developer.

You can download a sample database illustrating all the code here, AutomatingWordFromAccess .

.

Easy Excel Charting from Access

One of the strengths of the Microsoft Office suite is the ability for its component parts to communicate between themselves. It is particularly useful to communicate between Access and Excel because while Access is superior at storing data, Excel is superior at manipulating it. For example, I am often asked if it's possible to send data from Access to formatted cells in Excel and create a chart based on it.

This problem can be solved by extensive use of Office Automation, but many people find this prospect daunting. Office Automation through VBA (Visual Basic for Applications) is an extremely powerful but complicated method. I discussed this method in my post: How do I export Access data to Excel - Part 3.

But there are other methods, like the Access TransferSpreadsheet method, that are easier to use, but far more limited. How do I export Access data to Excel - Part 2

A Middle Ground

However, it's also possible to solve with a combination of built-in features of both Access and Excel, that is, Excel templates, a tiny bit of Office Automation, and the Access TransferSpreadsheet method. This middle ground uses the strengths of both, and is both easy and flexible.

The TransferSpreadsheet method allows me to export a query or table from Access to Excel. If the workbook does not exist, it will create one. If the workbook does exist, it will create a new sheet in the workbook named after the table or query. But if both the workbook and sheet already exist, Access will over-write the data in the sheet. This is the critical feature.

Another feature I'll make use of is Excel's ability to link cells from one sheet to another. This means I can link a chart on one worksheet to another worksheet that holds the data. If I use the TransferSpreadsheet method to export a query that overwrites the data in the data worksheet, my chart will be updated automatically.

Lastly, I will use an Excel template to create a new Excel workbook with pre-formatted cells and charts. An Excel template is a special kind of workbook with a .xltx extension. When you open a template, it automatically creates a new workbook with a .xlsx extension, leaving the template untouched.

These features, used in combination with a small amount of Office Automation, give me all the tools I need to accomplish the task.

Overview

The overall process goes something like this:

1. Create an empty Excel template (.xltx)
2. Create the Export code to do the following:
   1. Open the Excel template
   2. Save the resultant workbook
   3. Export my data to the workbook with the TransferSpreadsheet method.
3. Run the export program to create a new workbook with the exported data.
4. Create a new worksheet in this workbook with whatever data formatting and charts I need, linked to the data worksheet.
5. Save this workbook as template, over-writing the previous.
6. Clear the data from the exported worksheet.

Creating the Template

First thing to do is create an Excel template.

You might think I could simply create a data worksheet manually and name the tab after my exported query. Unfortunately, it's not that easy. That's because the TransferSpreadsheet method looks for a named range to export to, not a worksheet name. So if I create a worksheet named after my query, say Drug1, when I export the query, it will create a new worksheet called Drug1(1) instead of exporting to the worksheet I want.

The easiest way around this is to use the Access TransferSpreadsheet to export my query to a blank workbook. This will create the data worksheet automatically for me with the proper named range. Then I'll re-save it as a template so the export routine will find the correct worksheet for the next time.

So to start, I create a blank workbook and save it as a template. To do that, choose File > SaveAs and in the file type box choose Template(*.xltx). Now, Excel will try to save this in the default templates folder. I prefer to store this template with the database, so I then browse to the folder where the database exists and save it there.

Setting a Reference to Excel

Next, I switch to Access to create the export routine. But before I do that, I need to set a reference to Excel in Access. A reference is a call to an external code library, in this case the Excel Object Model. This will allow me to manipulate Excel objects from within Access.

To set a reference, I open the Visual Basic Editor. Then I go to Tools > References. In the dialog box, scroll down to Microsoft Excel 15.0 Object Library (your version number may vary). Click the checkbox next to it and click OK. Figure 1 shows what the References Dialog looks like.

 
Figure 1: The References Dialog showing the Excel Object Library reference.

Export Program

Next, I'll create the Access subroutine (called ExportSpreadsheet), which exports the data to Excel. I start the routine with a call to an Error Handler. I'll explain why later.

Sub ExportSpreadsheet()
On Error GoTo HandleError

Next, I'll declare some variables.

Dim objXLApp As Object
Set objXLApp = CreateObject("Excel.Application")
Dim objXLBook As Excel.Workbook
Dim strFile As String
Dim strPath As String

I also need to find the folder where the database resides. This is where the template is stored and also where I'll save the completed workbook. Naturally, if I wanted the files stored elsewhere (say a specific directory), I could code that here, too.

strFile = CurrentProject.Name
strPath = CurrentProject.Path

Now, I want to delete the existing workbook if it exists. I do this keep the SaveAs dialog box from asking me if I want to over-write the existing file. This is most useful if I am creating multiple workbooks.

Kill strPath & "\MySpreadsheet.xlsx"

Next, I need to create a workbook from the template. To do that, I have to use a tiny bit of Office Automation. I have to open an Excel Application object and an Excel Workbook object.

Set objXLApp = New Excel.Application
Set objXLBook = objXLApp.Workbooks.Open(strPath & _
       "\MyTemplate2010.xltx")

Next, I save the workbook object as a workbook and close it.

objXLBook.SaveAs (strPath & "\MySpreadsheet.xlsx")
objXLBook.Close

Which leaves a file called "MySpreadsheet.xlsx" in the same directory as my Access database. Next I use the TransferSpreadsheet method to export two queries to the workbook. Because I am specifying the same workbook, each query will create a separate worksheet in the workbook.

DoCmd.TransferSpreadsheet acExport, , "qryDrug1", strPath & _
      "\MySpreadsheet.xlsx", True
DoCmd.TransferSpreadsheet acExport, , "qryDrug3", strPath & _
      "\MySpreadsheet.xlsx", True

Sometimes, the chart data is not refreshed in the resulting workbook, so I'll open the workbook and save it again. This takes care of the refresh problem.

Set objXLBook = objXLApp.Workbooks.Open(strPath & _
     "\MySpreadsheet.xlsx")
objXLBook.Save
objXLBook.Close

Then I add a message box that identifies when the process is done.

MsgBox "Done!" & vbCrLf & vbCrLf & _
     "Look in the directory" & vbCrLf & vbCrLf & _
     "where the application resides for ""MySpreadsheet.xlsx"""

Lastly, I complete the error trapping. After the ProcDone label, I'll destroy the object variables I've created. I do that here so if there is an error and the routine terminates, an Excel object won't be left in memory.

ProcDone:
Set objXLBook = Nothing
Set objXLApp = Nothing
ExitHere:
Exit Sub

There are two main errors that must be handled. Error 1004 occurs if the Template does not exist. In that case, I just want the routine to end without doing anything else. The other error, 53, happens when the MySpreadsheet.xlsx file (that I'm trying to delete with the KILL command) does not exist. In that case, I just want the routine to continue.

HandleError:
Select Case Err.Number
Case 1004 'a template does not exist
     MsgBox "There is no template for this chart."
     Resume ProcDone
Case 53 'Excel file cannot be found to delete
     Resume Next
Case Else
     MsgBox Err.Description, vbExclamation, _
          "Error " & Err.Number
     Resume ProcDone
End Select
End Sub

In this example, I am going to export two queries (Drug1 and Drug3) to my spreadsheet and create two separate charts based on them. Because of that, I'm going to hard code the query names into the routine. But to utilize the real power of this process, you could store a list of queries to be exported in a table, then create a loop that would march through the table, exporting each query in turn. In this way, you could create literally hundreds of charts in just a few minutes. If your process requires dozens or hundreds of charts created every month, this could be quite handy.

Running the Code the first time

So far, all I've got is an empty template and my Export routine. The next step is to run the Export code for the first time. When that happens, my previously empty spreadsheet has two worksheets: qryDrug1 and qryDrug3.

Figure 2 shows the resulting workbook.

Figure 2: Workbook created by the first run of the ExportSpreadsheet subroutine.

Create worksheet with formatted data and chart.

Now I have to manually create two new worksheets, which I'll name Drug1 and Drug3. These will hold my formatted data and charts. (Since the process is the same for both charts, I'll just concentrate on Drug1, but you should realize that you can create as many charts as you want up to the 255 sheet limit of Excel.)

So next, I need to link the cells containing the data in sheet qryDrug1 into my new Drug1 sheet. To do that, I open the Drug1 sheet and select cell A1, hit the equal key (=), click on the qryDrug1 tab to go to that sheet, click cell A1 in that sheet, and finally click the green check mark on the Formula bar. The resulting formula looks like this: =qryDrug1!A1. Next, select cell A1 and click the Copy button, select the range A1:C13 and click Paste. Figure 3 shows the resulting worksheet.

Figure 3: Worksheet of cells linked to the data on qryDrug1.

Now I can format this data. For simplicity, I'll just apply an auto format, then I'll format the cells in column B as Percent with no decimal places. Figure 4 shows the results of the formatting.

But I'm not done with this sheet. I also want to create a chart on this data. So I'll use the Chart Wizard to create a bar chart comparing the drug prescription rate for each physician. Figure 4 also shows the resulting chart.

Figure 4: Formatted data and chart based on the data linked on the worksheet.

Notice that while the chart is based on the formatted data, the actual data resides on the qryDrug1 worksheet. This is important because the Drug1 information in the qryDrug1 worksheet is not formatted as percent. If I based the chart on the qryDrug1 worksheet, the Y axis of my chart would not automatically be formatted as percent either. By linking the data and formatting it, I can control the format of the chart.

Repeat these steps for the Drug3 worksheet.

Save Workbook as Template

I'm almost done now. All that's left is to save my finished spreadsheet as a template, overwriting my existing MyTemplate.xltx template. Again, Excel will try to save the template in the default Templates folder, so I have to browse to the application folder to save it over the existing template.

Lastly, I need to delete the data in the data worksheets: qryDrug1 and qryDrug3. It's very important to just delete the data and NOT the worksheets themselves. To do so will put #REF in each linked cell and all the linking will be lost. But just clearing the data from the exported data worksheet will leave the links intact. Figure 5 shows the cleared worksheet.

Figure 5: Formatted data and chart with data deleted from the qryDrug1 worksheet.

Re-Run the Program

That's all there is too it. Running the program again will open the new template, save it as a workbook, export my queries to the existing worksheets, which will display in the linked cells and chart. Figure 6 shows the final result.

Figure 6: Results of running the export to the completed template.

Conclusion

Microsoft Access has some powerful tools for communicating with Excel. Some of these tools, like the Transfer Spreadsheet method, are very easy to use but limited. Others, like Office Automation, are flexible but complicated. But as I have shown, by using the strengths of both products, you can accomplish this in a way that that is both easy and flexible.

To download a working sample database, follow this link: ExportToExcelCharts.mdb (intermediate)

How do I export Access data to Excel - Part 1

There are many ways to export data from an Access database to an Excel spreadsheet.  These include:

1. Manual Methods (This post) 
   • Cut and Paste
   • Manual Export
   • Saved Exports
2. TransferSpreadsheet Methods
   • Macros
   • VBA
3. Office Automation Methods
   • Do…While
   • CopyFromRecordSet

Linked Excel Sheets Not Updateable
One surprising way that you CANNOT export Access data to Excel is by linking the spreadsheet into Access. Linked Excel spreadsheets are Read-Only in Access. Why? 
The answer is not technical but legal.  Several years ago, Microsoft lost a patent infringement lawsuit that involved the ability to update records in Excel from Access.  The upshot is that in Access 2003 and later versions, by design, you can no longer update data in Excel spreadsheets.
In this part, I’ll discuss manual methods and leave TransferSpreadsheet and Office Automation for later.
Cut and Paste
I remember a time when I couldn’t take for granted that everyone knew what cut and paste was or how to do it.  That’s no longer the case, but I thought I should mention it for completeness.
Sometimes the quickest and easiest way to get Access data into excel is just open the table or query in Datasheet View

and paste it into a blank workbook.

There are limitations, of course.  You can copy whole rows


or whole columns


But not a combination.  You can work around this, however, by creating a query that limits the rows and columns to just those you want. 
Manual Export
You can also export Access data to Excel without opening a workbook by using Access’s built in export facility.  With this, you can export tables or saved queries to either a new or existing Excel workbook.  There a several ways to do this.
On the Ribbon, there is the External Data tab where you can export in many different formats. Select the query or table you want to export in the Navigation Pane, and select Excel on the Export section of the External Data tab.

Or, you can Right Click on the object for context sensitive menu

After that, it’s just a matter of giving a file location and naming the file.

By default, the name will be the same as your table or query.  You can, of course, change either at this point.
New Vs. Existing Excel Workbook
You can export to either a new workbook or an existing workbook.There are several possibilities:

1. New Workbook – XLSX file does not exist
   • If the file name (TheDataQuery.xlsx) does not exist in that folder, a new workbook will be created with that name and a tab named for the object (TheDataQuery).
   • If you change the filename in the  and that new name also does not exist, the file will be created under the new name also with a tab named for the object.
2. Existing Workbook
   • If the workbook exists, AND it already has a tab named for the object, the data in the tab will be over-written.This will become important later when I discuss the TransferSpreadsheet Method. (Note: if the new dataset is narrower or shorter than the data already on the tab, only the cells in the shape of the new data will be over-written.) 
   • If the workbook exists and it DOES NOT have an existing tab named for your object, a new tab will be created, leaving all existing tabs alone.

Saved Exports
After clicking OK in the Excel – Export Spreadsheet dialog, you will be given one last option: Save Export Steps. If you check the Save export steps box, the export will be saved.

Running Saved Exports
Lastly, to run the saved export, just click the Saved Exports button on the External Data ribbon tab.
This has some limited utility, but no flexibility. It will export the same object to the same file in the same location. On the plus side, you don’t have to go through the whole Export Wizard again.
Next time in How do I export Access data to Excel - Part 2, I’ll discuss more flexible export automation techniques.

How do I export Access data to Excel - Part 2

There are many ways to export data from an Access database to an Excel spreadsheet.  These include:

1. Manual Methods
   • Cut and Paste
   • Manual Export
   • Saved Exports
2. TransferSpreadsheet Methods (this article) 
   • Macros
   • VBA
3. Office Automation Methods
   • Do…While
   • CopyFromRecordSet

In Part 1, I discussed various manual methods.  This time I’ll look at the TransferSpreadsheet method, which allows you to have more control over automating exports.

Import Export Spreadsheet – Macro Action

Macros in Microsoft Access offer a quick easy way to automate some processes.  Macros are created and maintained in a graphical user interface, You don’t have to do any coding or know a programming language.

To create a new macro, go to the Create tab on the ribbon and choose Macro.

The Macro Editor will look like this

To add a new macro action, click the drop down, Add New Action.  However, you’ll note that the ImportExportSpreadsheet action is not in the list.

 

This is because when Action Catalog is selected on the ribbon, you only get actions the Microsoft considers “safe”. In this case, that means that using any of these actions don’t require the database to be in a Trusted Location. Unfortunately, ImportExportSpreadsheet is not one of those actions, so you’ll need to select Show All Actions, instead.

 

Now you can select ImportExportSpreadsheet.  You’ll get a number of options to fill out:

1. Transfer Type: Obviously, we want Export, but the same action can be used to Import or Link data.
2. Spreadsheet Type: Excel Workbook exports to your installed version of Excel. If you need to export it in a different spreadsheet format, you can change it here.
3. Table Name: The name of the Table or Query you want to export.
4. File Name: The path and file name of the spreadsheet. The filename will have a direct effect on how the export behaves.
   • If the file does not exist, a new spreadsheet named for the table will be created and the data will be placed on a tab also named for the table (i.e. TheDataQuery)
   • If the file does exist, AND named tab also exists, the data in the tab will be over-written.
   • If the file does exist BUT the named tab does not exist, a new tab named for the table will be created. Existing tabs will be left alone.
5. Has Field Names: If Yes, it will put the field names in row 1 of the spreadsheet.

Run the macro using the Run button on the Design tab.

It will create a workbook named Data_Spreadsheet.xlsx with a datatab called TheDataQuery.

Why Bother?

There’s no real value to creating a macro for a one-time export.  However, a macro will allow you to export multiple times or multiple “tables” or both. So to export a second “table”, I simply add another macro action.

The file name also determine how multiple exports are handled.

• If the filenames are the same for multiple exports, the will be exported to the same workbook.
• If the filenames are different, they will be exported to different workbooks.

Transfer Spreadsheet – VBA Method

The Macro method is useful if you always export the same tables or queries to the same locations.  It’s also very easy to set up.  However, since all the table names and path/file names are hard coded, to change anything, you have to modify the application.  This is less than ideal if you want to the application to be used by non-developers.

A better way is to store the names of the queries/tables in a tables and use a bit of VBA to repeat the export process for each value in the table.

Converting Macros to VBA

Fortunately, you don’t have to start from scratch.  You can convert an existing macro to a VBA procedure, which will give you the basic layout.  To do this,open the macro in Design View and click: Convert Macros to Visual Basic

The procedure will appear in a Module named for the macro and will look something like this:

'------------------------------------------------------------
' Macro2
'------------------------------------------------------------
Function Macro2()
On Error GoTo Macro2_Err

    DoCmd.TransferSpreadsheet acExport, 10, "TheDataQuery", _
        "C:\Users\roger_000\Documents\Access\Data_Spreadsheet.xlsx", _
        True, ""
    DoCmd.TransferSpreadsheet acExport, 10, "TheDataQuery2", _
        "C:\Users\roger_000\Documents\Access\Data_Spreadsheet.xlsx", _
        True, ""

Macro2_Exit:
    Exit Function

Macro2_Err:
    MsgBox Error$
    Resume Macro2_Exit

End Function
'------------------------------------------------------------

If I had named my macro something relevant, like “Export_Data”, the procedure would be named for that.

Creating an Export table.

Next, I will create a table called MyExport which will hold the text values of the table and filenames I want exported.

Then modify the code as follows.  I’ve added comments in the code to explain the modifications

'------------------------------------------------------------
' Export_Data
'------------------------------------------------------------
Sub Export_Data()
On Error GoTo Export_Data_Err

'add object and scalar variables
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim TheTable As String
Dim TheFile As String

'open the Export Table (MyExport) as a recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("MyExport")

'loop through recordset
Do While Not rs.EOF

    'set scalar variables
    TheTable = rs!Export_Table
    TheFile = rs!Export_Filename
   
    'export the table using the variables
    DoCmd.TransferSpreadsheet acExport, 10, TheTable, _
        TheFile, True, ""
       
    'move to the next record
    rs.MoveNext
Loop

Export_Data_Exit:
    Exit Sub

Export_Data_Err:
    MsgBox Error$
    Resume Export_Data_Exit

End Sub
'------------------------------------------------------------
Note: I prefer Sub procedures rather than functions in this case, so I modified it shown in the highlighting.

Now Just Run It

Next I just run the code (usually by way of a button on a form), and the indicated tables/queries will be exported.  In this case to the same workbook.  I can export them to different workbooks by simply changing the filenames in the MyExport table.  This can be useful if you want each query to go to a different user’s folder.

Download A Sample

You can find the companion sample here: ExportToExcel_TransferSpreadsheet

Taking It Further

You can take the automated process even further by exporting the data for a formatted sheet or chart using an Excel template. You can download samples with complete explanation here:

• ExportToExcel.mdb ( intermediate )
  This sample demonstrates how to export data from a database using the TransferSpreadsheet method, but have the data populate a formatted spreadsheet. The trick here is to export the data to a NEW tab in the Excel workbook and link the fields from the new tab into the formatted spreadsheet.
• ExportToExcelCharts.mdb ( intermediate )
  Sample with Documentation. This sample demonstrates how to export data from a database using the TransferSpreadsheet method, but have the data populate a Chart. The trick here is to export the data to a NEW tab in the Excel workbook and link the fields from the new tab into the chart.

Next up, in Part 3, I’ll show how to automate exports even further using Office Automation.

How do I export Access data to Excel - Part 3

There are many ways to export data from an Access database to an Excel spreadsheet.  These include:

• Manual Methods
  • Cut and Paste
  • Manual Export
  • Saved Exports
• TransferSpreadsheet Methods 
  • Macros
  • VBA
• Office Automation Methods (this article) 
  • Do…While
  • CopyFromRecordSet

In Part 1, I discussed various manual methods. In Part 2, I discussed TranferSpreadsheet. This time I’ll look at Office Automation, which gives you maximum control over automated exports.

Office Automation

Office automation allows a developer to open and manipulate other MS Office applications in VBA code.  It allows the Access developer to use the specialized functions of those other programs, functions that would be hard to create natively.  I’ve used automation to create documents in Word, charts in Excel, and presentations in PowerPoint.  Sometimes all three from the same Access application.

Here are a few examples:

AutomatingWordFromAccess.mdb ( advanced)

ExportToExcelCharts.mdb ( intermediate )

AutomatingPowerpoint.mdb ( intermediate )

In this case, I want to export data from an Access database into specific formatted cells in an Excel spreadsheet. To do this, I need to use the Data Access Object model built into all Office apps.

Excel Object Library (VBA)

Through VBA, a developer can open and manipulate other Office applications. This is not limited to MS Office applications, but that’s another discussion. Each Office application has its own Object Model, each with its own Collections, Methods, and Properties. Once I reference that model in Access, I can use VBA to use that application’s Collections, Methods and Properties. Since I want to automate Excel, I need to use the Excel Object Library.

Setting References

In order to use an external object model, I need to first set a Reference to it. To do that, I need to open the Visual Basic Editor (VBE), which can be opened in Access on the Database Tools tab and choosing Visual Basic.

Once the VBE is open, choose Tools > References

The Reference List will look like this:

The first three checked references will automatically be there in a new Access database.  The fourth line WON’T.  You need to scroll down the list (to the “M”s) to choose Excel.  Check the box and click OK. Reopen the References, and it should look something like the above.

Depending on your Office version, the references may be named differently, there may be additional references, or they may be in a different order. It’s not important most of the time.  The important thing is to find and check the Excel reference.

Data Access Objects (DAO)

DAO is the object model used to open and manipulate databases in VBA.  DAO is not part of Access. Technically, it’s the object model for the Jet database engine, and it can be referenced in any Office application to manipulate data.  However, since Access uses the Jet database engine as its default, a reference is set to it whenever an Access database is created. Since I need to take data from an Access database and send it to an Excel worksheet, I need both object models.

Two Methods

There are two ways of using Office Automation that I want to discuss:

1. Using a Do…While loop to PUSH data from Access to Excel
2. Using the CopyFromRecordset to PULL data into Excel from Access

Regardless of which method I use, there is a basic framework of creating and instantiating objects I need to build.  This framework looks like this:

'First I need to define some variables. Ordinarily, I would define all my variables here, but in this case, I’m going to define them as I go for clarity.
Dim RowVal As Integer
Dim ColVal As Integer
Dim conPath As String

'  Find the path where the database resides
    conPath = CurrentProject.Path

‘  Define and Open Database Object
    Dim db As DAO.Database
  Set db = CurrentDb

'  Define and Open Recordset Object based on a query in my database called “TheDataQuery”. I could, of course be any saved query.
    Dim rs As DAO.Recordset
    Set rs = db.OpenRecordset("TheDataQuery")

'  Define and Open Excel as an Excel Object. This is just opening Excel itself without a workbook.
    Dim objXLApp As Excel.Application
    Set objXLApp = Excel.Application

' Create and Open an Excel "Workbook" object. The workbook (that is Excel file) must exist in the place given in the .Open statement.
        Dim objXLBook As Excel.Workbook
    Set objXLBook = objXLApp.Workbooks.Open _
        (conPath & "\Export_ForNext.xlsx")

' Create and Open a "Worksheet" object. The tab, in this case named TheDataSheet, must also already exist.
    Dim objResultsSheet As Excel.Worksheet
    Set objResultsSheet = objXLBook.Worksheets("TheDataSheet")

' Create a "Range" object.  This is a named range in Excel which defines a certain group of cells. The actual values in the Range argument depends on the particular spreadsheet, but it should be at least as wide, and longer than the anticipated recordset.
        Dim rng As Excel.Range
    Set rng = objResultsSheet.Range("A2:F45")

' ****EXPORT CODE GOES HERE ****

' Save and close spreadsheet
        objXLBook.Save
    objXLBook.Close       

    ' Lastly, I’ll destroy all the objects I created.
    Set db = Nothing
    Set rs = Nothing
    Set objResultsSheet = Nothing
    Set objXLBook = Nothing
    Set objXLApp = Nothing

So far, I haven’t actually done anything useful. I’ve only created and destroyed a bunch of objects. The real work happens in the EXPORT CODE GOES HERE section. The code will vary depending on the method you use and the whether I want to over-write the data or append the data to the end of the existing data.

Do…While Method

I’ll start with the Do…While method.  This method “pushes” the data from Access into Excel. It does this by opening and walking through a recordset in Access and setting specified cells in the Excel sheet.

Overwrite Data
(Enter the following into the  ****EXPORT CODE GOES HERE ****)

'  **************************************
'  Set beginning row and column numbers
    RowVal = 2
    ColVal = 1

'  Delete any existing data
    rng.ClearContents

'  Write data from Access query to Spreadsheet
    Do While Not rs.EOF
        objResultsSheet.Range(Cells(RowVal, ColVal), _
            Cells(RowVal, ColVal)) = rs!phys_id
        objResultsSheet.Range(Cells(RowVal, ColVal + 1), _
            Cells(RowVal, ColVal + 1)) = rs!spec_code
        objResultsSheet.Range(Cells(RowVal, ColVal + 2), _
            Cells(RowVal, ColVal + 2)) = rs!Export_Date
        objResultsSheet.Range(Cells(RowVal, ColVal + 3), _
            Cells(RowVal, ColVal + 3)) = rs!Drug1
        objResultsSheet.Range(Cells(RowVal, ColVal + 4), _
            Cells(RowVal, ColVal + 4)) = rs!Drug2
        objResultsSheet.Range(Cells(RowVal, ColVal + 5), _
            Cells(RowVal, ColVal + 5)) = rs!Drug3
        RowVal = RowVal + 1
        rs.MoveNext
    Loop
'  **************************************

Append Data
(Enter the following into the  ****EXPORT CODE GOES HERE ****)

'  **************************************
'  Set beginning row and column numbers
   RowVal = 2
  ColVal = 1

' Find the last row of data in the Excel sheet
  Do While Not objResultsSheet.Cells(RowVal, ColVal) = Empty
        RowVal = RowVal + 1
    Loop

' Write data from Access query to Spreadsheet
    Do While Not rs.EOF
        objResultsSheet.Range(Cells(RowVal, ColVal), _
            Cells(RowVal, ColVal)) = rs!phys_id
        objResultsSheet.Range(Cells(RowVal, ColVal + 1), _
            Cells(RowVal, ColVal + 1)) = rs!spec_code
        objResultsSheet.Range(Cells(RowVal, ColVal + 2), _
            Cells(RowVal, ColVal + 2)) = rs!Export_Date
        objResultsSheet.Range(Cells(RowVal, ColVal + 3), _
            Cells(RowVal, ColVal + 3)) = rs!Drug1
        objResultsSheet.Range(Cells(RowVal, ColVal + 4), _
            Cells(RowVal, ColVal + 4)) = rs!Drug2
        objResultsSheet.Range(Cells(RowVal, ColVal + 5), _
            Cells(RowVal, ColVal + 5)) = rs!Drug3
        RowVal = RowVal + 1
        rs.MoveNext
   Loop
'  **************************************

CopyFromRecordSet Method

The CopyFromRecordSet method is not an Access function.  It’s actually a method built into Excel. However, because I’m using Office Automation, I can use this Excel method to pull data from Access to Excel.  Using this method, you don’t need to worry about row position or column position.

Recall that above, I defined a recordset object based on the query “TheDataQuery”,
    Set rs = db.OpenRecordset("TheDataQuery")
and I also created a range object
    Set rng = objResultsSheet.Range("A2:F45")

The CopyFromRecordSet method allows me to drop the defined recordset (rs) into the defined range (rng).  It requires an argument for maximum rows and columns (45 and 10 below). I usually just define an area bigger than I could possibly need.

Overwrite Data
(Enter the following into the  ****EXPORT CODE GOES HERE ****)

'  **************************************
'  Set beginning row and column numbers
    RowVal = 45
    ColVal = 10

' Delete any existing data
    rng.ClearContents
   
' Write data from Recordset to Spreadsheet range
    Call rng.CopyFromRecordset(rs, RowVal, ColVal)
'  **************************************

Append Data
(Enter the following into the  ****EXPORT CODE GOES HERE ****)

'  **************************************
'  Set beginning row and column numbers
    RowVal = 1
    ColVal = 6
   
' Find the last row of data
    Do While Not objResultsSheet.Cells(RowVal, ColVal) = Empty
        RowVal = RowVal + 1
    Loop
       
' Create a "Range" object
    Set rng = objResultsSheet.Range(Cells(RowVal, 1), Cells(RowVal, 1))
   
' Write data from Recordset to Spreadsheet range
    Call rng.CopyFromRecordset(rs, RowVal, ColVal)
'  **************************************

You can find this code all pulled together in a sample database from my website:

Download Sample:

ExportToExcel_OfficeAutomation

What’s the Difference Between Early Binding and Late Binding?

Some time ago, I ran into this question on the internet:

Question:

This is something I've never really figured out about Office Automation. These all seem to be equivalent. Is there a preferred version?

Dim objXLApp As Excel.Application
Dim objXLBook As Excel.Workbook
Set objXLApp = New Excel.Application
Set objXLBook = objXLApp.Workbooks.Open("C:\MyFile.xls")

------

Dim objXLApp As Object
Dim objXLBook As Object
Set objXLApp = New Excel.Application
Set objXLBook = objXLApp.Workbooks.Open("C:\MyFile.xls")

------

Dim objXLApp As Excel.Application
Dim objXLBook As Excel.Workbook
Set objXLApp = CreateObject("Excel.Application")
Set objXLBook = objXLApp.Workbooks.Open("C:\MyFile.xls")

-----

Dim objXLApp As Object
Dim objXLBook As Object
Set objXLApp = CreateObject("Excel.Application")
Set objXLBook = objXLApp.Workbooks.Open("C:\MyFile.xls")

------
The only difference I can find is the last one does not require a Reference to Excel set. Any advantage to or against this?

Answer:

The difference is between what's called Early Binding and Late Binding.

Early binding gives you faster access at runtime to an object's methods and properties and a smaller executable. This is because early binding lets the compiler "hard code" in the links between the app and the object. Early binding also ties you to whatever object is specified at design time because, under the hood, early binding uses the object unique identifier to flag all references. To use early binding you must instantiate the object with the New keyword. At runtime, New finds the object in the Windows registry using a direct access based on the object's unique identifier.

Late binding gives you slower access at runtime and a larger executable because code to search for the object's methods and properties that you ask for must be searched for at runtime. Late binding allows you to load a variety of different objects provided only that the object has the right method names (with the right parameters). To use late binding you must instantiate your object with CreateObject, which takes longer because the code performs a lookup in the registry using the object's ProgId.

To get IntelliSense support at design time you must declare your variable as a specific datatype (i.e. not "Object"). To use IntelliSense you must also add a reference to the object's library which is where the definition of the object's datatype is held. However, you can still use either early or late binding by using either New or CreateObject (respectively) to instantiate the object.

So, the first code sample is an example of Early Binding with IntelliSense because it instantiates the object with the New keyword and declares the variable with its datatype.

The second code sample is an example of Early Binding with without IntelliSense because it instantiates the object with the New keyword, declares the variable as "Object". This is probably the least useful because it requires a reference set but still doesn't give IntelliSense.

The third sample is an example of Late Binding with IntelliSense because it does not use the New keyword to instantiate the object, but it does declare the variable with the datatype.

The last sample shows Late Binding without IntelliSense.

Traditionally, Access has been notoriously bad at resolving references at runtime when a new version of a library was installed on the computer or when the Access application was moved to a different computer.

To avoid this problem, I've made it a practice to use CreateObject to instantiate the object because it allowed me to avoid having to add a reference to my Access project. However, to avoid giving up IntelliSense, I'll add a reference to the object's library at design time and declare their variables with a specific datatype. Then, when it was time to release the application, I'll remove the reference to the library and change the declarations to use "Object" rather than a specific datatype.

In terms of the above samples, I'll design using sample 3 and convert it to sample 4 when I put it into production.

Bug Report: Visual Basic Editor Menus Missing in Microsoft Office in Windows 10

I recently spent several frustrating hours trying to discover why my Visual Basic Editor (VBE) lost all of its menus and toolbars in Microsoft Access.  The same happens in the VBE of all Office products.

I was using a Surface Pro 3 upgraded to Windows 10 and with Office 2013. I’ve used the VBE many times with this set up, and I could not see what had changed.

Googling brought up several similar issues for Word and Excel VBE, but those involved corrupt registry keys.  I tried modifying the registry. I tried Repairing my Office install.  I tried uninstalling and reinstalling Office.  Nothing worked.

Another thing I noticed was that I could not Restore the VBA window.  I could Minimize and Maximize but not Restore.  And then I discovered that I couldn’t Restore ANY windows. This made me realize (finally) that I was in the Windows 10 new Tablet Mode.  Tablet mode doesn’t allow Restored windows.

I set my Surface to Desktop Mode and all the VBE menus and toolbars appeared as normal.

I’ve reported this to Microsoft and they can reproduce the problem. I’ve been assured that a fix will be forthcoming since the bug affects so many products.

Interestingly, I use Open Live Writer to edit these blogs.  It used to be a Microsoft product, but they released it to Open Source.  I had the same issue with the Ribbon not appearing when I was in Tablet Mode, so the problem may be more wide-spread than the VBE of Office.

As far as I know, it affects all versions of Office, but only those Window 10 installs that have a touch screen. 

I’d love to hear if anyone out there finds an exception, or if any other programs are affected by Tablet Mode.

Featured Sample: Delete Columns in Excel that are Empty

Delete Columns in Excel that are Empty

By Crystal Long


Use this code to delete columns that are completely empty by sending a parameter of one (1) for the first data row.  The default is currently to assume there is a row of labels to skip in determining if there is data in the column.


This is also perfect to run from Access after writing the results of a query where you only want to see columns with information.


Download at: http://www.rogersaccesslibrary.com/forum/long-crystal_forum71.html