Export from Excel to Word

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
John SheehySecurity AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SimonCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
John SheehySecurity AnalystAuthor Commented:
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
John SheehySecurity AnalystAuthor Commented:
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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

SimonCommented:
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
John SheehySecurity AnalystAuthor Commented:
I am sorry, I didn't see where I could credit the points but I did mark your answer as an A.
0
John SheehySecurity AnalystAuthor Commented:
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
SimonCommented:
Hi John. Thanks for re-visiting this. You're not the only one who finds the changes confusing :)
0
John SheehySecurity AnalystAuthor Commented:
I posted another question out for the same project if you want to take a look at that one.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Office Productivity

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.