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
25 Comments »
Leave a comment
Mohan Kumar Kakarla
-
Archives
- July 2011 (1)
- March 2011 (1)
- November 2010 (1)
- November 2008 (4)
- October 2008 (2)
- September 2008 (2)
- June 2008 (1)
- March 2008 (4)
- February 2008 (44)
-
Categories
- Automated Test Script Creation Process
- Automating MS Word
- Automating QC
- Automation Object Model For QTP
- Backward compatability in QTP
- Browser Scripts
- COM and QTP
- compare 2 text files
- Descriptive programming
- Descriptive Programming and Child Objetcs
- Dictonary Object
- DotNetFactory
- Excel Automation
- Extra Topics
- Files and Folders
- General Standards to be followed in Test scripts
- New Features In QTP9.5
- QC-OTA
- QTP Additional Faqs
- QTP Faqs
- QTP Naming Convention
- QTP Tips and Faqs
- Running stored procedures from QTP
- Send keyboard keys
- Uncategorized
- Uninstall a software using QTP
- Uninstall a software using vbscript
- Update data in a database
- VBScript and IE Automation
- VBScript Faqs and Useful resources
- What’s New in Quick Test Pro 9.0
- Whats New in QTP 9.5
- XML Scripting
-
RSS
Entries RSS
Comments RSS
Quick test professional 
Vineet,
Please search before posting the comment
try here
https://quicktestprofessional.wordpress.com/category/excel-automation/
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.
Dear Mohan,
The website is really helping good work 🙂
thanx a lot
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?
same dought for me also do you got reply for this
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
just comment the create command and add open command and get file and the use copy
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
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.
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
I was having trouble figuring out what the two lines for properly copying & pasting would be. Thanks, it came in handy.
How to copy values from one input excel sheet to different tabs in another excel sheet.
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.
I have run this script in qtp-10.0 it is showing the error as “Excel.Application” not found
please resolve this query
For the very first line of code, it says that “ActiveX component can’t create objExcel. Why is this???
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…
[…] Copy an excel sheet to another excel […]
Hi,
I wan’t know , how to activate some file (excel ,Prn etc) without use its “Name”
thanks.
Hi,
I am new to qtp.
how and where can i execute this code?
Please help.
just open qtp window and type the code dont copy paste it wont work and run it ok
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
QTP Scripts Blog—http://funandknowledge.blogspot.com
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
super example some more advance file system we want in QTP