Trubble
10-18-2006, 10:44 AM
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)
Private Sub PrintButton_Click()
Worksheets("INPUT FORM").PrintOut Copies:=1, Preview:=True, Collate:= _
True
End Sub
One that saves the Excel sheet as a name based on some cells values (using a macro that I found)
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
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. :dunno
Thanks much in advance
T
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)
Private Sub PrintButton_Click()
Worksheets("INPUT FORM").PrintOut Copies:=1, Preview:=True, Collate:= _
True
End Sub
One that saves the Excel sheet as a name based on some cells values (using a macro that I found)
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
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. :dunno
Thanks much in advance
T