Macro-Text from word doc paste to excel empty row

[J]

Hi all!
I think that I am very close to the answer on this question so any
assistance you could give would be greatly appreciated!
I am calling a macro from word to format a page of text then copy and
open an excel doc paste it to the first *empty* row in this
spreadsheet.
So far I have:

[more code above...]
'Opens an Excel worksheet located in the default Documents folder


strXLPath = "C:\Fill_the_Bank_MasterList.xls"

Set objExcelApp = CreateObject("Excel.Application")
objExcelApp.Workbooks.Open (strXLPath)
Set objExcelBook = objExcelApp.ActiveWorkbook
Set objExcelSheets = objExcelBook.Worksheets
Set objExcelSheet = objExcelBook.Sheets(1)
objExcelSheet.Activate
objExcelApp.Application.Visible = True

'Paste clipboard into first empty row
'

objExcelSheet.Range("ListTop").Select
objExcelSheet.Selection.End(xlDown).Select <--- Error 438 here
objExcelSheet.ActiveCell.Offset(1, 0).PasteSpecial


End Sub

I get an error 438 on this (obj or property not supported on the line
as indicated. I can paste these lines into excel and run the macro
from inside excel, but when I run from word, I get the error. Should I
set this before I run in word?

Thanks again!

 

[Dave Peterson]

I think you have a couple of problems. Selection belongs to a window or the
application--not the worksheet. Similarly with Activecell.

And unless you have a reference to excel in your word project, it won't know
what xlDown means. (And if you did have a reference to excel, you wouldn't have
used createobject????).

objExcelApp.Selection.End(-4121).Select
objExcelApp.ActiveCell.Offset(1, 0).PasteSpecial

A quick and dirty way to find excel's constants is to open excel, got to the VBE
and hit ctrl-G to see the immediate window.

then type:
?xldown
(? is old basic for print)

The immediate window showed this:
-4121

(You could also open the object browser and see lots of excel's constants all at
once.)

Hi all!
I think that I am very close to the answer on this question so any
assistance you could give would be greatly appreciated!
I am calling a macro from word to format a page of text then copy and
open an excel doc paste it to the first *empty* row in this
spreadsheet.
So far I have:

[more code above...]
'Opens an Excel worksheet located in the default Documents folder

strXLPath = "C:\Fill_the_Bank_MasterList.xls"

Set objExcelApp = CreateObject("Excel.Application")
objExcelApp.Workbooks.Open (strXLPath)
Set objExcelBook = objExcelApp.ActiveWorkbook
Set objExcelSheets = objExcelBook.Worksheets
Set objExcelSheet = objExcelBook.Sheets(1)
objExcelSheet.Activate
objExcelApp.Application.Visible = True

'Paste clipboard into first empty row
'

objExcelSheet.Range("ListTop").Select
objExcelSheet.Selection.End(xlDown).Select <--- Error 438 here
objExcelSheet.ActiveCell.Offset(1, 0).PasteSpecial


End Sub

I get an error 438 on this (obj or property not supported on the line
as indicated. I can paste these lines into excel and run the macro
from inside excel, but when I run from word, I get the error. Should I
set this before I run in word?

Thanks again!