Solved: Paste Special

[peterwmartin]

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.
thanks

[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

Selection.Copy
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"
killit:
Wkb.Save
Wkb.Close True
objExcel.Quit
Set objExcel = Nothing
Set Wkb = Nothing
Set WS = Nothing

End Sub

[/vba]

[peterwmartin]

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

[lucas]

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.

[peterwmartin]

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

[JonPeltier]

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 http://peltiertech.com/Excel/EarlyLateBinding.html

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

[peterwmartin]

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