Solved

Export from Excel to Word

Posted on 2014-11-21
8
236 Views
Last Modified: 2014-11-25
I would like to be able to export certain columns out to a word document, but one document for each row.
For example.  The columns that contain the data I want exported are C, E, F, and G.  Each row must be a separate document with Cell C being the documents name.

I have 4 sheets that this needs to happen on

Here is what I have so far and I am not sure I am on the right path.

Sub test()
Dim objWord As Object
Dim ws As Worksheet
Dim LastRow As Long, i As Long
Dim FileName As String, strPath As String


Set ws = ThisWorkbook.Sheets("CNE")
LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row


For i = 2 To LastRow


Set objWord = CreateObject("Word.Application")


objWord.Visible = True


'objWord.Documents.Open "D:\nic\Procedura inspectie 5010 revizie 05.06.2013.docx"
objWord.Documents.Open "C:\Users\J34396\Documents\Sample_CO.docx"


With objWord.ActiveDocument
.Bookmarks("Book1").Range.Text = ws.Range("A" & i).Value
.Bookmarks("Book2").Range.Text = ws.Range("E" & i).Value
.Bookmarks("Book3").Range.Text = ws.Range("F" & i).Value
.Bookmarks("Book4").Range.Text = ws.Range("G" & i).Value


strPath = "C:\Users\J4396\Documents\COs" & ws.Range("C" & i).Value & ".docx"


objWord.ActiveDocument.SaveAs FileName:=strPath, _
AddToRecentFiles:=False
objWord.Quit False


End With


Next i
Set objWord = Nothing


End Sub

Thanks

John
0
Comment
Question by:John Sheehy
  • 5
  • 3
8 Comments
 
LVL 18

Accepted Solution

by:
Simon earned 500 total points
ID: 40458746
You're definitely on the right lines. I'd suggest that you switch these two lines of code:
For i = 2 To LastRow
Set objWord = CreateObject("Word.Application")

Open in new window

so that you only open the application once, and instead of quitting it after each document, just save and close the document then open the original again at the start of the next iteration of the loop.

I tested this version and it works on my machine after I created a simple Word doc with bookmarks in and setting the document path to it.
Sub test()
Dim objWord As Object
Dim ws As Worksheet
Dim LastRow As Long, i As Long
Dim FileName As String, strPath As String

Set ws = ThisWorkbook.Sheets("CNE")
LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row

Set objWord = CreateObject("Word.Application") 'Open the application ONCE before the start of the loop

For i = 2 To LastRow

objWord.Visible = True

objWord.Documents.Open "C:\Users\J34396\Documents\Sample_CO.docx"


With objWord.ActiveDocument
.Bookmarks("Book1").Range.Text = ws.Range("A" & i).Value
.Bookmarks("Book2").Range.Text = ws.Range("E" & i).Value
.Bookmarks("Book3").Range.Text = ws.Range("F" & i).Value
.Bookmarks("Book4").Range.Text = ws.Range("G" & i).Value


strPath = "C:\Users\J4396\Documents\COs" & ws.Range("C" & i).Value & ".docx"

objWord.activedocument.SaveAs FileName:=strPath, AddToRecentFiles:=False
objWord.activedocument.Close ' close instead of quitting
End With
Next i
objWord.Quit False 'quit after processing all of the files.
Set objWord = Nothing
End Sub

Open in new window

0
 

Author Comment

by:John Sheehy
ID: 40464526
That is a great idea.  Sorry it took so long for me to respond.
My issue when I started this was the .bookmarks("book1") statement.  I had no idea they were the book marks in the word document.  So when I figured that out it ran as expected but your suggestion will help with the annoying opening and closing.

I have a few more questions but I will post them separately

Thanks
John
0
 

Author Comment

by:John Sheehy
ID: 40464955
I've requested that this question be closed as follows:

Accepted answer: 0 points for John's comment #a40464526

for the following reason:

The suggestion offered was right on point and is very extremely helpful in my new endeavor

John
0
Easy, flexible multimedia distribution & control

Coming soon!  Ideal for large-scale A/V applications, ATEN's VM3200 Modular Matrix Switch is an all-in-one solution that simplifies video wall integration. Easily customize display layouts to see what you want, how you want it in 4k.

 
LVL 18

Expert Comment

by:Simon
ID: 40464956
As I'm newly returned to EE, I'm not sure whether to object to this or not...

The suggestion offered was right on point and is very extremely helpful in my new endeavor

John

I _think_ the asker meant to credit me for my suggestions. I did take the time to mock up a spreadsheet and Word doc to test his code.

However, I'm not too precious about it. If the asker felt that they solved it themselves because they figured out the .bookmarks issue, I'm OK with that. I'm objecting mainly to find out more about the EE landscape and whether this should just be a case of 'roll with it'.
0
 

Author Comment

by:John Sheehy
ID: 40465174
I am sorry, I didn't see where I could credit the points but I did mark your answer as an A.
0
 

Author Comment

by:John Sheehy
ID: 40465187
Even when I went back into the question it only says the question is 500 points but doesn't say how I can assign them.  So I accepted your answer  Not sure how this is working.  Every time they re-do the site I always get confused.

John
0
 
LVL 18

Expert Comment

by:Simon
ID: 40465304
Hi John. Thanks for re-visiting this. You're not the only one who finds the changes confusing :)
0
 

Author Comment

by:John Sheehy
ID: 40465325
I posted another question out for the same project if you want to take a look at that one.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Meetings to discuss business process can waste time, and often do .  The meeting's dialog can get confusing when participants have different professional perspectives and backgrounds.  A jointly-developed process picture helps wade through the confu…
Today companies are subjected to more-and-more data, and it won't stop any time soon.  But there are obvious opportunities for reducing data, particularly data duplicated among companies.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question