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

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

Medium Priority
1,282 Views
Last Modified: 2013-12-02
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

Comment
Watch Question

Commented:
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

Author

Commented:
i want to copy this excel sheet in the another workbook.

Commented:
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

Author

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'

Commented:
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

Commented:
Should be

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

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.