Megan Fan
FRProject
User
 Offline
Posts: 48
|
 |
« on: March 30, 2008, 11:44:34 PM » |
|
I want to copy an Excel Sheet to an empty sheet. My scripts are:
Set ExcelObj = CreateObject("Excel.Application")
Set activexobj=CreateObject("WScript.Shell")
ExcelObj.Visible = true
ExcelObj.Workbooks.Open "C:\temp.xls"
wait(2)
Set objSheet1 = ExcelObj.Worksheets("MEMBERS")
objSheet1.Activate
objSheet1.range("A1").Select
activexobj.Sendkeys "^a"
activexobj.Sendkeys "^c"
Set objSheet2 = ExcelObj.Worksheets("Sheet2")
objSheet2.Activate
objSheet2.range("A1").Select
activexobj.Sendkeys "^a"
activexobj.SendKeys "^v"
wait(2)
Copy result is failed. The Sheet2 is still empty. Debugging into these scripts and I found after "^a" statement, all the scripts in QTP are selected, but the "MEMBERS" sheet is selected. Why this happaned?
|
|
|
|
|
Logged
|
|
|
|
srikanth Narayanan
User
Offline
Posts: 40
|
 |
« Reply #1 on: March 31, 2008, 12:33:04 AM » |
|
Hi Megan Fan,
try if you want to create a new excfel sheet itself,
Set ExcelObj = CreateObject("Excel.Application")
Set activexobj=CreateObject("WScript.Shell")
ExcelObj.Visible = true
ExcelObj.Workbooks.Open "C:\temp.xls"
ExcelObj.Workbooks.saveas "D:\temp1.xls"
but if you want to create add that in your existing sheet itself then
Set ExcelObj = CreateObject("Excel.Application")
ExcelObj.Workbooks.Open "C:\temp.xls"
ExcelObj.Worksheets("MEMBERS").Activate
Activesheet.usedrange.select
selection.copy
ExcelObj.Worksheets("Sheet2").Activate
ActiveSheet.Paste
Please let us know if that works
|
|
|
|
|
Logged
|
|
|
|
Megan Fan
FRProject
User
Offline
Posts: 48
|
|
« Reply #2 on: March 31, 2008, 01:27:13 AM » |
|
Run these statement:
Set ExcelObj = CreateObject("Excel.Application")
ExcelObj.Workbooks.Open "C:\temp.xls"
ExcelObj.Worksheets("MEMBERS").Activate
Activesheet.usedrange.select
selection.copy
ExcelObj.Worksheets("Sheet2").Activate
ActiveSheet.Paste
It prompted "Object Required: 'ActiveSheet'".
|
|
|
|
|
Logged
|
|
|
|
srikanth Narayanan
User
Offline
Posts: 40
|
|
« Reply #3 on: March 31, 2008, 01:40:48 AM » |
|
Sorry if it's not working. did u try ExcelObj.Worksheets("MEMBERS") instead og the active sheet
|
|
|
|
|
Logged
|
|
|
|
Megan Fan
FRProject
User
Offline
Posts: 48
|
|
« Reply #4 on: March 31, 2008, 07:00:18 PM » |
|
These statements will work in QTP.
Set ExcelObj = CreateObject("Excel.Application")
Set activexobj=CreateObject("WScript.Shell")
ExcelObj.Visible = true
ExcelObj.Workbooks.Open "D:\temp.xls"
ExcelObj.Worksheets("MEMBERS").Activate
ExcelObj.Worksheets("MEMBERS").UsedRange.Select
ExcelObj.Worksheets("MEMBERS").UsedRange.copy
ExcelObj.Worksheets("Sheet1").Activate
ExcelObj.Worksheets("Sheet1").Range("A1").Select
ExcelObj.Worksheets("Sheet1").Paste
Thank Narayanan very much!
|
|
|
|
|
Logged
|
|
|
|
|
