Wednesday, January 1, 2020

* Index to Access 101 *

Access Basics

Compacting Databases

Domain Functions Demystified

Date Stuff

 Database Design Basics

What’s Wrong With Repeated Columns?

Normalizing Repeating Columns

 

Queries

Select Queries Series:

Data Definition Language (DDL) Queries:

Top Queries Revealed:

Count Distinct In Access Series:

Miscellaneous

Reports

VBA/Programming

Automation

Web Databases

  • The Application

Wednesday, May 25, 2016

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.

image 
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.

image
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.

image
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.

image
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.

image
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.

image
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)

Monday, May 23, 2016

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:

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.

    image

    Once the VBE is open, choose Tools > References

    image

    The Reference List will look like this:

    image

    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

    • Monday, May 16, 2016

      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.

      Tuesday, May 10, 2016

      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.

      Monday, May 9, 2016

      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

      image

      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.

      image image

      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.

      image image

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

      image

      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.

      image

      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.

      image

      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.

      image

      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

      image

      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.

      image

      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.

      Thursday, May 5, 2016

      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