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.

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.

--
Regards,
Tom Ogilvy


news.easynews.com wrote in message
...
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.

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" wrote in message
...
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.

--
Regards,
Tom Ogilvy


news.easynews.com wrote in message
...
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.

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.

--
Regards,
Tom Ogilvy

news.easynews.com wrote in message
...
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" wrote in message
...
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.

--
Regards,
Tom Ogilvy


news.easynews.com wrote in message
...
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.

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


"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy

news.easynews.com wrote in message
...
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" wrote in message
...
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.

--
Regards,
Tom Ogilvy


news.easynews.com wrote in message
...
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.

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)????



"news.easynews.com" wrote:

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

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy

news.easynews.com wrote in message
...
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" wrote in message
...
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.

--
Regards,
Tom Ogilvy


news.easynews.com wrote in message
...
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.










--

Dave Peterson

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

--
Regards,
Tom Ogilvy

news.easynews.com wrote in message
...
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


"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy

news.easynews.com wrote in message
...
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" wrote in message
...
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.

--
Regards,
Tom Ogilvy


news.easynews.com wrote in message
...
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.