Prev: Problem whit CreateObject("WScript.Shell") and space in string
Next: Check WMI Connectivity
From: D. Pirate Roberts on 5 Oct 2005 13:51
Thanks, Tom - that works perfectly! By the way, is there an online reference
guide that lists the PasteSpecial codes that can be used in this manner?


"Tom Ogilvy" <twogilvy(a)msn.com> wrote in message
news:OVC0cJdyFHA.2064(a)TK2MSFTNGP09.phx.gbl...
> XLBook.Worksheets("Sheet1").Select
> XLBook.Worksheets("Sheet1").Range("A1").Copy
> XLBook.Worksheets("Sheet1").Range("B1").PasteSpecial 12
>
> --
> regards,
> Tom Ogilvy
>
>
> "D. Pirate Roberts" <dreadpirateroberts(a)bride.com> wrote in message
> news:etaUBAdyFHA.1168(a)TK2MSFTNGP15.phx.gbl...
>> I use the following code in vbs to copy and paste data from one Excel
>> cell
>> to another:
>>
>> XLBook.Worksheets("Sheet1").Select
>> XLBook.Worksheets("Sheet1").Range("A1").Copy
>> XLBook.Worksheets("Sheet1").Range("B1").PasteSpecial
>>
>> My code above does a regular paste, but I need to do a paste special that
>> only pastes values and number formats. I've created a macro in Excel that
>> does this and the vba code for it is:
>>
>> Range("A1").Select
>> Selection.Copy
>> Range("B1").Select
>> Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
>> Operation:=xlNone, SkipBlanks:=False, Transpose:=False
>>
>> However, the vba code above does not work in vbs. It throws an Expceted
>> Statement error at the first colon (:) in the PasteSpecial line. Does
> anyone
>> know how I can do this in vbs?
>>
>>
>
>


From: Tom Ogilvy on 5 Oct 2005 14:59
Excel VBA help on Pastespecial

the values for the constants can be seen in the Object Browser in the VBE.

--
Regards,
Tom Ogilvy

"D. Pirate Roberts" <dreadpirateroberts(a)bride.com> wrote in message
news:Og1AcVdyFHA.2516(a)TK2MSFTNGP12.phx.gbl...
> Thanks, Tom - that works perfectly! By the way, is there an online
reference
> guide that lists the PasteSpecial codes that can be used in this manner?
>
>
> "Tom Ogilvy" <twogilvy(a)msn.com> wrote in message
> news:OVC0cJdyFHA.2064(a)TK2MSFTNGP09.phx.gbl...
> > XLBook.Worksheets("Sheet1").Select
> > XLBook.Worksheets("Sheet1").Range("A1").Copy
> > XLBook.Worksheets("Sheet1").Range("B1").PasteSpecial 12
> >
> > --
> > regards,
> > Tom Ogilvy
> >
> >
> > "D. Pirate Roberts" <dreadpirateroberts(a)bride.com> wrote in message
> > news:etaUBAdyFHA.1168(a)TK2MSFTNGP15.phx.gbl...
> >> I use the following code in vbs to copy and paste data from one Excel
> >> cell
> >> to another:
> >>
> >> XLBook.Worksheets("Sheet1").Select
> >> XLBook.Worksheets("Sheet1").Range("A1").Copy
> >> XLBook.Worksheets("Sheet1").Range("B1").PasteSpecial
> >>
> >> My code above does a regular paste, but I need to do a paste special
that
> >> only pastes values and number formats. I've created a macro in Excel
that
> >> does this and the vba code for it is:
> >>
> >> Range("A1").Select
> >> Selection.Copy
> >> Range("B1").Select
> >> Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
> >> Operation:=xlNone, SkipBlanks:=False, Transpose:=False
> >>
> >> However, the vba code above does not work in vbs. It throws an Expceted
> >> Statement error at the first colon (:) in the PasteSpecial line. Does
> > anyone
> >> know how I can do this in vbs?
> >>
> >>
> >
> >
>
>


From: Lee Peedin on 6 Oct 2005 07:32
Tom,
It's a shame that VBScript doesn't have a built in method to retrieve
ActiveX/OLE constants. I'm not posting this to try and start a flame
war on what scripting language is best, but the one I use (ooRexx) has
2 simple methods that make working with constants very easy to
incorporate into your script.

To retrieve a single constant:
myExcel = .OLEObject~new("Excel.Application")
xlCenter = myExcel~GetConstant("xlCenter")
say 'xlCenter =' xlCenter

To retrieve all constants:
myExcel = .OLEObject~new("Excel.Application")
constants. = myExcel~GetConstant
do i over constants.
say i '=' constants.i
end

A good way to use the retrieval of all constants for a particuliar
ActiveX object is to run the above code once and save the results in a
text file (xlConstants.txt). Then in any script that uses the ActiveX
object in question, all you have to do is load xlConstants.txt into
the .local enviroment.

Just posting this in the hopes that the "keepers" of VBScript might
learn something from the "grandfather" of scripting languages. :-)

Lee


On Wed, 5 Oct 2005 14:59:25 -0400, "Tom Ogilvy" <twogilvy(a)msn.com>
wrote:

>Excel VBA help on Pastespecial
>
>the values for the constants can be seen in the Object Browser in the VBE.

From: Tom Ogilvy on 6 Oct 2005 08:08
Actually, MS has provided a way to read typelibs. Chip Pearson has sample
code on his site. It certainly isn't as easy as your code, but once someone
has written the code, it isn't that bad I wouldn't think.

--
Regards,
Tom Ogilvy

"Lee Peedin" <lpeedinREMOVE(a)UPPERCASEnc.rr.com> wrote in message
news:7b2ak11qe8srp4dvkc0b66t58130gjedcm(a)4ax.com...
> Tom,
> It's a shame that VBScript doesn't have a built in method to retrieve
> ActiveX/OLE constants. I'm not posting this to try and start a flame
> war on what scripting language is best, but the one I use (ooRexx) has
> 2 simple methods that make working with constants very easy to
> incorporate into your script.
>
> To retrieve a single constant:
> myExcel = .OLEObject~new("Excel.Application")
> xlCenter = myExcel~GetConstant("xlCenter")
> say 'xlCenter =' xlCenter
>
> To retrieve all constants:
> myExcel = .OLEObject~new("Excel.Application")
> constants. = myExcel~GetConstant
> do i over constants.
> say i '=' constants.i
> end
>
> A good way to use the retrieval of all constants for a particuliar
> ActiveX object is to run the above code once and save the results in a
> text file (xlConstants.txt). Then in any script that uses the ActiveX
> object in question, all you have to do is load xlConstants.txt into
> the .local enviroment.
>
> Just posting this in the hopes that the "keepers" of VBScript might
> learn something from the "grandfather" of scripting languages. :-)
>
> Lee
>
>
> On Wed, 5 Oct 2005 14:59:25 -0400, "Tom Ogilvy" <twogilvy(a)msn.com>
> wrote:
>
> >Excel VBA help on Pastespecial
> >
> >the values for the constants can be seen in the Object Browser in the
VBE.
>


First  |  Prev  | 
Pages: 1 2
Prev: Problem whit CreateObject("WScript.Shell") and space in string
Next: Check WMI Connectivity