PasteSpecial: values and formats possible in vbs?

[D. Pirate Roberts]

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?

 

[y sakuda]

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

Hellow
Try this

XLBook.Worksheets("Sheet1").Range("B1").PasteSpecial -4122

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

 

[Tom Ogilvy]

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

 

[Tom Ogilvy]

? xlPasteValuesAndNumberFormats
12
? xlpasteFormats
-4122
? xlpasteValues
-4163

 

[y sakuda]

XLBook.Worksheets("Sheet1").Range("B1").PasteSpecial -4122

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

 

[D. Pirate Roberts]

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]

Excel VBA help on Pastespecial

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

 

[rochelles]

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

 

[Lee Peedin]

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

 

[Tom Ogilvy]

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.