• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 860
  • Last Modified:

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

0
SecureMeters
Asked:
SecureMeters
  • 4
  • 2
1 Solution
 
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
 
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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
 
ahalyaCommented:
Should be

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

0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now