Hi.
Can someone help me with this script?
Why it doesn't run?
Thanks for advanced.
dalexx
VBSTART
Sub Chiamata2()
Dim xl1
Dim xl2
Set xl1 = GetObject("C:\Appoggio\Tariffatore\BT.xls")
Set xl2 = GetObject("C:\Appoggio\Tariffatore\input_mod.xls")
xl1.Application.Visible = True
xl1.Worksheets("B2V 2003").Select
Range("A2:M17").Select
Selection.Copy
xl2.Workbooks.Activate
xl2.Worksheets("Foglio2").Select
Range("A3").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveWorkbook.SaveAs Filename:="C:\appoggio\tariffatore\input.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
xl1.Quit
xl2.Quit
Set xl1 = Nothing
Set xl2 = Nothing
End Sub
VBEND
VBRun>Chiamata
Cut and paste with Excel in Vbscript
Moderators: JRL, Dorian (MJT support)
Hi,
There are a number of problems with your script. First you are calling sub Chiamata when it is called Chiamata2. Then you are trying to reference Excel methods and properties without referencing the objects to which they belong. E.g. Macro Scheduler / VBScript doesn't know about the Range object - you can't just reference it on it's own as it belongs to the XL sheet object which belongs to the XL workbook object, which belongs to the XL application object.
Another thing to remember is that Macro Scheduler / VBScript doesn't know about XL constants. E.g. xlValues is a constant variable only known to Excel. So to use it you need to either specify the value of xlValues or declare it as a constant in your script. Remember that we are working outside of XL so these XL values aren't known by us. I used the XL VBA debugger to determine the values of the constants that you want and then declared them as constants at the top of your script.
I have rewritten your script below. It works fine, but there may be other ways to do it.
VBSTART
Const xlValues = -4163
Const xlNone = -4142
Const xlNormal = -4143
Sub Chiamata2()
Dim xlApp
Dim xlSheet1
Dim xlSheet2
Dim xlBook1
Dim xlBook2
Set xlApp = CreateObject("Excel.Application")
xlApp.Workbooks.Open "C:\Appoggio\Tariffatore\BT.xls"
Set xlBook1 = xlApp.Workbooks("bt.xls")
Set xlSheet1 = xlBook1.Worksheets("B2V 2003")
xlApp.Application.Visible = True
xlSheet1.Range("A2:M17").Copy
'Above line quicker than next two
'xlSheet1.Range("A2:M17").Select
'xlApp.Selection.Copy
xlApp.Workbooks.Open "C:\Appoggio\Tariffatore\input_mod.xls"
Set xlBook2 = xlApp.Workbooks("input_mod.xls")
Set xlSheet2 = xlBook2.Worksheets("Foglio2")
xlSheet2.Range("A3").PasteSpecial xlValues, xlNone, False, False
xlBook2.SaveAs "C:\appoggio\tariffatore\input.xls", xlNormal, "", "", False, False
xlApp.Quit
Set xlApp = Nothing
Set xlBook1 = Nothing
Set xlBook2 = Nothing
Set xlSheet1 = Nothing
Set xlSheet2 = Nothing
End Sub
VBEND
VBRun>Chiamata2
There are a number of problems with your script. First you are calling sub Chiamata when it is called Chiamata2. Then you are trying to reference Excel methods and properties without referencing the objects to which they belong. E.g. Macro Scheduler / VBScript doesn't know about the Range object - you can't just reference it on it's own as it belongs to the XL sheet object which belongs to the XL workbook object, which belongs to the XL application object.
Another thing to remember is that Macro Scheduler / VBScript doesn't know about XL constants. E.g. xlValues is a constant variable only known to Excel. So to use it you need to either specify the value of xlValues or declare it as a constant in your script. Remember that we are working outside of XL so these XL values aren't known by us. I used the XL VBA debugger to determine the values of the constants that you want and then declared them as constants at the top of your script.
I have rewritten your script below. It works fine, but there may be other ways to do it.
VBSTART
Const xlValues = -4163
Const xlNone = -4142
Const xlNormal = -4143
Sub Chiamata2()
Dim xlApp
Dim xlSheet1
Dim xlSheet2
Dim xlBook1
Dim xlBook2
Set xlApp = CreateObject("Excel.Application")
xlApp.Workbooks.Open "C:\Appoggio\Tariffatore\BT.xls"
Set xlBook1 = xlApp.Workbooks("bt.xls")
Set xlSheet1 = xlBook1.Worksheets("B2V 2003")
xlApp.Application.Visible = True
xlSheet1.Range("A2:M17").Copy
'Above line quicker than next two
'xlSheet1.Range("A2:M17").Select
'xlApp.Selection.Copy
xlApp.Workbooks.Open "C:\Appoggio\Tariffatore\input_mod.xls"
Set xlBook2 = xlApp.Workbooks("input_mod.xls")
Set xlSheet2 = xlBook2.Worksheets("Foglio2")
xlSheet2.Range("A3").PasteSpecial xlValues, xlNone, False, False
xlBook2.SaveAs "C:\appoggio\tariffatore\input.xls", xlNormal, "", "", False, False
xlApp.Quit
Set xlApp = Nothing
Set xlBook1 = Nothing
Set xlBook2 = Nothing
Set xlSheet1 = Nothing
Set xlSheet2 = Nothing
End Sub
VBEND
VBRun>Chiamata2
MJT Net Support
[email protected]
[email protected]
