Solved: Moving data to a Word document

I'm trying to move the data from a Excel spreadsheet to a Word document. I need to be able to automatically move all the rows in a spreadsheet to a word document but only certain columns in the row need to be moved. I will never now how many rows will be in the spreadsheet could be 5 could be 50. This information needs to go into a certain location in the Word document. Need to figure out how to do this, on the Excel side or the Word side. I'm looking for ideas on how to go about this. Also the Excel spreadsheet would have more then one worksheet and each worksheet would go on a different section of the Word document. Any ideas would be apreciated.

Thanks in advance.

Many questions arise.

You say specific location in a word doc.....does that word doc exist and what is the specific location? Is it a bookmark or can it be bookmarked?

then you go on to say that you want to put each worksheet in a different section......does that mean page break? Why would you need to do this if you are putting the data into a specific location in the doc.

Just to get you started here is code that will copy each sheet to a new page of a new word doc. It should give you some ideas and probably raise more questions.

please read the comments in the first two lines of the code before using it.

[VBA]Option Explicit
Sub CopyWorksheetsToWord()
' requires a reference to the Word Object library:
' in the VBE select Tools, References and check the Microsoft Word X.X object library
Dim wdApp As Word.Application, wdDoc As Word.Document, ws As Worksheet
Application.ScreenUpdating = False
Application.StatusBar = "Creating new document..."
Set wdApp = New Word.Application
Set wdDoc = wdApp.Documents.Add
For Each ws In ActiveWorkbook.Worksheets
Application.StatusBar = "Copying data from " & ws.Name & "..."
ws.UsedRange.Copy ' or edit to the range you want to copy
wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.InsertParagraphAfter
wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.Paste
Application.CutCopyMode = False
wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.InsertParagraphAfter
' insert page break after all worksheets except the last one
If Not ws.Name = Worksheets(Worksheets.Count).Name Then
With wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range
.InsertParagraphBefore
.Collapse Direction:=wdCollapseEnd
.InsertBreak Type:=wdPageBreak
End With
End If
Next ws
Set ws = Nothing
Application.StatusBar = "Cleaning up..."
' apply normal view
With wdApp.ActiveWindow
If .View.SplitSpecial = wdPaneNone Then
.ActivePane.View.Type = wdNormalView
Else
.View.Type = wdNormalView
End If
End With
Set wdDoc = Nothing
wdApp.Visible = True
Set wdApp = Nothing
Application.StatusBar = False
End Sub
[/VBA]

Bookmarks can be a good way to poke your xls data into word. See http://www.vbaexpress.com/kb/getarticle.php?kb_id=126 for the bookmark method.

Here are a few links for one method:
'Add Table to MSWord
http://vbaexpress.com/forum/showthread.php?t=23975
http://vbaexpress.com/forum/showthread.php?p=168731

The Word Document would exist and each section that would have data inserted into would have a text field or a bookmark. Probably a bookmark since a text field may be limited in the amount of data it can accept. I will look into this further with the information here.

The Word document would not necessarily have different sections but would have different paragraphs where each worksheet would be inserted into. Paragraph one would be followed by the data of worksheet 1 and would be input into a bookmark or text field which ever would be best to use. Paragraph 2 would be followed by the data in worksheet 2 and so forth. Each would have a bookmark or text field that would be location for data to go into.

If you are stuck and need more help, make a simple DOC with the bookmarks and an XLS with like-named worksheets and attach.

Lucas, how would you get it to copy only specific columns and if possible would it copy them in the order you specify or would it copy them in the order they fall in the table regardless of the code.

Attached files are samples of data and word document using.

The Spreadsheet.

By specifying the column numbers in an array, you can assemble the data in any order required. Can the Bookmark names be included in the spreadsheet, or how are these locations decided?

No attachments. If the files are too big, you could try zipping them together.

I can't help with 2007 files anyway. I guess I'm going to have to bite the bullet......

2000 - 2003 version of document

2000 - 2003 version of spreadsheet

Your DOC file is using bookmark names like "Code3" and your XLS is using sheet names like "Supp Code 5". While we can work with this, we will have to account for the case where the worksheet name does not match a bookmark name at all.

Lucas, I use 2003 only as well. The converter let me see what he posted. I will save the files as DOC and XLS files. The names can be changed later to use 2007 format names.

So, I could do it but I am not sure what you want in the bookmarks now. I would have gone with the UsedRange but you seem to want some discontinuous range now. As mdmackillop said, we can do that but need to know what ranges you want specifically.

The data file is never the same. This information is pulled based on certain criteria and I am given the spreadsheet. From the spreadsheet I have to generate the Word Document. So it would be best to use the usedrange. While the worksheet names do not match the bookmarks they can be made to match. This is only a sample there there would be many more worksheets. But I would like to be able to put the Supp Code 3 data in the Code3 bookmark and so forth.

Kenneth, there are a set number of codes (what is used to name the Worksheet and the bookmark, Supp Code 3 = Code3) and all would be used some may not have data in them though as criteria would retrieve no data. But if the spreadsheet has a worksheet for each code and the Word document has a bookmark for each code can it be written so tht it goes from each worksheet to its corresponding bookmark in the Word document. Once the move is over some cleanup would be done in the Word document to remove the areas that have no data. So if you write it so that those were the only codes I could add the rest myself.

You won't need all of this.

Post this to its own Module or at the end of the main code after this. You will need to add the references as I commented. You can later delete the getClipboard Sub and remove the reference since I just used it in a commented part where I used the Sub TextInBName. That method can be deleted as well. I left these in to show you that bookmark method.

