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
' Macro1 Macro
ActiveSheet.Range("$A$1:$F$301").AutoFilter Field:=3, Criteria1:= _
ActiveSheet.Range("$A$1:$F$301").AutoFilter Field:=5, Criteria1:=">500", _
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 :)
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