Pastespecial via vbscript not working

[msnews.microsoft.com]

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.

 

[Chip Pearson]

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]

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.