PDA

View Full Version : Create separate word docs with data from xls



Neodubois
05-15-2014, 03:07 AM
Hello,

I have searched the forum and haven't found any identical thread.
I also tried to record this but all actions outside Excel are not recorded

I am using Excel and Word 2013.

I have a word document in a specific folder which I use as a template.
I have a Excel file and one of the tabs named "final information" contains the data I want to use in columns A and B, the number of rows is different every time.

I would likte to use a macro which would
1. open the word template
2. replace in 3 places in the word document data from colum C from Excel
3. "save as" the word document in a specific folder : the name should contain:
a. "INT." : followed by a 3 digit number starting at 1 example INT.001 for the first document INT.002 for the second one and so on
b. "INT.001" should be followed by a space than a "-" than a space than the data in column A1 followed by a space than a "-" than a space than the data in column for the first document from the Excel file
example INT.001 - red - table
4. save and close the word document created
5. start over unless the data in the next row of the Excel is empty. And of course us the Excel data from the next row and so on
example : doc 1 would be INT.001 - data A1 - data B1
doc 2 would be INT.002 - data A2 - data B2

I hope my request is clear and easy to understand and hope you will be able to help me out :)

Thank you

ranman256
05-15-2014, 08:08 AM
You create a word app in VBA...
(this can get complicated) You must have a reference to WORD app in VBA, you must know the Word Objects...like Excel has workbooks, worksheet, etc.




Set wordapp = CreateObject("word.Application")
wordapp.documents.Open "path\myDoc.doc"
wordapp.Visible = True
'do stuff here

snb
05-15-2014, 12:36 PM
Are you familair with mailmerge in Word ?
It's been designed to do exactly what you want.

Neodubois
05-16-2014, 01:31 AM
Thanks for the replies. Am not familiar with mail merge in word but will look into it.

I thought I had to create a macro in xls in order to do what I want.


Update : just checked. Not sure the mail merge would do what I want. And, I would like to be able to use this macro for other macro's by adapting the code.

Neodubois
05-16-2014, 01:42 AM
You create a word app in VBA...
(this can get complicated) You must have a reference to WORD app in VBA, you must know the Word Objects...like Excel has workbooks, worksheet, etc.




Set wordapp = CreateObject("word.Application")
wordapp.documents.Open "path\myDoc.doc"
wordapp.Visible = True
'do stuff here



Thanks I will start with this
The most complicated part will be the numbering and the iteration I suppose

snb
05-16-2014, 01:57 AM
It's not complicated at all if you use mailmerge in Word that has been designed for this purpose.

If you insist in reinventing the wheel:


Sub M_snb()
sn=thisworkbook.sheets(1).cells(1).currentregion

with getobject("G:\OF\example.docx")
for j=1 to ubound(sn)
.Variables("A1")=sn(j,1)
.Variables("A2")=sn(j,2)
.Variables(("A3")=sn(j,3)
.Fields.Update
.SaveAs2 "G:\OF\example_" & format(j,"000") & ".docx"
next
end with
End Sub

In the document "G:\OF\example.docx" you will have to insert fieldcodes for the documentvariables A1 - A3 first.

Neodubois
05-16-2014, 02:08 AM
Thanks again Master
Will try that