Sheets with a space in their description - Add Name

[Dmeade]

Hi

if you have a excel sheet that has changed the normal Sheet1 to something that has a space e.g.
"Space Test"
and then try and assign a name to the "Space Test" Sheet im getting the error below

E4016: Uknown sheet name "Test"

sorry for all these questions about Names - we were using Delphi XE2 with XLS RW 4 and are now using Delphi XE5 with XLS WR 5
we have been creating templates in Excel and loading them in and exporting based off the templates
some stuff that was working in XLSRW V4 isnt in XLSWR V5

i was just trying to set up a scenario to show a Name issue - that was working in the old XLS RW 4 version

Code: Select all

unit XLSTestRun3;

interface
uses Xc12Utils5, XLSReadWriteII5, XLSNames5, SysUtils, typinfo, Vcl.Dialogs;

procedure XLSAddNameWithSpace( XLSVersion : TExcelVersion; sFilename : String ) ;

implementation

procedure XLSAddNameWithSpace( XLSVersion : TExcelVersion; sFilename : String ) ;
var Sheet1Name, Sheet2name, Sheet3Name : String;
    XLSReadWriteII52: TXLSReadWriteII5;
    Nme : TXLSName;
begin
  XLSReadWriteII52 := TXLSReadWriteII5.Create(nil);
  XLSReadWriteII52.Version := XLSVersion;
  XLSReadWriteII52.Filename := sFilename;

  try
    XLSReadWriteII52.Write;
    assert( FileExists( XLSReadWriteII52.Filename ) , 'File not Created. "'+ XLSReadWriteII52.Filename +'"');

    XLSReadWriteII52.Add;

    Sheet1Name := 'Normal';
    Sheet2Name := 'Space Test';

    XLSReadWriteII52.Sheets[0].Name := Sheet1Name;
    XLSReadWriteII52.Write;
    XLSReadWriteII52.Sheets[1].Name := Sheet2Name;
    XLSReadWriteII52.Write;

    XLSReadWriteII52.names.Add( StringReplace( Sheet1Name, ' ', '', [rfReplaceAll, rfIgnoreCase] ) + '_NAME', Sheet1Name+'!$A$1:$A$5' );
    XLSReadWriteII52.Write;
    XLSReadWriteII52.names.Add( StringReplace( Sheet2Name, ' ', '', [rfReplaceAll, rfIgnoreCase] ) + '_NAME', Sheet2Name+'!$A$1:$A$5' );
    XLSReadWriteII52.Write;

  finally
    XLSReadWriteII52.Free;
  end;
end;

end.

Code: Select all

procedure TForm7.btn5Click(Sender: TObject);
var sExportLocation : String;
begin

  sExportLocation := IncludeTrailingPathDelimiter( ExtractFilePath(Application.exename) ) +
                                          'XLSTestV97' + FormatDateTime('yymmdd_hhnnss_zzz', now) + '.XLS';
  XLSAddNameWithSpace(xvExcel97, sExportLocation );

  sExportLocation := IncludeTrailingPathDelimiter( ExtractFilePath(Application.exename) ) +
                                          'XLSTestV2007' + FormatDateTime('yymmdd_hhnnss_zzz', now) + '.XLSX' ;
  XLSAddNameWithSpace(xvExcel2007, sExportLocation);
end;

[larsa]

Hello

Sheet names with spaces must be within single quotes, example: 'My sheet'!A1

[Dmeade]

yep that works

Code: Select all

unit XLSTestRun3;

interface
uses Xc12Utils5, XLSReadWriteII5, XLSNames5, SysUtils, typinfo, Vcl.Dialogs;

procedure XLSAddNameWithSpace( XLSVersion : TExcelVersion; sFilename : String ) ;

implementation

procedure XLSAddNameWithSpace( XLSVersion : TExcelVersion; sFilename : String ) ;
var Sheet1Name, Sheet2name, Sheet3Name : String;
    XLSReadWriteII52: TXLSReadWriteII5;
    Nme : TXLSName;
begin
  XLSReadWriteII52 := TXLSReadWriteII5.Create(nil);
  XLSReadWriteII52.Version := XLSVersion;
  XLSReadWriteII52.Filename := sFilename;

  try
    XLSReadWriteII52.Write;
    assert( FileExists( XLSReadWriteII52.Filename ) , 'File not Created. "'+ XLSReadWriteII52.Filename +'"');

    XLSReadWriteII52.Add;

    Sheet1Name := 'Normal';
    Sheet2Name := 'Space Test' ;

    XLSReadWriteII52.Sheets[0].Name := Sheet1Name;
    XLSReadWriteII52.Write;
    XLSReadWriteII52.Sheets[1].Name := Sheet2Name;
    XLSReadWriteII52.Write;

    XLSReadWriteII52.names.Add( StringReplace( Sheet1Name, ' ', '', [rfReplaceAll, rfIgnoreCase] ) + '_NAME', ''''+Sheet1Name+ ''''+'!$A$1:$A$5' );
    XLSReadWriteII52.Write;
    XLSReadWriteII52.names.Add( StringReplace( Sheet2Name, ' ', '', [rfReplaceAll, rfIgnoreCase] ) + '_NAME', ''''+ Sheet2Name + ''''+'!$A$1:$A$5' );
    XLSReadWriteII52.Write;

  finally
    XLSReadWriteII52.Free;
  end;
end;

end.