Page 1 of 1
Adding autofilter to sheets with names with spaces
Posted: Tue Sep 22, 2015 4:58 pm
by 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;
Re: Adding autofilter to sheets with names with spaces
Posted: Wed Sep 23, 2015 7:17 am
by larsa
Hello
Of course there will be an error. There is no sheet named "Sheet Name" by default. The default sheet name is "Sheet1".
Re: Adding autofilter to sheets with names with spaces
Posted: Wed Sep 23, 2015 8:53 am
by 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.
Re: Adding autofilter to sheets with names with spaces
Posted: Wed Sep 23, 2015 9:42 am
by DrTob
Same error with names containing a "-" (minus) and "(" / ")" (parentheses) and "!"
Re: Adding autofilter to sheets with names with spaces
Posted: Thu Sep 24, 2015 7:57 am
by 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;