PDA

View Full Version : Export Excel data to Word template



da_phat
11-17-2008, 08:56 PM
hi,
i am quite new in VBA programming. I would like to ask all the experts here to help me on implementing small and simple project.

I have an excel file which contain a data with column Name, Address 1, Address 2, Suburb, State, Postcode. i have about 200 data in each of the column. Then i would like to export the data in all the column into a customised word template then i would like to print the template. If there are 200 data, i would like to print 200 template which contain the data in specific field in the template.

Hereby i attach the sample of sample of word template. The data should be written on the box with the red oval.

Hope all the experts can help me here.

GTO
11-17-2008, 10:12 PM
Greetings da_phat,

Assuming you have the Excel data distributed as shown (if not, maybe show us), could you say where ea data is going?

I think partly this may be language, as I suspect suburb equals city, etc. But just to be sure for thick-headed guys like me, how about a description like:

Name = Thomas
Address1 =
Address2 =
...
Poct Code = 3500

Thank you so much and hope to help,

Mark

da_phat
11-17-2008, 10:27 PM
Hi,
thank you for replying. The description as follows

Name: Thomas
Address 1: Unit 14
Address 2 670-675 Dandenong Road
Suburb:Springvale
State:VIC
Postcode:3500

GTO
11-18-2008, 12:51 AM
Hi da_phat,

Since I already wrote in Early Binding (convenience), I included both late and early bound versions. Personally, I've never seen a noteable difference in speed, not to mention you'll be waiting on printing anyways, so I would suggest you just use the 'MailList_Create_LateBound()'.

This is certainly a bit of "rude and crude" coding, as I suspect that a mail merge might be more efficient; but, since I don't often use Word (programattically), I just installed some bookmarks in your template, and use them to insert the various text values.

Anyways, to use these, after unzipping the attachment, you must install both the workbook "Data2Export2WD.xls" and the Word Template "tpl.dot" in the same folder, as the workbook expects to find the template therein.

As you mentioned being new to VBA coding, I marked up (commented) the code up pretty well.

Hope this helps,

Mark

Sub MailList_Create_LateBound()
Dim _
oWord As Object, _
oDoc As Object, _
strFPath As String, _
bolWDCreated As Boolean, _
rngList As Range, _
iCol As Integer, _
rCell As Range

'// Initially set a range as a single cell, in this case, the cell in //
'// the last row in column B, that has a value in it. (B4, "Alicia Down//
'// in the example data) //
Set rngList = Sheet1.Range("B65536").End(xlUp)

'// Then reuse the variable, now setting the range to include from B2, //
'// to the last cell we already found. //
Set rngList = Sheet1.Range("B2", rngList)

'// Create a string variable with the path to this workbook (and hence, //
'// the Word Template). We include the last backslash "\" also, so it //
'// doesn't goober-up on us :-) //
strFPath = ThisWorkbook.Path & Application.PathSeparator

'// Now we temporarily set error handling to "in-line", meaning we'll //
'// handle errors while the code runs. We're doing this so that if an //
'// error is raised trying to 'GetObject' (see the vba Help topic), then//
'// we know that Word wasn't already running and we'll need to create it.//
On Error Resume Next
Set oWord = GetObject(Class:="Word.Application")

'// If an error raised, we'll clear it, and Create a new instance of Word//
If Err.Number > 0 Then
Err.Clear
Set oWord = CreateObject(Class:="Word.Application")

'// If we created Word, then we'll take note of this by setting a //
'// flag/boolean to True. We'll use this later. //
bolWDCreated = True
End If

'// Now we'll reset error handling //
On Error GoTo 0

With oWord
'// In case we Created Word, we'll make sure it's not hidden. //
.Application.Visible = True

'// Now since rngList was the range of cells that had names in them,//
'// we'll stop at each cell and get the values from each appropriate//
'// column. //
For Each rCell In rngList

'// Now for each record, we'll make a new temporary document, //
'// based on your template. //
Set oDoc = .Documents.Add(Template:=strFPath & "tpl.dot")

'// Since you have six (6) different bits of data to retrieve, //
'// we'll use Offset to grab the values, assigning these to the //
'// Text of each range in the document, using the bookmarks to //
'// set this range. //
For iCol = 1 To 6
oDoc.Range(oDoc.Bookmarks("bm_" & iCol).Range.Start, _
oDoc.Bookmarks("bm_" & iCol).Range.End).Text _
= rCell.Offset(0, iCol - 1).Value

Next iCol

'// Now we're back on the outer loop, so we'll print the created//
'// document, and close it w/o saving (ie - throw it out.). //
oDoc.PrintOut
oDoc.Close SaveChanges:=False

'// Then move to the next (name) cell in the workboook, til done. //
Next rCell

End With

'// If we Created the instance of Word, then we'll close it. //
If bolWDCreated Then oWord.Quit
'// Explicitly release our objects. //
Set oWord = Nothing
Set oDoc = Nothing
End Sub

da_phat
11-18-2008, 03:48 AM
hi mark,
thanks for helping.i did unzipped it under the same folder.I opened the excel workbook, then i run the macro.But it give me the error "user-defined type not defined". It was on the "oWord As Word.Application". Can you have a check for me please

GTO
11-18-2008, 03:57 AM
Hi da_phat,

You are almost there, you just didn't quite catch what I was saying, or I didn't explain it thoroughly enough.

Try this: Do NOT run the Sub "MailList_Create", instead, run the Sub "MailList_Create_LateBound".

I have to get to bed, so hopefully you get this quickly, and are able to respond within a few minutes.

Mark

da_phat
11-18-2008, 03:59 AM
i have run it...the problem is that...the application name one note appear instead of the template.The template does appeared with the name on it but it keep disappear..and leave microsoft one note open.Thanks for your help....im really looking forward to it

