|
Pastespecial via vbscript not working - REPOST
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. |
Pastespecial via vbscript not working - REPOST
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. |
Pastespecial via vbscript not working - REPOST
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. |
Pastespecial via vbscript not working - REPOST
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. |
Pastespecial via vbscript not working - REPOST
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. |
Pastespecial via vbscript not working - REPOST
sh.Parent.SaveAs "C:\test.txt", -4158
-- regards, Tom Ogilvy Dave Peterson wrote in message ... 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 |
Pastespecial via vbscript not working - REPOST
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. |
Pastespecial via vbscript not working - REPOST
Thanks Tom and Dave. That worked.
I appreciate your help. "Tom Ogilvy" wrote in message ... 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. |
Pastespecial via vbscript not working - REPOST
Oops. Missed that.
Tom Ogilvy wrote: sh.Parent.SaveAs "C:\test.txt", -4158 -- regards, Tom Ogilvy Dave Peterson wrote in message ... 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 -- Dave Peterson |
| All times are GMT +1. The time now is 02:51 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com