Online Data Doesn't have to be Dumb - Automatic Excel Filters

<< Click to Display Table of Contents >>

Navigation:  Office365 and PowerApps >

Online Data Doesn't have to be Dumb - Automatic Excel Filters

by Garry Robinson

 

Before an Excel File is opened in Dropbox or OneDrive it looks like Figure 1.

 

excelFilter1

Figure 1 - A normal read-only view of a spreadsheet in Dropbox

 

 

After it is opened it will look very similar to Figure 1 if the data Filters are turned off. That clearly makes your data a little dumb. But if Filters are turned on, then your spreadsheet becomes much more useful as shown by the powerful dropdown arrows illustrated  in Figure 2. We should be providing our users with these if we want them to explore our data.

 

excelFilter2

Figure 2 - A Spreadsheet that has had filters turned on before the user opens it

 

 

Making a Spreadsheet (Data Dump) that Has Filters Turned On

 

In Access you can use VBA or a Macro to export a table or query to a spreadsheet sheet. The VBA for this will look similar to this. FileDir is the folder where you want to place the file. This would be a local OneDrive or Dropbox folder.

 

    DoCmd.OutputTo acOutputTable, "zWorld_Demo", "ExcelWorkbook(*.xlsx)", FileDir & "Exported.xlsx", False

 

Now using Excel Automation as described in Doug Steele's article, you can open the Exported.xlsx spreadsheet after the export is complete. Now you need to move to one of the cells where the data is and set the Filter using the following code

    Excel.Application.Selection.AutoFilter

If you want to actually turn on a filter (as in Figure 2), you will do more with the AutoFilter code similar to what follows

   Excel.Application.Selection.AutoFilter Field:=5, Criteria1:=">1000", Operator:=xlAnd

 

And then you will Save and Close your spreadsheet using the Automation.

 

Dear Reader - You Got To Work Filters Out For Yourself

One of the great things about Excel automation from Access is that you can record macro's of your steps in Excel and then look at the resulting code. Usually it is very specific and verbose and you need to make it simpler to add it to your solutions. But the way you collect the sample code is as follows

 

On the View tab in Excel, choose Macro's then Record Macro to start. Do all your steps and then press Stop Recording as in Figure 3.  Then you will look at your code in View Macros (also Figure 3) and then Edit as in Figure 4.

 

 

excelFilter3

Figure 3 - This is where the Start Recording and Stop Recording buttons are in Excel

 

 

excelFilter4

Figure 4 - Editing a macro

 

The code that is generated by this recording session is as follows

 

Sub Macro1()

'

' Macro1 Macro

'

    Selection.AutoFilter

    ActiveSheet.Range("$A$1:$F$301").AutoFilter Field:=3, Criteria1:= _

        "Australia"

    ActiveSheet.Range("$A$1:$F$301").AutoFilter Field:=5, Criteria1:=">500", _

        Operator:=xlAnd

End Sub

 

Don't forget at the end of the recording session, copy the code into notepad and do not save the Excel file. If you don't, it will turn from being a online ready Excel file into an XLSM file that only will be editable on your desktop. Read about that in Excel Online - File types and other basics

 

Its Time To Do Some Homework

 

Now that Online Excel is starting to pop up more and more, its important that we learn all the little tricks that makes those Excel files smarter. Using the Excel Autofilter in Automation is one way that you can value add your Excel exports.

And because I haven't actually put all the code in front of you, its time for you to go and figure this one out for yourself.  Enjoy the challenge :)

 

Dropbox Tip

To turn off the bell noise caused by file updates in Dropbox on Windows 10, do the following.  Press Windows key then Type Settings then Type Notifications then Type find Dropbox sound and turn it off