Solved

Export from Excel to Word

Posted on 2014-11-21
8
242 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
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

Manage your data center from practically anywhere

The KN8164V features HD resolution of 1920 x 1200, FIPS 140-2 with level 1 security standards and virtual media transmissions at twice the speed. Built for reliability, the KN series provides local console and remote over IP access, ensuring 24/7 availability to all servers.

Question has a verified solution.

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

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
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…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

739 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