Adding autofilter to sheets with names with spaces

[DrTob]

The following Code crashes with Exception 'E4016: Uknown sheet name "Name"'.

Code: Select all

    with TXLSReadWriteII5.Create(self) do begin
        Sheets[0].Name := 'Sheet Name';
        Sheets[0].Autofilter.Add(0, 1, 1, 1);
    end;

[larsa]

Hello

Of course there will be an error. There is no sheet named "Sheet Name" by default. The default sheet name is "Sheet1".

[DrTob]

I know that the default name is "Sheet1", but I set the name of the first sheet to "Sheet Name". Then I try to add an autofilter.

[DrTob]

Same error with names containing a "-" (minus) and "(" / ")" (parentheses) and "!"

[larsa]

Hello

Sorry, I was thinking of something else...

The problem is that sheet names with spaces and other separator characters must be within single quotes when used in formulas. This is not the case now when adding autofilters. Will fix this in the nest update.
In the mean time, you can use this code to add an autofilter:

Code: Select all

var
  N: TXLSName;
  Col1,Row1,
  Col2,Row2: integer;
  SheetIndex: integer;
begin
  Col1 := 0;
  Row1 := 0;
  Col2 := 0;
  Row2 := 0;
  SheetIndex := 0;

  XLS[0].Autofilter.Clear;
  XLS[0].Autofilter.Ref := SetCellArea(Col1,Row1,Col2,Row2);

  N := TXLSName(XLS.Names.FindBuiltIn(bnFilterDatabase,SheetIndex));
  if N = Nil then
    N := TXLSName(XLS.Names.Add(bnFilterDatabase,SheetIndex));
  N.Definition := '''' + XLS[0].Name + '''!' + AreaToRefStr(Col1,Row1,Col2,Row2,True,True,True,True);
  N.Hidden := True;