Excel Transfer To Word

[brettvba]

I know absolutly nothing about word can someone help me I have a workbook with some address fields in it.

What I want to do is create a macro that opens up a new letter in word and puts in the address fields selected in excel.

Any Ideas?

[sen_edp]

Hello Bretvba

I know that word has that option , under tools,mail merge , where you can setup
this communication with excel.

[brettvba]

yeah,

What I really want is just a copy and paste function to put the address in the right spot.

Im using forms and when a user clicks the details they want, I want to open a new document and put in the relevant data in the form of a letter

Any Ideas??

[NateO]

You could just copy and paste for a one time process. But a macro could look like the following:

Sub WordUp()

Dim WdObj As Object, fname As String

fname = Sheets(2).[a1].Value

Set WdObj = CreateObject("Word.Application")

WdObj.Visible = False

Sheets(1).UsedRange.Copy 'Your Copy Range

WdObj.documents.Add

WdObj.Selection.Paste

Application.CutCopyMode = False

If fname <> "" Then 'make sure fname is not blank

With WdObj

    .ChangeFileOpenDirectory "c:temp" 'save Dir

    .ActiveDocument.SaveAs Filename:=fname & ".doc"

End With

Else:

MsgBox ("File not saved, naming range was botched, guess again.")

End If

With WdObj

    .ActiveDocument.Close

    .Quit

End With

Set WdObj = Nothing

End Sub

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-05-22 19:17 ]

[brettvba]

Thanks Nate

I think I can work that to fit my needs

Cheers

[brettvba]

sorry i cannot get what I want to work.

What I am trying to achieve is when the macro is run the different fields get transfered from excel into a word document.

I am not sure what I would need to complete this any more Ideas??

[NateO]

When you say it doesn't work, what do you mean? E.g., errors, nothing, wrong range, etc.....

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-05-22 18:37 ]

[brettvba]

hi Nate

could not get the code to work its also kinda not what Im hoping to do what Ive got is a form with address's on it when the user clicks an address and clicks the create letter button I want it to
Put the required fields in the letter,
I can get the information in variables
but I want to insert the information into word so the user can then create a letter hopefully without copy and paste.

Can you reference lines and columns in word?

Hope thats clear enough can you help?

[Mark O'Brien]

I love the subroutine name there, Cameo.

"Yo, pretty ladies around the world..."

(I've just turned into AudioJoe"

[Nimrod]

Hi Bret.
There Probably is a better way of doing this but it worked for me ....
I was able to select a name in excel and have a form letter open in word with the appropriate information in it ... is that what you want ? If soooo then this is how I did it ....

1) created a extra sheet (you may be able to hide it ) in my xls workbook
2) Put the appropiate headers on the blank xls sheet I.e. name address etc , and added one line of persons information
2)I then wrote a macro to copy the selected row to the "spare" spreadsheet. AND then Open a Word Document called MyFormLetter.

HOW TO CONFIGURE myFormLetter PRIOR to running macro.
1) Open up Word and Open Mail Merge
2) Use Mail Merge wizard to point to the spreadsheet that is blank except for header and 1 line of information.
3) Create you formletter with the one line of information from xls sheet.
4) Save file with 1 line of info showing

DONE:
Now to use, click on the source Spreadsheet which will A) copy the one row of info to the Row One of the sheet that Mail Merge uses for dataBase. B) Opens the Mail Merge document call MyFormLetter with the appropriate information in place.