How to set any Sheet as Acitve Sheet in Excel and copy the data from other file

Problem:
Copy data from 3 diffrent .xls files to one .xls file on diffrent sheet

i have already created 3 xls files which having only one sheet in it.
now i need to merge or copy the the each 3 files on new .xls files with diffrent sheet. for example file 1 data will be copied on sheet 1 of new file and so on

these 3 files can have diffrent range set so i can't write the code which only depends on the range and copy it.

is there any way to do it directly
Exlapp := TExcelApplication.Create(Self);
  Exlapp.Connect;
  Exlapp.SheetsInNewWorkbook[0]:=10;
  Exlapp.Workbooks.Add(NULL, 0);
  Exlapp.Workbooks[1].SaveAs('D:\myFile.xls',EmptyParam,EmptyParam,EmptyParam,
                            EmptyParam,EmptyParam,0,EmptyParam,
                            EmptyParam,EmptyParam,EmptyParam,0);
 Exlapp.Visible[0]:=True;
 Exlapp.disConnect;
 ShowMessage('File Created Successfully');

Open in new window

SecureMetersAsked:
Who is Participating?
 
ahalyaConnect With a Mentor Commented:
Should be

      xlsheet2.Select;
      xlsheet1.Copy(, xlsheet2);

0
 
ahalyaCommented:
The following code snippet shows how to make a copy of the current sheet, right after it.

//Select the first worksheet in the first workbook
xlsheet := xlapp.workBooks[1].Worksheets[1];
 
//Select the entire sheet
xlsheet.Select;
 
//Now Make a copy of it, and place it after the current sheet
xlsheet.Copy(, xlsheet); {copy after current sheet}

Open in new window

0
 
SecureMetersAuthor Commented:
i want to copy this excel sheet in the another workbook.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
ahalyaCommented:
Don't have Delphi with me now to try this.  But, it might work.

xlsheet1 := xlapp.workBooks[1].Worksheets[1];
xlsheet2 := xlapp.workBooks[2].Worksheets[1];
 
xlsheet1.Select;
xlsheet1.Copy(, xlsheet2);

Open in new window

0
 
SecureMetersAuthor Commented:
it is not working
i have tried and it says that [Pascal Error] Unit1.pas(331): E2010 Incompatible types: 'TExcelWorksheet' and 'IDispatch' on statment
xlsheet1 := xlapp.workBooks[1].Worksheets[1];
xlsheet2 := xlapp.workBooks[2].Worksheets[1];
and getting below message on this statement XlWorkBook1 :=xlapp.workbooks.open(OpenDialog1.FileName,EmptyParam,EmptyParam,EmptyParam
,EmptyParam,EmptyParam,EmptyParam,EmptyParam

i have done it in other way but new sheet does not have the same colour as of source sheet
can you give me idea about this?
,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,0);
[Pascal Error] Unit1.pas(329): E2010 Incompatible types: 'TExcelWorkbook' and '_Workbook'
0
 
ahalyaCommented:
Try the following
uses ComObj;
 
procedure TForm1.btn1Click(Sender: TObject);
 
var xlApp, xlBook1, xlBook2,  xlsheet1, xlsheet2 : Variant;
 
begin
  xlApp := CreateOleObject('Excel.Application');
  xlapp.visible := true;
  try
    if OpenDialog1.Execute then
      xlBook1 := xlApp.WorkBooks.Add(OpenDialog1.FileName)
    else exit;
 
    if OpenDialog1.Execute then
    begin;
      xlBook2 := xlApp.WorkBooks.Add(OpenDialog1.FileName);
      xlsheet1 := xlapp.workBooks[1].Worksheets[1];
      xlsheet2 := xlapp.workBooks[2].Worksheets[1];
 
      xlsheet1.Select;
      xlsheet1.Copy(, xlsheet2);
    end;
  finally
    xlApp := UnAssigned;
  end;
end;

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.