Closed Thread
Results 1 to 10 of 10

[SOLVED] PasteSpecial: values and formats possible in vbs?

  1. #1
    D. Pirate Roberts
    Guest

    [SOLVED] 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").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?



  2. #2
    y sakuda
    Guest

    Re: PasteSpecial: values and formats possible in vbs?

    "D. Pirate Roberts" <[email protected]> wrote in message
    news:[email protected]...
    >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



  3. #3
    Tom Ogilvy
    Guest

    Re: PasteSpecial: values and formats possible in vbs?

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

    --
    regards,
    Tom Ogilvy


    "D. Pirate Roberts" <[email protected]> wrote in message
    news:[email protected]...
    > 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?
    >
    >




  4. #4
    Tom Ogilvy
    Guest

    Re: PasteSpecial: values and formats possible in vbs?

    ? xlPasteValuesAndNumberFormats
    12
    ? xlpasteFormats
    -4122
    ? xlpasteValues
    -4163


    --
    Regards,
    Tom Ogilvy


    "y sakuda" <[email protected]> wrote in message
    news:%[email protected]...
    > "D. Pirate Roberts" <[email protected]> wrote in message
    > news:[email protected]...
    > >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
    >
    >




  5. #5
    y sakuda
    Guest

    Re: PasteSpecial: values and formats possible in vbs?

    "y sakuda" <[email protected]> wrote in message
    news:%[email protected]...
    > "D. Pirate Roberts" <[email protected]> wrote in message
    > news:[email protected]...
    >
    > 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


  6. #6
    D. Pirate Roberts
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    > XLBook.Worksheets("Sheet1").Select
    > XLBook.Worksheets("Sheet1").Range("A1").Copy
    > XLBook.Worksheets("Sheet1").Range("B1").PasteSpecial 12
    >
    > --
    > regards,
    > Tom Ogilvy
    >
    >
    > "D. Pirate Roberts" <[email protected]> wrote in message
    > news:[email protected]...
    >> 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?
    >>
    >>

    >
    >




  7. #7
    Tom Ogilvy
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    > 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" <[email protected]> wrote in message
    > news:[email protected]...
    > > XLBook.Worksheets("Sheet1").Select
    > > XLBook.Worksheets("Sheet1").Range("A1").Copy
    > > XLBook.Worksheets("Sheet1").Range("B1").PasteSpecial 12
    > >
    > > --
    > > regards,
    > > Tom Ogilvy
    > >
    > >
    > > "D. Pirate Roberts" <[email protected]> wrote in message
    > > news:[email protected]...
    > >> 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?
    > >>
    > >>

    > >
    > >

    >
    >




  8. #8
    Registered User
    Join Date
    10-04-2005
    Posts
    2

    Need similar help

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

  9. #9
    Lee Peedin
    Guest

    Re: 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" <[email protected]>
    wrote:

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



  10. #10
    Tom Ogilvy
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    > 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" <[email protected]>
    > wrote:
    >
    > >Excel VBA help on Pastespecial
    > >
    > >the values for the constants can be seen in the Object Browser in the

    VBE.
    >




Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1