PDA

View Full Version : Exporting defined ranges into word



atlas_85
12-13-2007, 01:10 AM
Hi Everyone,

Can anyone help write a macro which can do the following?

I have 10 worksheets called E1, E2, E3 -> E10 and 10 ranges are called Element_1, Element_2, Element_3 -> Element_10 on the respective sheets (ie. Element_1 range is on sheet E1).

What I would like is for a macro to automatically copy the various ranges into an existing word template (at the beginning) , and ideally, close excel without saving changes and display the newly created word document.

The word document would ideally be displayed in the order of

Element_1
Element_2
Element_3
|
V
Element_10

Does anyone know a code that may be able to do this? I've been trying to write one myself but I've never had to write a code connecting different programs, the codes I write are fully excel based.

Any help would be mostly appreciated.

Thanks,

Andrew

Bob Phillips
12-13-2007, 02:17 AM
Sub ToWord()
Dim WordApp As Object
Dim WordDoc As Object
Dim i As Long
Dim sh As Worksheet
Dim cell As Range

Set WordApp = CreateObject("Word.Application")
Set WordDoc = WordApp.documents.Add
WordApp.Visible = True

i = 1
For Each sh In Worksheets(Array("E1", "E2"))

For Each cell In sh.Range("Element_" & i).Cells
WordDoc.content.insertafter cell.Value & vbNewLine
Next cell
i = i + 1
Next sh

Set WordDoc = Nothing
Set WordApp = Nothing
Application.Quit

End Sub