Forum Discussion
Post Patronxlnm filter duplicate "error" when refreshing data
campelliann
Did you figure out which specific excel file caused that error, just test out.Once you have find the problematic excel, copy all the cells and paste them in an empty new sheet then work with the new sheet, remove the old one.
Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
campelliann
Did you figure out which specific excel file caused that error, just test out.
Once you have find the problematic excel, copy all the cells and paste them in an empty new sheet then work with the new sheet, remove the old one.
Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I got a better one.
When you uploading multiple files in a document, first step is to add a column with "Excel.Workbook".
Then normally when we expand the custom column, what we need is the "data" item.
This time select "name", "data", "kind" all together. You will see the rows are increased with these hidden tables named "_xlmn._FilterDatabase", and in the "kind" column, these rows are tagged with "DefinedName".
All you need to do is filter the "kind" column, leave "sheet" along, and then delete all the columns except "data". Don't worry even delete the "kind" column will not cancel the filter. Now, you get a neat source of data without duplication.
The following steps are as usual. Problem solved for good.
thank you!
Create a new copy is the faster solution for now.
Or you can write a vba context to show all table names in the file, and then delete the "xlmn filterdatabase".
But there is no guarentee that after you have done any modification to the file, a new filterdatabase won't be created.
Besides, process the files one by one is so inefficient.
I'm still looking for any way that can prevent the filterdatabase being produced, or to delete dbs in multiple files at one time.
Pls let me know if you find one. I'll do the same.
