Results 1 to 6 of 6

Thread: Solved: Paste Special

  1. #1

    Solved: Paste Special

    Hi All,
    the following code works, However can someone see what I need to add to make the selection linked(paste special) to the word doc.

    [vba]Sub ExcelMacro()
    'dim app dim workbook dim worksheet.
    Dim objExcel As Excel.Application 'why is this sometimes "as object"
    Dim Wkb As Excel.Workbook
    Dim WS As Excel.Worksheet
    Dim Path As String
    Dim FName As String
    Dim TargetRow As Long
    Dim var1 As String
    On Error GoTo killit

    var1 = Selection
    'var1 = InputBox("What ever you type here goes to Excel", "Text to Excel")
    Path = ThisDocument.Path
    FName = "book2.xls"
    Set objExcel = New Excel.Application
    objExcel.Visible = True 'Add this line to make the Excel app visible
    Set Wkb = objExcel.Workbooks.Open(FileName:=Path & "\" & FName)
    Set WS = Wkb.Sheets("Sheet1")
    TargetRow = Wkb.Sheets("Sheet1").Range("A65536").End(xlup).Row + 1
    Wkb.Sheets("Sheet1").Range("A" & TargetRow).Value = var1

    MsgBox "when I want"
    Wkb.Close True
    Set objExcel = Nothing
    Set Wkb = Nothing
    Set WS = Nothing

    End Sub


  2. #2
    next question can someone tell me how to move this thread to automation. Sorry

  3. #3
    VBAX Wizard lucas's Avatar
    Jun 2004
    Tulsa, Oklahoma
    Moved to automation section.....
    Hi Peter,
    could you give a little more info so we can help you. I have to assume(since you don't make it clear) that you are running this from Word and wish to paste to excel????

    A little explaination of what your trying to do would help a lot.
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  4. #4
    Hi Lucas ,
    I am trying to select some text from a word doc and Pastespecial into excel. The next thing I need is to try it with a table then an object. It is part of a half finished work project.
    thanks for your reply

  5. #5
    MS Excel MVP VBAX Tutor
    Mar 2005
    Peter -

    1. Dim objExcel As Excel.Application 'why is this sometimes "as object"

    When using late binding, the compiler doesn't know what the variable type is, so it is treated as a generic object. See

    2. Use the macro recorder. Copy the text in Word, switch to Excel, activate the macro recorder, then use Paste Special - Link to get the relevant code:

    ActiveSheet.Paste Link:=True

    This means you need to adjust the code somewhat. The Selection.Copy that was redundant in your original code is now important, but you can remove the stuff with var1. Instead of

    Wkb.Sheets("Sheet1").Range("A" & TargetRow).Value = var1

    use this

    Wkb.Sheets("Sheet1").Range("A" & TargetRow).Select
    ActiveSheet.Paste Link:=True
    - Jon
    Jon Peltier, Microsoft Excel MVP
    Peltier Technical Services
    Tutorials and Custom Solutions

  6. #6
    Thanks Jon this cleared some things up for me.
    I also visited your web site. I really needed the VBScripts you have. This is a must for all VBA beginners. I don't no how many times I have needed to close some of these programs and was unable to.
    Thank you
    Last edited by peterwmartin; 12-30-2006 at 07:14 AM.

Posting Permissions

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