Pastespecial via vbscript not working - REPOST

[news.easynews.com]

This is a repost. Maybe someone else can help.


I'm having problems using pastespecial. These are the steps the code
performs (code is below):

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.

 

[Tom Ogilvy]

There are two forms of pastespecial. Sheet and range. You are using the
Sheet form with the Range form arguments.

Xl.Range("A1").Select
Xl.ActiveSheet.Range("A1").PasteSpecial -4163,-4142,0,0

Should work.

 

[news.easynews.com]

Thanks Tom. I tried it but got the same error. Here is the revised code:

Set Xl = CreateObject("Excel.Application")
Xl.Visible = false
Xl.Workbooks.Open("C:\test.xls")
Xl.Worksheets("Sheet1").Select
Xl.Range("B4:C5").Select
Xl.Selection.Copy
Xl.Workbooks.Add
Xl.ActiveSheet.Range("A1").PasteSpecial -4163,-4142,0,0
Xl.Application.CutCopyMode = False
Xl.ActiveWorkbook.SaveAs "C:\test.txt",-4158,0
Xl.ActiveWorkbook.Close(0)
Xl.quit

This is the error I get:
Microsoft Excel: PasteSpecial method of Range class failed

I read somewhere that there may be some bugs in excel when programmaticaly
using pastespecial between workbooks. Can you duplicate the error I am
getting?

If I set Excel visible, the last active window is my new workbook. I can
manually go to the Edit menu and use pastespecial from there.

 

[Tom Ogilvy]

Sub Tester10()
Dim xl As Object
Dim sh As Object, wkbk As Object
Set xl = CreateObject("Excel.Application")
xl.Visible = False
Set wkbk = xl.Workbooks.Open("C:\test.xls")
xl.Workbooks.Add
Set sh = xl.ActiveSheet
wkbk.Worksheets("sheet1").Range("B4:C5").Copy
sh.Range("A1").PasteSpecial -4163, -4142
On Error Resume Next
Kill "C:\Test.txt"
On Error GoTo 0
sh.Parent.SaveAs "C:\test.txt", -4158
sh.Parent.Close SaveChanges:=False
Set sh = Nothing
wkbk.Close SaveChanges:=False
Set wkbk = Nothing
xl.Quit
Set xl = Nothing

End Sub

worked fine for me.

I am not aware of any problems with pastespecial as you describe.

 

[news.easynews.com]

I get many errors from this. I am not running this in Excel. I am running a
..vbs file from the DOS prompt as follows:

c:\>cscript test.vbs

 

[Dave Peterson]

Things are slightly different from a .vbs:

Option Explicit

Sub Tester10()
Dim xl
Dim sh, wkbk
Set xl = CreateObject("Excel.Application")
xl.Visible = True
Set wkbk = xl.Workbooks.Open("C:\test.xls")
xl.Workbooks.Add
Set sh = xl.ActiveSheet
wkbk.Worksheets("sheet1").Range("B4:C5").Copy
sh.Range("A1").PasteSpecial -4163, -4142
On Error Resume Next
Kill "C:\Test.txt"
On Error GoTo 0
sh.Parent.SaveAs "C:\test.txt", -4158
sh.Parent.Close False
Set sh = Nothing
wkbk.Close False
Set wkbk = Nothing
xl.Quit
Set xl = Nothing

End Sub

Notice the "As Objects" are gone. As well as "savechanges:="

But shouldn't one of those closes actually save the changes (sh.parent.close
True)????

 

[Tom Ogilvy]

sh.Parent.SaveAs "C:\test.txt", -4158

 

[Tom Ogilvy]

I ran it from Word, but

using

c:\> cscript c:\test.vbs

from the DOS prompt
this ran fine for me

with c:\Test.vbs containing this:

Set xl = CreateObject("Excel.Application")
xl.Visible = False
Set wkbk = xl.Workbooks.Open("C:\test.xls")
xl.Workbooks.Add
Set sh = xl.ActiveSheet
wkbk.Worksheets("sheet1").Range("B4:C5").Copy
sh.Range("A1").PasteSpecial -4163, -4142
On Error Resume Next
Kill "C:\Test.txt"
On Error GoTo 0
sh.Parent.SaveAs "C:\test.txt", -4158
sh.Parent.Close 0
Set sh = Nothing
wkbk.Close 0
Set wkbk = Nothing
xl.Quit
Set xl = Nothing

 

[msnews.microsoft.com]

Thanks Tom and Dave. That worked.

I appreciate your help.

 

[Dave Peterson]

Oops. Missed that.

sh.Parent.SaveAs "C:\test.txt", -4158