peterwmartin
12-22-2006, 05:23 AM
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
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
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
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