GTO
11-18-2008, 04:16 AM
...the problem is that...

the application name one note appear instead of the template.

The template does appeared with the name on it but it keep

disappear..and leave microsoft one note open...

I am sorry da_phat, but I do not understand the above three (bolded) phrases. Please try to describe he problem in a more detailed and accurate manner, as best you can.

Thank you so very much,

Mark

TO ALL: Could someone try the attachments in 2007? I doubt the causal factor, but in case...

da_phat
11-18-2008, 04:25 AM
sorry for bothering you.

When i click the run button on VBA windows, it run successfully and the "tpl.dot" appeared and the data inserted but suddenly disappeared again. After that "Micosoft OneNote" application appear and a window appear state "You cannot use this command while there is a dialog open in OneNote". Thats all happened and when i click "Ok" on the window, nothing happened except a running "Microsoft OneNote" application.

FYI, im using office 2007.

GTO
11-18-2008, 04:35 AM
Dear da_phat,

You are not bothering me a bit, and I thank you for responding so quickly. I do have to get some sleep, not to mention which, as I do not have 2007, I am slightly concerned tha this may be an issue as I have not heard of "OneNote".

I see several others logged on that have great knowledge and I believe also have 2007, so I imagine you'll get an answer quickly.

Good luck and thanks again for trying hard. Very nice to "meet".

Mark

da_phat
11-18-2008, 04:47 AM
hi mark,
i tried on my friend's computer, and guess what..it works...but i have small favour to ask you and you can reply it later.Actually the word template is just a trial. i thought i could make the adjustment later.But guess what, i failed to do it just now.Therefore i will upload the word template to you now, and hope you can change the code a bit for me. and 2 last favour, is it possible to make the file autorename for example, if i save it as test1.mdi, can it make it continue automatically with the name test2.mdi, test3.mdi, ..and so on.And the last favor is that, is it possible for you to make all the saved to document to automatically print.Thanks again mark.i really appreciate if you could help me with this.This is my first lesson on VBA..

GTO
11-18-2008, 10:12 PM
Hello da_phat,

The goal here is to offer and receive assistance thru discussion (a public forum, originally a meeting at the town's square). I am very happy to share what little I have learned, and quite often, learn by "stretching" myself a bit while assisting. But... I'm not writing it all for you, as you can certainly add bookmarks to a new template.

Open your .doc, SaveAs as a .dot (Template) to the folder the workbook is in.

Then add the same bookmarks that are in the other template, naming them the same ("bm_" and 1-6), at your desired locations.

As to saving them, offhand, I am unfamiliar with .mdi as an extension, other than maybe(?) a midi sound file.

Anyways, I used .doc as an extension, but you should be able to change this to suit.

In the code, find:

With oWord
'// In case we Created Word, we'll make sure it's not hidden. //
.Application.Visible = True

'// Now since rngList was the range of cells that had names in them,//
'// we'll stop at each cell and get the values from each appropriate//
'// column. //
For Each rCell In rngList

'//ADD THIS HERE//
intDocNumber = intDocNumber + 1

'// Now for each record, we'll make a new temporary document, //
'// based on your template. //
Set oDoc = .Documents.Add(Template:=strFPath & "tpl.dot")

'// Since you have six (6) different bits of data to retrieve, //
'// we'll use Offset to grab the values, assigning these to the //
'// Text of each range in the document, using the bookmarks to //
'// set this range. //
For iCol = 1 To 6
oDoc.Range(oDoc.Bookmarks("bm_" & iCol).Range.Start, _
oDoc.Bookmarks("bm_" & iCol).Range.End).Text _
= rCell.Offset(0, iCol - 1).Value

Next iCol

'// Now we're back on the outer loop, so we'll print the created//
'// document, and close it w/o saving (ie - throw it out.). //

'//ADD THIS (to suit)//
oDoc.SaveAs Filename:=strFPath & "Test_" & intDocNumber & ".doc"
oDoc.PrintOut
oDoc.Close SaveChanges:=False

'// Then move to the next (name) cell in the workboook, til done. //
Next rCell

End With

Remember to add at the start of the procedure:
Dim intDocNumber As Integer

Hope this helps,

Mark

da_phat
11-19-2008, 04:59 PM
hi Mark,
im really sorry if bothered you so much.Actually i have worked it out yesterday by copying the the table into the template.I just need a slight adjustment if you could help me. The code that you wrote for me is for a 4 line address. How can i change it to make it a 3 line address because some people have a 4 line address and some 3 line address.I will use the current code that you've written to me for 4 line address, and for 3 line address, could you guide me on what value should i change.I attached the sample picture.Thanks.

da_phat
11-19-2008, 05:24 PM
hi Mark,
i think i have figure it out.I change the bookmarks in msword.thanks a lot for helping.I really2 appreciate that.Is it possible if i can PM you if i have a another problem?

GTO
11-19-2008, 06:44 PM
Hello da_phat,

I'm glad you got it working. If you have another problem, please put it in the thread (assuming related to this project).

I am happy to receive any "howdy, how's it going" type PM.

Likewise, a "Hey, you were helping me on this, but I ran into another problem. Could you take another look at thread ######### ?" type PM is utterly fine.

But... in most cases, it's better that the actual work on the solution be done in the thread. This way, anyone else not familiar with a procedure or particular solution can benefit.

I want to reassure you that you were not bothering me. I am happy to be what little help/blessing I can be to someone's learning.

Further - and as aforementioned, I quite often am learning right along with the original poster. I simply didn't see a reason you couldn't put in a bit more effort. I did not mean to sound terse if you took it in that manner.

Again, glad you got the bookmarks figured out,

Mark