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.



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.



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


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.




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




Figure 4 - Editing a macro


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


Sub Macro1()


' Macro1 Macro



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


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


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