Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pastespecial via vbscript not working
I'm having problems using pastespecial. These are the steps the code
performs: 1) Open workbook A 2) Select specifi worksheet 3) Copy the data from a specifig range 4) Creates/Opens a new blank workbook 5) Pastes the values of the cells in the range into the first worksheet (Sheet1) starting at A1 6) Saves the new workbook as a tab delimite file 7) closs and exits Everything works except step 5. If I use paste, it works except it doesn't give me the value in cells. The following code gives me : "Microsoft Excel: PasteSpecial method of Range class failed" Set Xl = CreateObject("Excel.Application") Xl.Visible = False Xl.Workbooks.Open("C:\source.xls") Xl.Worksheets("MySheet").Select Xl.Range("B2:C12").Select Xl.Selection.Copy Xl.Workbooks.Add Xl.Range("A1").Select 'Xl.ActiveSheet.PasteSpecial -4163,-4142,0,0 Xl.Selection.PasteSpecial -4163,-4142,0,0 Xl.Application.CutCopyMode = False Xl.ActiveWorkbook.SaveAs "C:\test.txt",-4158,0 Xl.ActiveWorkbook.Close(0) Xl.quit If I try Xl.ActiveSheet.PasteSpecial -4163,-4142,0,0 instead, I get the following: "Microsoft Excel: PasteSpecial method of Worksheet class failed" I need to use pastespecial because I want just the values of the cell. Any help would be greatly appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pastespecial via vbscript not working
It would be helpful if you posted the actual code, but try
switching the order of steps 3 and 4. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "msnews.microsoft.com" wrote in message ... I'm having problems using pastespecial. These are the steps the code performs: 1) Open workbook A 2) Select specifi worksheet 3) Copy the data from a specifig range 4) Creates/Opens a new blank workbook 5) Pastes the values of the cells in the range into the first worksheet (Sheet1) starting at A1 6) Saves the new workbook as a tab delimite file 7) closs and exits Everything works except step 5. If I use paste, it works except it doesn't give me the value in cells. The following code gives me : "Microsoft Excel: PasteSpecial method of Range class failed" Set Xl = CreateObject("Excel.Application") Xl.Visible = False Xl.Workbooks.Open("C:\source.xls") Xl.Worksheets("MySheet").Select Xl.Range("B2:C12").Select Xl.Selection.Copy Xl.Workbooks.Add Xl.Range("A1").Select 'Xl.ActiveSheet.PasteSpecial -4163,-4142,0,0 Xl.Selection.PasteSpecial -4163,-4142,0,0 Xl.Application.CutCopyMode = False Xl.ActiveWorkbook.SaveAs "C:\test.txt",-4158,0 Xl.ActiveWorkbook.Close(0) Xl.quit If I try Xl.ActiveSheet.PasteSpecial -4163,-4142,0,0 instead, I get the following: "Microsoft Excel: PasteSpecial method of Worksheet class failed" I need to use pastespecial because I want just the values of the cell. Any help would be greatly appreciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pastespecial via vbscript not working
Hi Chip.
I did post thecode towards the bottom of my post. I tried switching steps 3 and 4, but that did not work. If I set my Excel application to be visible, the last window that is open is my new empty workbook open. When the error occurrs, I can manually go to the edit -- pastespecial and paste the values that way with no problem. Has anyone else had this problem? I am using Excel 2000, but tried the same code with Excel 2002. "Chip Pearson" wrote in message ... It would be helpful if you posted the actual code, but try switching the order of steps 3 and 4. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "msnews.microsoft.com" wrote in message ... I'm having problems using pastespecial. These are the steps the code performs: 1) Open workbook A 2) Select specifi worksheet 3) Copy the data from a specifig range 4) Creates/Opens a new blank workbook 5) Pastes the values of the cells in the range into the first worksheet (Sheet1) starting at A1 6) Saves the new workbook as a tab delimite file 7) closs and exits Everything works except step 5. If I use paste, it works except it doesn't give me the value in cells. The following code gives me : "Microsoft Excel: PasteSpecial method of Range class failed" Set Xl = CreateObject("Excel.Application") Xl.Visible = False Xl.Workbooks.Open("C:\source.xls") Xl.Worksheets("MySheet").Select Xl.Range("B2:C12").Select Xl.Selection.Copy Xl.Workbooks.Add Xl.Range("A1").Select 'Xl.ActiveSheet.PasteSpecial -4163,-4142,0,0 Xl.Selection.PasteSpecial -4163,-4142,0,0 Xl.Application.CutCopyMode = False Xl.ActiveWorkbook.SaveAs "C:\test.txt",-4158,0 Xl.ActiveWorkbook.Close(0) Xl.quit If I try Xl.ActiveSheet.PasteSpecial -4163,-4142,0,0 instead, I get the following: "Microsoft Excel: PasteSpecial method of Worksheet class failed" I need to use pastespecial because I want just the values of the cell. Any help would be greatly appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run VBScript from Excel | Excel Discussion (Misc queries) | |||
Copy PasteSpecial not working | Excel Worksheet Functions | |||
embed excel in ie using vbscript | Excel Programming | |||
Entering a formula via VBscript | Excel Programming | |||
use shell function in vbscript | Excel Programming |