Quick test professional

QTP Tips QTP codes QTP Faqs and more

Copy an excel sheet to another excel

Following is the code to copy the conntents of a sheet in one excel to another excel sheet

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
Set objWorkbook1= objExcel.Workbooks.Open(“C:\Documents and Settings\mohan.kakarla\Desktop\1.xls”)
Set objWorkbook2= objExcel.Workbooks.Open(“C:\Documents and Settings\mohan.kakarla\Desktop\2.xls”)
objWorkbook1.Worksheets(“Sheet1”).UsedRange.Copy
objWorkbook2.Worksheets(“Sheet1”).Range(“A1”).PasteSpecial Paste =xlValues
objWorkbook1.save
objWorkbook2.save
objWorkbook1.close
objWorkbook2.close
set objExcel=nothing

 Author: Mohan Kakarla

Advertisements

June 9, 2008 - Posted by | Excel Automation, Uncategorized | , , ,

25 Comments »

  1. Vineet,
    Please search before posting the comment
    try here

    https://quicktestprofessional.wordpress.com/category/excel-automation/

    Comment by quicktestprofessional | June 18, 2008 | Reply

  2. I want to enter dynamic values in “Range” field like first time the script will copy the Range “B5:Z5” and second time
    “B15:Z15” and so on. So I would like to make the script dynamic. Is there any way can I do it or using loops etc.? please help me out.

    Thanks.

    Comment by Simran | December 11, 2008 | Reply

  3. Dear Mohan,
    The website is really helping good work 🙂
    thanx a lot

    Comment by amrita | June 8, 2009 | Reply

  4. Hi Mohan,

    I am new to QTP.. These articles are nice.. Thanks a lot for providing these kind of help.
    I have one doubt regarding PasteSpecial

    objWorkSheet.Range(“A1”).PasteSpecial Paste = xlValues

    Here what is the exact syntax for PasteSpecial
    or what u mean by “Paste” and “xlValues”
    As per my knowledge it is variables only, Am I right?
    So I can use any other variables also for this. But I didnt get the exact meaning of this usage.. Can u plz explain this?

    Comment by SriDevi | July 22, 2009 | Reply

    • same dought for me also do you got reply for this

      Comment by manoj | January 21, 2014 | Reply

  5. Please tell how to copy from one excel (sheet1) to another one excel2 (sheet2) when excel 2 is already open and i dnt want to close it

    Please reply soon .. its urgent

    Comment by sweta | November 27, 2009 | Reply

    • just comment the create command and add open command and get file and the use copy

      Comment by kumar | October 31, 2013 | Reply

  6. Hi Mohan,

    Thanks for sharing these information. Its really helping a lot of people like me who are learning QTP.

    Keep up the good work.
    Once again..Thank you

    Comment by Manju | March 6, 2010 | Reply

  7. Hello

    i have to append data from multiple sheets into one sheet.
    i have tried all but can’t get thru.

    could you please mail me the solution

    Thanks in advance.

    Comment by saurabh | April 13, 2010 | Reply

  8. Hi Mohan

    Thanks for sharing these information. Its really helping a lot of people like me who are learning QTP.

    Keep up the good work.
    Once again..Thank you

    Comment by Lokeswar Reddy B | May 17, 2010 | Reply

  9. I was having trouble figuring out what the two lines for properly copying & pasting would be. Thanks, it came in handy.

    Comment by Dennis | July 16, 2010 | Reply

  10. How to copy values from one input excel sheet to different tabs in another excel sheet.

    Comment by Kanchana | December 14, 2010 | Reply

  11. Hi Mohan ,
    objWorkSheet.Range(“A1″).PasteSpecial Paste = xlValues.

    Please explain me this script i.e:-What is the function of Pastespecial and how Qtp will recognize xlValues as it is not defined as Dim.

    Comment by Shishir Pandey | February 1, 2011 | Reply

  12. I have run this script in qtp-10.0 it is showing the error as “Excel.Application” not found

    Comment by jagmohan | May 20, 2011 | Reply

  13. please resolve this query

    Comment by jagmohan | May 20, 2011 | Reply

  14. For the very first line of code, it says that “ActiveX component can’t create objExcel. Why is this???

    Comment by Elena Buhay | July 15, 2011 | Reply

  15. Hi Mohan, thanks a lot for your great work..I used the excel automation part for coying 1 file into another and worked fine. But it pops up with 2 excel window. 1 for if I wanna replace the content and other one is if I wanna continue. I tried with recording and also recovery scenario to enter/click yes on those 2 windows. But nothing is working. I have manually click OK button. Can you please suggest? Will really appreciate…

    Comment by Arifur | July 26, 2011 | Reply

  16. […] Copy an excel sheet to another excel […]

    Pingback by tallada | September 18, 2012 | Reply

  17. Hi,

    I wan’t know , how to activate some file (excel ,Prn etc) without use its “Name”

    thanks.

    Comment by shasika liyanage | September 26, 2012 | Reply

  18. Hi,

    I am new to qtp.
    how and where can i execute this code?
    Please help.

    Comment by indumathi | October 29, 2012 | Reply

    • just open qtp window and type the code dont copy paste it wont work and run it ok

      Comment by kumar | October 31, 2013 | Reply

  19. I have two excel sheets, where in it does vlookup based on Pathname attribute, the matched machines will be copied to second excelsheet.

    So, colud you please help me how to automate vlookup and provide some macros for the same

    Comment by indumathi | October 29, 2012 | Reply

  20. QTP Scripts Blog—http://funandknowledge.blogspot.com

    Comment by QTP Scripts | March 13, 2013 | Reply

  21. Hi, Is there a way we can also copy the formating from once excel to the other ?
    I was able to do in VBA but not able to do in QTP

    Comment by mmk | June 20, 2013 | Reply

  22. super example some more advance file system we want in QTP

    Comment by kumar | October 31, 2013 | Reply


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: