Solved

Export from Excel to Word

Posted on 2014-11-21
8
234 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
 
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Companies keep a much closer eye on costs today, so changing to new Technology – Microsoft Office 365 is the smartest move to take.
An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

911 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now