[VBA]'Similar to Steiner, http://www.vbaexpress.com/kb/getarticle.php?kb_id=126
'Requires Tools > References...> MicroSoft Word 11.0 Object Library
Sub TextInBName(ByRef WDoc As Word.Document, ByVal BName As String, ByVal TextIn As String)
With WDoc
If .Bookmarks.Exists(BName) Then
Dim r As Word.Range
Set r = WDoc.Bookmarks(BName).Range
r.Paste
r.Text = TextIn
WDoc.Bookmarks.Add BName, r
Else
Debug.Print "Bookmark not found: " & BName
End If
End With
End Sub


Sub PasteAfterBName(ByRef WDoc As Word.Document, ByVal BName As String)
With WDoc
If .Bookmarks.Exists(BName) Then
Dim r As Word.Range
Set r = WDoc.Bookmarks(BName).Range
r.Paste
Else
Debug.Print "Bookmark not found: " & BName
End If
End With
End Sub

Function getClipboard()
'Requires Tools > References... > Microsoft Forms 2.0 Object Library
Dim MyData As DataObject

On Error Resume Next
Set MyData = New DataObject
MyData.GetFromClipboard
getClipboard = MyData.GetText
End Function
[/VBA]


Here is main part. You will need to modify the part where I set the DOC and copied DOC filenames. You can delete the 2 commented lines with TextInBName. Or, uncomment them and comment the PasteAfterBName line to see the difference in the 2 methods.
[VBA]Sub FillForm()
'Dim WDApp As Object, wd As Object 'Late Binding
Dim WDApp As New Word.Application, wd As Word.Document 'Early binding
Dim rn As Long 'Used for formfield method
Dim fn As String, fn2 As String
Dim ws As Worksheet, r As Excel.Range
Dim suffixNum As Integer, a

rn = ActiveCell.Row
On Error Resume Next
Set WDApp = GetObject(, "Word.Application") 'Late Binding
If Err.Number <> 0 Then
Set WDApp = CreateObject("Word.Application") 'Late Binding
End If
On Error GoTo 0

'************* Set your own DOC filename and path and copied path/filename ****************
fn = ThisWorkbook.Path & Application.PathSeparator & "Letter.doc"
fn2 = ThisWorkbook.Path & Application.PathSeparator & "Letter1.doc"
FileCopy fn, fn2
Set wd = WDApp.Documents.Open(fn2)

WDApp.Visible = True
With wd
'Example FormFields method:
'.FormFields("Brand").Result = Cells(rn, "B")
For Each ws In Worksheets
'TextInBName(ByRef WDoc As Word.Document, ByVal BName As String, ByVal TextIn As String)
a = Split(ws.Name)
ws.UsedRange.Copy
'TextInBName wd, "Code" & a(UBound(a)), getClipboard
PasteAfterBName wd, "Code" & a(UBound(a))
Next ws
End With
Application.CutCopyMode = False

Set wd = Nothing
Set WDApp = Nothing
End Sub[/VBA]

I figured out how to get the data from the spreadsheet to the Word document I created. So now I have the data from each worksheet going to the proper paragraph. I would like to keep this open incase I run into some problems I can not work my way through. Lucas the code you sent worked great after some modifications. Thanks for that snipet of code, made a big difference.

When the copying is complete the Word document has the data in a table. Is there a way to format the table so that it goes from margin to margin and the first column is a specific width and the second column the remainder of the width. I have no idea how this would get done.

This is the final version of the code I used in the spreadsheet.

[vba]
Sub CopyWorksheetsToWord()
' requires a reference to the Word Object library:
' in the VBE select Tools, References and check the Microsoft Word X.X object library
Dim wdApp As Word.Application, wdDoc As Word.Document, ws As Worksheet
Application.ScreenUpdating = False
Application.StatusBar = "Opening Word Document..."
Set wdApp = New Word.Application
Set wdDoc = wdApp.Documents.Open("C:\Documents and Settings\Dominguc\My Documents\Letter.dot")
For Each ws In ActiveWorkbook.Worksheets
Application.StatusBar = "Copying data from " & ws.Name & "..."
ws.UsedRange.Copy ' or edit to the range you want to copy
wdDoc.Bookmarks(ws.Name).Range.Paste
Application.CutCopyMode = False
Next ws
Set ws = Nothing
Application.StatusBar = "Cleaning up..."
Set wdDoc = Nothing
wdApp.Visible = True
Set wdApp = Nothing
Application.StatusBar = False
End Sub
[/vba]

This is the final version of the code in the spreadsheet:

Sub CopyWorksheetsToWord()
' requires a reference to the Word Object library:
' in the VBE select Tools, References and check the Microsoft Word X.X object library
Dim wdApp As Word.Application, wdDoc As Word.Document, ws As Worksheet
Application.ScreenUpdating = False
Application.StatusBar = "Opening Word Document..."
Set wdApp = New Word.Application
Set wdDoc = wdApp.Documents.Open("C:\Documents and Settings\Dominguc\My Documents\Letter.dot") '.Add
For Each ws In ActiveWorkbook.Worksheets
Application.StatusBar = "Copying data from " & ws.Name & "..."
ws.UsedRange.Copy ' or edit to the range you want to copy
wdDoc.Bookmarks(ws.Name).Range.Paste
Application.CutCopyMode = False
Next ws
Set ws = Nothing
Application.StatusBar = "Cleaning up..."
Set wdDoc = Nothing
wdApp.Visible = True
Set wdApp = Nothing
Application.StatusBar = False
End Sub

I just thought of something with regards to the code. This code sits on the spreadsheet and I was trying to put the code on Excel so that the user could run it after opening up the new spreadsheet that will get sent on a regular basis with new data. Does Excel have a global location where the data can be put and then the user can run it on the new spreadsheet. Sort of like the Normal.dot in Word.