exp vg
asked on
Excel - Saved Filters
I have an excel workbook, and would like to pre-define filter specifications using the filter drop/down menus, and then load in data that would automatically filter based on this, without using VBA.
I did a test myself, and did not get this to work, but wanted to see if this is possible.
Basically, I will be filtering repeated datasets using the same filter specifications, and would rather have these filters already defined.
Thank you.
I did a test myself, and did not get this to work, but wanted to see if this is possible.
Basically, I will be filtering repeated datasets using the same filter specifications, and would rather have these filters already defined.
Thank you.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Glad to help. If you want to use code post back
Hello exp vg,
The import of data from external text files datasets differ significantly from import of data from other sources and, specifically, because Excel does not create a so-called "Excel Table" for the imported data range.
Your are then perfectly right, any refresh of a text file connection will cause the filters to be reset.
However, a refresh on other data connections (such as MS-SQL, MSQ-Access or MS-Excel as illustrated further) does not causse this issue provided the option "preserve column sort/filter/layout" has been selected on the data range properties ("Data" menu/ribbon, "Properties" - this option is unfortunately grayed out for text files connections).
In other words, if you can manage to have your datasets as Excel files, you may be able to achieve what you want.
There is still one issue with these other sources:
Selecting a different dataset with text files connection is easy because the system will prompt you for the file whenever you refresh (and assuming that the "Prompt for file name on refresh" data range property is set which is the default).
This is not (or usually not) the case with the other sources.
To get around this (I am now assuming you can get your datasets in Excel format), you can have an external command that will copy any of your dataset you want to process to a work dataset with a known constant name and use this one for import.
To process another dataset, just copy it to the work dataset with the known constant name and refresh your data import.
This is all illustrated in the attached files (created from C:\Test and as some absolute path are involved, please download there as well):
- A Master.xlsx Excel workbook with
. Sheet1 importing the Text datasets illustrating the issue, if you refresh, you can select the "Data-A.txt" or "Data-B.txt"
. Sheet2 similar but with a Excel import from a unique Data.xlsx file (you'll immediately see that the range is an Excel table)
+ You can then use the "Select-Data-A.cmd" command to copy "Data-A.xlsx" on "Data.xlsx" (or "Select-Data-B.cmd" for "Data-B.xlsx")
+ After reresh, you'll see either dataset A or B with sort rules, filters preserved
It is far from perfect and having a Excel Table import of Text files would greatly help but I have not seen this so far
(although in the past, Text files could be imported through the Excel ODBC driver - this disappeared)
Hope it helps,
best regards,
Pierre
Master.xlsx
Data-A.txt
Data-B.txt
Data.xlsx
Data-A.xlsx
Data-B.xlsx
Select-Data-A.cmd
Select-Data-B.cmd
The import of data from external text files datasets differ significantly from import of data from other sources and, specifically, because Excel does not create a so-called "Excel Table" for the imported data range.
Your are then perfectly right, any refresh of a text file connection will cause the filters to be reset.
However, a refresh on other data connections (such as MS-SQL, MSQ-Access or MS-Excel as illustrated further) does not causse this issue provided the option "preserve column sort/filter/layout" has been selected on the data range properties ("Data" menu/ribbon, "Properties" - this option is unfortunately grayed out for text files connections).
In other words, if you can manage to have your datasets as Excel files, you may be able to achieve what you want.
There is still one issue with these other sources:
Selecting a different dataset with text files connection is easy because the system will prompt you for the file whenever you refresh (and assuming that the "Prompt for file name on refresh" data range property is set which is the default).
This is not (or usually not) the case with the other sources.
To get around this (I am now assuming you can get your datasets in Excel format), you can have an external command that will copy any of your dataset you want to process to a work dataset with a known constant name and use this one for import.
To process another dataset, just copy it to the work dataset with the known constant name and refresh your data import.
This is all illustrated in the attached files (created from C:\Test and as some absolute path are involved, please download there as well):
- A Master.xlsx Excel workbook with
. Sheet1 importing the Text datasets illustrating the issue, if you refresh, you can select the "Data-A.txt" or "Data-B.txt"
. Sheet2 similar but with a Excel import from a unique Data.xlsx file (you'll immediately see that the range is an Excel table)
+ You can then use the "Select-Data-A.cmd" command to copy "Data-A.xlsx" on "Data.xlsx" (or "Select-Data-B.cmd" for "Data-B.xlsx")
+ After reresh, you'll see either dataset A or B with sort rules, filters preserved
It is far from perfect and having a Excel Table import of Text files would greatly help but I have not seen this so far
(although in the past, Text files could be imported through the Excel ODBC driver - this disappeared)
Hope it helps,
best regards,
Pierre
Master.xlsx
Data-A.txt
Data-B.txt
Data.xlsx
Data-A.xlsx
Data-B.xlsx
Select-Data-A.cmd
Select-Data-B.cmd
It occurs to me that setting up a template file for Advanced Filter instead of AutoFilter5 might be an option.
ASKER