使用数组（错误）的AutoFilter标准 - 太大的字符串？ [英] AutoFilter Criteria Using Array (Error) - Too Large String?

问题描述

更新：通过我发现的一些额外测试：
1）255个字符 似乎是断点（字符限制）。使用字符长度为245的数组设置过滤器工作正常 - 我能够保存并重新打开而没有任何错误。我添加了另一个条件到数组使长度为262，保存文件，然后得到相同的错误。
2）删除记录中的工作表消息是指工作表索引，而不是工作表名称，它确实使用自动过滤器参考了纸张。 结束更新

我的问题 - 我编写了一个代码来设置数据集的 AutoFilter 基于几个切片机中的选定项目。有时当我打开文件时，我会收到错误（转义）： Excel在工作簿中发现不可读的内容。是否要修复文件？然后弹出一个对话框，并说出已删除的记录：从/xl/worksheets/sheet2.xml部分排序。

代码按照设计工作;数据集反映了切片器中选择的任何内容（甚至很多选择）。

我按如下所示设置数组（一个字符串数组），然后使用数组来设置条件：

 如果sCache.Name =Slicer_Test然后
对于ActiveWorkbook.SlicerCaches（sCache.Name）中的每个sItem .SlicerItems 
如果sItem.Selected = True然后
 ReDim保存sArr（0到sCount）
 sArr（sCount）= sItem.Name 
 sCount = sCount + 1 
结束如果
下一个sItem 
 filterRng.AutoFilter Field：= 9，Criteria1：= sArr，Operator：= xlFilterValues 
 ReDim sArr（0到0）
如果
  

我为每个切片器复制上述代码。

我认为问题源自于三个最大的切片器包含27个， 120和322个项目。所以你可以想象，当最大的切片器中的所有项目被选中时，数组的字符串长度超过5K个字符长...像上面提到的那样，代码按照设计工作。 我发现这个主题，其中提到一个字符最大？

我在保存/关闭工作簿之前尝试删除过滤器，但这并不总是可以工作，而且这个文件将被许多其他人使用。所以我想知道如果1）任何人有一个办法解决这个错误的建议，或2）如果可能有办法完成过滤，而不使用非常长的数组...

对此的任何想法将不胜感激！

解决方案

我的同事帮我解决了这个问题。

显然当使用这种语法时：

  Criteria1：= sArr 
  

Excel将数组作为一个长字符串读取，而不是将其视为包含许多字符串元素的数组。

修复是使用 Array（）函数如下：

  Criteria1：= Array（sArr）
  / pre> 
 
 
这似乎阻止了Excel的破坏。
 
Update: Through some additional testing I discovered:
1) 255 Characters does seem to be the breaking point (character limit). Setting the filter with an array with a character length of 245 worked fine -- I was able to save and reopen without any errors. I added another criteria to the array to make the length 262, saved the file, and then got the same error.
2) The sheet in the removed records message refers to the sheet index, not the sheet name, and it does indeed reference the sheet with the autofiltering. End Update

My Issue -- I've written code to set a dataset's AutoFilter based on selected items in several slicers. Sometimes when I open up the file I get the error (paraphrased): Excel found unreadable content in the workbook. Do you want to repair the file? Then a dialog pops up and says Removed Records: Sorting from /xl/worksheets/sheet2.xml part. 

The code works as designed; the dataset reflects whatever is selected in the slicers (even many selections). 

I set the array (a string array) as follows and then use the array to set the criteria:
If sCache.Name = "Slicer_Test" Then
    For Each sItem In ActiveWorkbook.SlicerCaches(sCache.Name).SlicerItems
        If sItem.Selected = True Then
            ReDim Preserve sArr(0 To sCount)
            sArr(sCount) = sItem.Name
            sCount = sCount + 1
        End If
    Next sItem
filterRng.AutoFilter Field:=9, Criteria1:=sArr, Operator:=xlFilterValues
ReDim sArr(0 To 0)
End If
I replicate the above code for each slicer.

Where I think the problem stems from is that the three largest slicers contain 27, 120, and 322 items, respectively. So as you can imagine, when all the items in the largest slicer are selected, the array's string length is over 5K characters long... like I mentioned above, the code works as designed. I found this thread, which mentions a character maximum? 

I've tried removing the filters before saving/closing the workbook, but that doesn't always work, and this file will be used by many other people. So I'm wondering if 1) anyone has a suggestion for a way to workaround this error, or 2) if there might be a way to accomplish the filtering without using a terribly-long array...

Any thoughts on this will be much appreciated! 
解决方案
A co-worker of mine helped me resolve the issue.

Apparently when using this syntax:  
Criteria1:=sArr
Excel reads the array as one long string instead of looking at it as an array that contains many string elements.

The fix is to use the Array() function like so:
Criteria1:=Array(sArr)
This seems to prevent Excel from corrupting.

                        
这篇关于使用数组（错误）的AutoFilter标准 - 太大的字符串？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！