PasteSpecial: values and formats possible in vbs?
 

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").PasteSpeci al

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?

PasteSpecial: values and formats possible in vbs?
 

"D. Pirate Roberts" wrote in message
...
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").PasteSpeci al

Hellow
Try this

XLBook.Worksheets("Sheet1").Range("B1").PasteSpeci al -4122

Note: for -4122 search for Object browser with xlpasteformats
Y Sakuda from JPN

PasteSpecial: values and formats possible in vbs?
 

XLBook.Worksheets("Sheet1").Select
XLBook.Worksheets("Sheet1").Range("A1").Copy
XLBook.Worksheets("Sheet1").Range("B1").PasteSpeci al 12

--
regards,
Tom Ogilvy


"D. Pirate Roberts" wrote in message
...
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").PasteSpeci al

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?

PasteSpecial: values and formats possible in vbs?
 

? xlPasteValuesAndNumberFormats
12
? xlpasteFormats
-4122
? xlpasteValues
-4163


--
Regards,
Tom Ogilvy


"y sakuda" wrote in message
...
"D. Pirate Roberts" wrote in message
...
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").PasteSpeci al

Hellow
Try this

XLBook.Worksheets("Sheet1").Range("B1").PasteSpeci al -4122

Note: for -4122 search for Object browser with xlpasteformats
Y Sakuda from
JPN

PasteSpecial: values and formats possible in vbs?
 

"y sakuda" wrote in message
...
"D. Pirate Roberts" wrote in message
...

XLBook.Worksheets("Sheet1").Range("B1").PasteSpeci al -4122

Sorry you want to Paste values and format.
I misread your post.
-4122 paste format only.
Y Sakuda from JPN

PasteSpecial: values and formats possible in vbs?
 

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" wrote in message
...
XLBook.Worksheets("Sheet1").Select
XLBook.Worksheets("Sheet1").Range("A1").Copy
XLBook.Worksheets("Sheet1").Range("B1").PasteSpeci al 12

--
regards,
Tom Ogilvy


"D. Pirate Roberts" wrote in message
...
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").PasteSpeci al

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?

PasteSpecial: values and formats possible in vbs?
 

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" wrote in message
...
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" wrote in message
...
XLBook.Worksheets("Sheet1").Select
XLBook.Worksheets("Sheet1").Range("A1").Copy
XLBook.Worksheets("Sheet1").Range("B1").PasteSpeci al 12

--
regards,
Tom Ogilvy


"D. Pirate Roberts" wrote in message
...
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").PasteSpeci al

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?

PasteSpecial: values and formats possible in vbs?
 

I want to do the same thing, except, copy the last three characters in
cell (file extension, so it will always be three) and past it int
another cell. Any ideas

--
rochelle
-----------------------------------------------------------------------
rochelles's Profile: http://www.excelforum.com/member.php...fo&userid=2780
View this thread: http://www.excelforum.com/showthread.php?threadid=47350

PasteSpecial: values and formats possible in vbs?
 

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"
wrote:

Excel VBA help on Pastespecial

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

PasteSpecial: values and formats possible in vbs?
 

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

Excel VBA help on Pastespecial

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