PDA

View Full Version : Macro to export cell values to Word



dn1509
06-10-2010, 04:15 AM
Dear all,

I am new to this forum so forgive me if this should go in a different forum. Excel is the main or 'host program in what I am trying to achieve so I felt it best fit here.

I have a spreadsheet which is nothing fancy at all - basic columns with manually entered information. I want to be able to highlight one row across 4 columns and have a macro button which will take the information and insert it into certain locations on a pre-existing Word document. I do not need it to save at this point (though this is functionality I may wish to incorporate later).

For example, say columns A, B, C, and D hold my data. I then want to highlight A1 : D1 and click the macro button. This will take the cell values and insert them into the word document as follows:

----------------
Dear A1,

Thank you for your letter dated B1. Your query has been forwarded to our C1 department for further investigation. Should you have any queries please contact your HR representative, D1, who will be happy to assist you.

Regards,

Dominic
----------------

Please note this is a very crude and fundamental example of what I am doing, but there is no need to go into great detail and hopefully this gives you the idea of what I am trying to achieve.

If I need to be clearer on something then please do let me know.

Any help you can offer greatly appreciated!

Regards,

Dominic

Bob Phillips
06-10-2010, 04:24 AM
Insert bookmarks in the fieds in the word document that you want to update, then just use Excel to startip Word, open the document, and insert the cell values into the bookmarks.

lynnnow
06-10-2010, 05:43 AM
xld, wouldn't mail merge be a better option? I mean if he is aware of the positions where the text goes, the form letter can be modified to suit, and later edited as per needs. I don't dispute bookmarks can be used with Excel being the host application, however, using Word as the host and utilizing mail merge is an easier way to go. Chances are that bookmarks can inadvertently be deleted either by creator or end user and this can throw the Excel macro out of whack. With mail merge, there are fewer chances of that happening. Just wondering if it can be done like this. Sorry if I've questioned your methods.

Also, going by the example provided by Dominic there will have to be a loop that goes through all or some of the records. A mail merge option would allow the flexibility at any time to select which records need to be output and which don't need to be output.

Bob Phillips
06-10-2010, 07:56 AM
I suppose maile merge is an option, I must admit I don't like mail merge, it is a clumsy implementation, I prefer to control it. Let' see what the OP comes back with.

mdmackillop
06-10-2010, 10:59 AM
I'm sure there are examples of this in the KB.

Kenneth Hobs
06-11-2010, 07:59 AM
Try: http://vbaexpress.com/forum/showthread.php?p=185718

dn1509
06-11-2010, 07:59 AM
Thank you for your ideas thus far.

Looking through other threads I have managed to throw together this code, using the placeholder [REFNO] in my Word document:

Sub automateword()
Dim WordApp As Object
Dim DocWord As Word.Document
Set WordApp = CreateObject("word.Application")
Set DocWord = WordApp.Documents.Open("P:\Folder\File.doc")
WordApp.Visible = True
Set myRange = DocWord.Content
myRange.Find.Execute FindText:="[REFNO]", ReplaceWith:=Cells(9, 1).Text

End Sub

This works great, however I am unable to integrate more find/replaces. I want the following integrated also. Can anyone help me just put these into my code so all 5 placeholders are replaced?

myRange.Find.Execute FindText:="[NAME]", ReplaceWith:=Cells(9, 2).Text
myRange.Find.Execute FindText:="[DOB]", ReplaceWith:=Cells(9, 3).Text
myRange.Find.Execute FindText:="[PPNO]", ReplaceWith:=Cells(9, 4).Text
myRange.Find.Execute FindText:="[DATE]", ReplaceWith:=Cells(9, 5).Text

Any help would be much appreciated!

Thanks.

GTO
06-11-2010, 08:59 AM
Greetings Dominic,

Not sure how much help I'd be at Word, but maybe use a template. It appears you are using text to find/replace like I used to in WordPerfect.

Anyways, with the template in the same folder as the workbook, try:


Option Explicit

Sub exa()
Dim WD As Object '<---Word.Application
Dim DOC As Object '<--- Word.Document

On Error Resume Next
Set WD = GetObject(, "Word.Application")
If Err.Number > 0 Then
Set WD = CreateObject("Word.Application")
End If
On Error GoTo 0

With WD
.Visible = True
Set DOC = .Documents.Add(Template:=ThisWorkbook.Path & "\MyTemplate.dot", _
NewTemplate:=False, DocumentType:=0)
With .Selection
.Find.ClearFormatting
.Find.Replacement.ClearFormatting
With .Find
.Text = "[REFNO]"
.Replacement.Text = Cells(9, 1).Text
.Forward = True
.Wrap = 1
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
.Find.Execute Replace:=2

.Find.Execute FindText:="[NAME]", ReplaceWith:=Cells(9, 2).Text, Replace:=2
.Find.Execute FindText:="[DOB]", ReplaceWith:=Cells(9, 3).Text, Replace:=2
.Find.Execute FindText:="[PPNO]", ReplaceWith:=Cells(9, 4).Text, Replace:=2
.Find.Execute FindText:="[DATE]", ReplaceWith:=Cells(9, 5).Text, Replace:=2
End With
End With
End Sub


Hope that helps,

Mark

Bob Phillips
06-11-2010, 09:08 AM
Use bookmarks as I suggested earlier. It is trivial then



Doc.Bookmarks("bmk_A1").Range.Text = Activesheet.Range("A1").Value