We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Excel Sheet color

SecureMeters
SecureMeters asked
on
Medium Priority
677 Views
Last Modified: 2012-05-06
i have to copy the excel sheet from one excel work book to another workbook at run time using the delphi code.
source excel sheet is formated with some colors
i have done it sucessfully.
but when i newly created work sheet entire work sheet is painted with Green color

can some body give me idea how to retain the same color format in the new work sheet?
Comment
Watch Question

Commented:
Can you show the code you're using to perfom the sheet copy?

Author

Commented:
procedure TForm1.Button8Click(Sender: TObject);
var
wbSource : _WorkBook;
wbDestination: _WorkBook;
fileLocationSource : String;
fileLocationDestination : String;
FileAvail:boolean;
Sheets: Variant;
begin
try
Exlapp:=TExcelApplication.Create(nil);

OpenDialog1.Execute;
fileLocationSource := OpenDialog1.FileName;
fileLocationDestination := 'D:\111.xls';
exlapp.Visible[0]:=true;
//opening both the source and the destination workbooks
 Exlapp.Workbooks.Open(fileLocationSource,EmptyParam,EmptyParam,EmptyParam
                        ,EmptyParam,EmptyParam,EmptyParam,EmptyParam
                        ,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,0);
 wbSource := (exlapp.ActiveWorkbook);
 wbDestination := Exlapp.Workbooks.add(Null,0);
 wbDestination := (exlapp.ActiveWorkbook);
 FileAvail:=FileExists(fileLocationDestination);
 if not FileExists(fileLocationDestination) then
 begin
  wbDestination.SaveAs(fileLocationDestination,EmptyParam,EmptyParam,EmptyParam,
                      EmptyParam,EmptyParam,0,
                      EmptyParam,EmptyParam,EmptyParam,
                      EmptyParam,0);

 end;
 Exlapp.Workbooks.Open(fileLocationDestination,EmptyParam,EmptyParam,EmptyParam
                        ,EmptyParam,EmptyParam,EmptyParam,EmptyParam
                        ,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,0);
 wbDestination := (exlapp.ActiveWorkbook);
 wbSource.Worksheets.Copy(null,wbDestination.Worksheets[wbDestination.Worksheets.count],0);
  if not FileAvail then
  begin
   Sheets := Exlapp.Sheets;
   while Sheets.count<>1 do
    Sheets.item[1].delete;
  end;
  OleVariant(Exlapp.ActiveWorkbook.Worksheets[1]).Name :='aaa';
  wbDestination.Save(0);
  wbDestination.Close(emptyparam,emptyparam,emptyparam,0);
  wbsource.Close(emptyparam,emptyparam,emptyparam,0);

finally
  exlapp.Free;
 end;
end;
Commented:
That works fine for me (with a couple of minor amendments). I certainly don't see the colour issues that you're having. I'm using Excel 2003/Delphi6. Sorry.
var
wbSource : _WorkBook;
wbDestination: _WorkBook;
fileLocationSource : String;
fileLocationDestination : String;
FileAvail:boolean;
Sheets: Variant;
Exlapp:         TExcelApplication;
begin
  if not OpenDialog1.Execute then
    Exit;
  Exlapp := TExcelApplication.Create(nil);
  try
    fileLocationSource := OpenDialog1.FileName;
    fileLocationDestination := 'C:\111.xls';
    exlapp.Visible[0]:=true;
    //opening both the source and the destination workbooks
    wbSource := Exlapp.Workbooks.Open(fileLocationSource,EmptyParam,EmptyParam,EmptyParam
                            ,EmptyParam,EmptyParam,EmptyParam,EmptyParam
                            ,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,0);
//    wbSource := (exlapp.ActiveWorkbook);
    wbDestination := Exlapp.Workbooks.add(Null,0);
//    wbDestination := (exlapp.ActiveWorkbook);
    FileAvail:=FileExists(fileLocationDestination);
    if not FileExists(fileLocationDestination) then
    begin
      wbDestination.SaveAs(fileLocationDestination,EmptyParam,EmptyParam,EmptyParam,
                          EmptyParam,EmptyParam,0,
                          EmptyParam,EmptyParam,EmptyParam,
                          EmptyParam,0);
 
    end;
    wbDestination := Exlapp.Workbooks.Open(fileLocationDestination,EmptyParam,EmptyParam,EmptyParam
                            ,EmptyParam,EmptyParam,EmptyParam,EmptyParam
                            ,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,0);
    //wbDestination := (exlapp.ActiveWorkbook);
    wbSource.Worksheets.Copy(null,wbDestination.Worksheets[wbDestination.Worksheets.count],0);
    {if not FileAvail then
    begin
      Sheets := Exlapp.Sheets;
      while Sheets.count<>1 do
        Sheets.item[1].delete;
    end;
    OleVariant(Exlapp.ActiveWorkbook.Worksheets[1]).Name :='aaa';}
    wbDestination.Save(0);
    wbDestination.Close(emptyparam,emptyparam,emptyparam,0);
    wbsource.Close(emptyparam,emptyparam,emptyparam,0);
  finally
    exlapp.Free;
  end;
end;

Open in new window

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.