Controlling Excel from Word

[Barry-W]

Hi, I have read, and used, the information in the Control Excel fro Word
article, and I have got my system to partially work (i.e. I can write to
excel from a Word Macro)

What I am now struggling with is inserting rows in the Excel Spreadsheet
from the Word Macro, here is the relevant portion of the code:

'Open the workbook
Set oWB = oXL.Workbooks.Open(FileName:=WorkbookToWorkOn)

'Select the Sheet

Set oSheet = oWB.Sheets(SheetToWorkOn)

oSheet.Range("C12") = "test" 'To prove I can access the worksheet!
oSheet.Rows("3:3").Select
Selection.Insert Shift:=xlDown ' "Compile Error Method or Data member
not found"
'oSheets.Insert Shift:=xlDown 'doesn't work either
'oSheets.Selection.Insert Shift:=xlDown 'Nor does this,

Could some one point out the errors of my ways please?

Barry

 

[Helmut Weber]

Hi Barry,

first read about Early binding vs. late binding:
http://word.mvps.org/faqs/interdev/EarlyvsLateBinding.htm

Selection.Insert Shift:=xlDown

this would be a Word-selection, not an Excel-Selection

oSheets.Insert Shift:=xlDown 'doesn't work either
oSheets.Selection.Insert Shift:=xlDown 'Nor does this,

Your object is oSheet, not oSheets.
If you use late binding,
instead of the Excel-constant "xlDown",
which is unknown to Word,
use the numeric equivalent,
which is -4121.

 

[Doug Robbins - Word MVP]

Assuming that what you are trying to do is insert a row before row 3, use

oSheet.Range("A3") = "test" 'To prove I can access the worksheet!
Set oRng = oSheet.Rows(3)
oRng.Insert (xlShiftDown) 'Cell A4 will now contain "test"


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Barry-W]

Helmut, Doug;

Thanks for the information, examples and references.The programme now works
as expected, and I am a little wiser with respect to coding. How one gets to
know such information as:

""xlDown", which is unknown to Word, use the numeric equivalent, which is
-4121."

shows the depth of knowledge available here.

Many Thanks

Barry

 

[Helmut Weber]

Hi Barry,

in Excel:
msgbox xlDown
results in
-4121

--

Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Vista Small Business, Office XP

 

[Barry-W]

Helmut, it is SO obvious when you put it like that! One more tool in my
arsenal of weapons. One day I'll get the better of this programming!!

Thanks again

Barry