Could the Name.Area be changed by code

[bennylan]

Hi,
I have a excel file, there is a Name "resultset1" defined, then i use following code to update the Area of the Name, but it seems no changed. Can you help me on this? thanks!

Code: Select all

            xlsName := xlsApp.Names.Find('resultset' + inttostr(L+1));
            if xlsName <> nil then
            begin
              xlsName.Area.SheetIndex := L;
              xlsName.Area.Row1 := J;
              xlsName.Area.Row2 := row-1;
              xlsName.Area.Col1 := 0;
              xlsName.Area.Col2 := pgPreview.ColCount-2;
              xlsName.Update;
            end
            else
              xlsApp.Names.Add('resultset' + inttostr(L+1), xlsSheet.Name, 0, J, pgPreview.ColCount-2, row-1);

Benny

[larsa]

Hello

Use the Definition property instead. Example:

Code: Select all

var
  xlsName: TXLSName;
begin
  xlsName := FXSS.XLS.Names.Find('MyName');
  if xlsName <> nil then begin
    xlsName.Definition := 'Sheet2!$E$1:$f$8';
  end;

[bennylan]

Thank you. It works well for excel 2007, but for excel 2003, the referenced sheet index of the Name is not matched unless apply the workaround below.

Code: Select all

            xlsName := xlsApp.Names.Find('name_' + sn);
            if xlsName <> nil then
            begin
                xlsName.Definition := '''' + xlsSheet.Name + '''!$A$'+inttostr(j+1)+':$' + GetColumnName(pgPreview.ColCount-1) + '$'+inttostr(row);

                // Workaround. XLS problem here?
                if xlsApp.Version = xvExcel97 then
                begin
                  if xlsSheet.Index = 0 then
                    xlsName.Area.SheetIndex := 1
                  else if xlsSheet.Index = 4 then
                    xlsName.Area.SheetIndex := 3;
                end;
            end;