Solved: Using Excel To Perform a Word Mailmerge

[Trubble]

Hi there,

I'm working on a little project to automate a mailmerge from within Excel.

I'm trying to create several buttons that do different tasks on my Excel sheet.

I have one that Prints the document, (nice and straight forward)

[vba]Private Sub PrintButton_Click()
Worksheets("INPUT FORM").PrintOut Copies:=1, Preview:=True, Collate:= _
True
End Sub[/vba]

One that saves the Excel sheet as a name based on some cells values (using a macro that I found)

[vba]Private Sub SaveButton_Click()

Dim vFile As Variant
Dim sName As Variant

PrimaryName = ActiveSheet.Range("B1")
CMN_Num = ActiveSheet.Range("D1")

sName = "A" & CMN_Num & " - " & PrimaryName & " - Overview Sheet.xls"

vFile = Application.GetSaveAsFilename(InitialFileName:=sName, _
fileFilter:="Excel files (*.xls), *.xls", _
Title:="")
If vFile <> False Then
ThisWorkbook.SaveAs Filename:=vFile
Else
MsgBox "Not a valid path" 'cancel
End If

End Sub[/vba]

and the tricky one - one that will open a word mailmerge document and merge with the previously saved Excel sheet.

My question is (well my first question).... how do I get the name of the file path that I saved the spreadsheet to(C:\blah blah\blah), so that word knows where the source data is?

What I am managing to do is open Word and autorun a macro, but the macro needs the information (C:\blah blah\blah). This is where I'm stuck.

Sorry if my post seems a bit vague, this is all kind of new to me.

Thanks much in advance

T

[lucas]

Maybe something like:
[VBA]
Dim FName As String

FName = "f:\AAA\Data1.xls"
Set wb = objExcel.Workbooks.Open(FName)
[/VBA]

[Trubble]

Thanks for that.

Maybe I?ve been looking at it from the wrong side.

Would it be easier to have Word get the name and file path of the open Excel Spreadsheet or have Excel ?pass? the name and file path to Word?

I thought that seeing as I have a function already in Excel to save the spreadsheet to specific name, the variable is already there and I just need Word to know what it is?.

So I guess that what I want is for Excel to pass Word a variable that Word can then use (its going to be the source file for a mail. Is that easy to do or straight forward?

Cheers

Trubble

[mdmackillop]

Have a look at these KB items
http://vbaexpress.com/kb/getarticle.php?kb_id=256
http://vbaexpress.com/kb/getarticle.php?kb_id=122