Ken1000
04-10-2006, 10:46 AM
Hello ... how are you today?
I am using Office 2003 and am trying to have an interaction between
Excel and Word. All of my macro code works except for one area.
I have successfully created code in Excel which
creates a new Word doc and pastes from Excel a
specific range. By doing this, that range now
exists in the Word Doc as a table. What I next am
trying to do (but am not successfull) is to create an
empty line directly below that new table and have the
cursor positioned at the very beginning of the empty
line.
I have tried these three lines of code, but nothing works:
:banghead:
'Selection.TypeParagraph
'Selection.GoTo What:=wdGoToLine, Which:=wdGoToAbsolute, Count:=5
'Selection.EndKey Unit:=wdStory
Below is the macro code in its entirety, with the "troublesome area" showing between the two lines with asterisks.
Could you please review my macro and tell me what changes are
needed for this line creation and cursor placement to work?
Thank you very much for your help.
Ken
Sub PlaceExcelRangeIntoWord()
Dim oWord As Word.Application
Dim oDoc As Word.Document
'Get existing instance of Word if it's open; otherwise create a new one
On Error Resume Next
Set oWord = GetObject(, "Word.Application")
If Err Then
Set oWord = New Word.Application
WordWasNotRunning = True
End If
'copy range from Excel
Range("Sheet1!A1:E4").Copy
'create new Word doc
oWord.Visible = True
oWord.Activate
Set oDoc = oWord.Documents.Add
'adjust margins
With ActiveDocument.PageSetup
.TopMargin = InchesToPoints(0.5)
.BottomMargin = InchesToPoints(0.5)
.LeftMargin = InchesToPoints(0.5)
.RightMargin = InchesToPoints(0.5)
End With
'paste Excel range into Word doc
oDoc.Range.Paste
'* * * * * * * * * * * * * *
'move to next empty line after the inserted range
'line below does not work
'Selection.GoTo What:=wdGoToLine, Which:=wdGoToAbsolute, Count:=5
Selection.EndKey Unit:=wdStory
'* * * * * * * * * * * * * *
'free up selection on Excel
Application.CutCopyMode = False
'save the Word doc and close
ActiveDocument.SaveAs Filename:="FizzButt1.doc"
oDoc.Close
oWord.Quit
'Make sure you release object references.
Set oWord = Nothing
Set oDoc = Nothing
End Sub
I am using Office 2003 and am trying to have an interaction between
Excel and Word. All of my macro code works except for one area.
I have successfully created code in Excel which
creates a new Word doc and pastes from Excel a
specific range. By doing this, that range now
exists in the Word Doc as a table. What I next am
trying to do (but am not successfull) is to create an
empty line directly below that new table and have the
cursor positioned at the very beginning of the empty
line.
I have tried these three lines of code, but nothing works:
:banghead:
'Selection.TypeParagraph
'Selection.GoTo What:=wdGoToLine, Which:=wdGoToAbsolute, Count:=5
'Selection.EndKey Unit:=wdStory
Below is the macro code in its entirety, with the "troublesome area" showing between the two lines with asterisks.
Could you please review my macro and tell me what changes are
needed for this line creation and cursor placement to work?
Thank you very much for your help.
Ken
Sub PlaceExcelRangeIntoWord()
Dim oWord As Word.Application
Dim oDoc As Word.Document
'Get existing instance of Word if it's open; otherwise create a new one
On Error Resume Next
Set oWord = GetObject(, "Word.Application")
If Err Then
Set oWord = New Word.Application
WordWasNotRunning = True
End If
'copy range from Excel
Range("Sheet1!A1:E4").Copy
'create new Word doc
oWord.Visible = True
oWord.Activate
Set oDoc = oWord.Documents.Add
'adjust margins
With ActiveDocument.PageSetup
.TopMargin = InchesToPoints(0.5)
.BottomMargin = InchesToPoints(0.5)
.LeftMargin = InchesToPoints(0.5)
.RightMargin = InchesToPoints(0.5)
End With
'paste Excel range into Word doc
oDoc.Range.Paste
'* * * * * * * * * * * * * *
'move to next empty line after the inserted range
'line below does not work
'Selection.GoTo What:=wdGoToLine, Which:=wdGoToAbsolute, Count:=5
Selection.EndKey Unit:=wdStory
'* * * * * * * * * * * * * *
'free up selection on Excel
Application.CutCopyMode = False
'save the Word doc and close
ActiveDocument.SaveAs Filename:="FizzButt1.doc"
oDoc.Close
oWord.Quit
'Make sure you release object references.
Set oWord = Nothing
Set oDoc = Nothing
End Sub