Solved

Export from Excel to Word

Posted on 2014-11-21
8
229 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:
SimonAdept 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:SimonAdept
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

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 article will shed light on the latest trends when it comes to your resume building needs. For far too long, the traditional CV format has monopolized the recruitment market.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free when…

760 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