Solved: Mail merge from any excel file name to word template...possible?

[psctornado]

Hi,

I currently have a an excel spreadsheet where a user completes a series of fields and then triggers a macro which then opens word and completes the merge. The current setup requires the excel file to save to say : generator.xls, the word doc has the following code in it. What I'm looking for is if the code below could be modified so that if at a later point the merge has to be rerun that the file does not have to be named to generator.xls like it was originally, but rather any file name. I wasn't sure if this was possible or not...thoughts???:dunno :dunno

Btw I'm working with office 2003 if that makes any difference in the help...Thank you!!!

Word VBA below:Private Sub Document_Open()
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource Name:= _
"I:\Generator.xls", _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=I:\Generator.xls;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Pass" _
, SQLStatement:="SELECT * FROM `Master2$`", SQLStatement1:="", SubType _
:=wdMergeSubTypeAccess
ActiveDocument.MailMerge.ViewMailMergeFieldCodes = wdToggle
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
Application.WindowState = wdWindowStateMaximize
Windows("Details Template.doc").Activate
ActiveWindow.Close SaveChanges:=wdDoNotSaveChanges
End Sub

[macropod]

A mailmerge cannot use 'any' filename - it needs a specific filename. To that end, you might try something along the lines of:
Private Sub Document_Open()
Dim StrSrc As String
With Application
.DisplayAlerts = wdAlertsNone
.ScreenUpdating = False
With .FileDialog(FileDialogType:=msoFileDialogFilePicker)
.AllowMultiSelect = False
SendKeys "%n %n"
SendKeys "I:\Generator.xls"
If .Show = False Then GoTo Finished
StrSrc = .SelectedItems(1)
End With
End With
With ThisDocument.MailMerge
.MainDocumentType = wdFormLetters
.OpenDataSource _
Name:=StrSrc, ReadOnly:=True, AddToRecentFiles:=False, LinkToSource:=False, _
ConfirmConversions:=False, Revert:=False, Format:=wdOpenFormatAuto, _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=StrSrc;" & _
"Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";" & _
"Jet OLEDB:Registry Path="""";Jet OLEDB:Database Pass", _
SQLStatement:="SELECT * FROM `Master2$`", SQLStatement1:="", SubType:=wdMergeSubTypeAccess
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
.MainDocumentType = wdNotAMergeDocument
End With
Finished:
With Application
.DisplayAlerts = wdAlertsAll
.WindowState = wdWindowStateMaximize
.ScreenUpdating = True
End With
ThisDocument.Close SaveChanges:=wdDoNotSaveChanges
End Sub
The above code will require the data source to be selected each time, not just when the code is re-run, but defaulting to "I:\Generator.xls".

Note that with this approach your mailmerge main document will not give the usual mailmerge SQL warning and needn't be saved as a mailmerge main document. You might also want to comment-out the 'ThisDocument.Close' line for testing.

[psctornado]

Hi Paul,

Thanks for the code. While it works, I see what you mean about it prompting for the file name. So even though in the code it references the Mail merge source 'Master2', there is no workaround for the naming issue?

I guess what I was hoping is that provided the source tab :

SQLStatement:="SELECT * FROM `Master2$`", SQLStatement1:="", SubType _

would be constant throughout any name the xls has, I was hoping it could simply be looking for that in the VBA and not so much the xls file name.

Any other thoughts you may have???

Thanks again!

[macropod]

One can do the same kind of thing for 'Master2$', though you'd probably want to use a userform dropdown to give the user only the valid choices, with the userform populated by testing the workbook for worksheet and/or range names. Otherwise, how would the user decide what to use?

[psctornado]

Sorry for the delayed response, I've been thinking over your last post and came up with another possible thought. Instead of the prompt as we've discussed previously...is it possible to have the xls file save to the default location with the default name?

Example: Opened file is xyz.xls, when launching the mail merge macro from xyz.xls could that macro also save a copy of xyz.xls to the default name of 'generator.xls' in location : I:\Generator.xls ?

I appologize if this is now more of an excel question, but since its related specifically to mail merge I thought it would be ok. I guess with the above idea I wouldn't have to have the user select the source since its doing it all in the 'background' so to speak. The merge would then read from the newly created generator.xls file...Please let me know your thoughts on this....

Oh also...the xyz.xls could have a different file name ...meaning that I'm thinking the xyz.xls could be pqr.xls or honey.xls etc...but still save in the I directory with the generator.xls file name...

Thanks again for all the help!!:banghead:

[psctornado]

I was able to figure a work around by using the following code :

Sub Save()
ActiveWorkbook.SaveCopyAs Filename:="I:\Templates\Generator.xls"
End Sub

I was originally trying to do on a local drive C:My Documents folder, this proved to a bit difficult though in my word VBA to find the 'source doc'. Below is what I had in my excel vba, and it put the Generator.xls in the my documents\Templates folder, but when trying to get the VBA updated in the word doc, that seemed a bit more difficult.

The excel vba was :
Sub Save()
ActiveWorkbook.SaveCopyAs CreateObject("WScript.Shell").SpecialFolders("MyDocuments") & "\Templates\Generator.xls"
End Sub

Any thoughts of how to modify the word vba to pull from the my documents location? I tried environmentals, but I think something may have been wrong with my coding.

Thanks to anyone that can help!
:dunno

[macropod]

I'm not sure where you're going with this.

You're starting off with a workbook named 'generator.xls' and you have a mailmerge process that works for that. For mailmerge purposes, Word wouldn't care if you used one version of that file today and a completely different one tomorrow - provided they have the same names and the named data sheet (Master2), the mailmerge would be oblivious to the change.

Your first post suggested that having run the mailmerge once, you might then want to run it against a different workbook. I provided code that would allow you to do that.

You then asked about pointing the mailmerge to a different worksheet in (the same?) workbook, for which I likewise provided some advice.

Then you asked whether it's:

possible to have the xls file save to the default location with the default name
and you want to do this from another Excel file. Nothing we'd discussed previously had anything to do with running code from Excel or, as would be required for what you now seem to envisage, automating Word from Excel.

All of the above are possible, but I don't want to invest my time developing solutions as you chase your thought processes around the paddock. When you've have a clear understanding of what it is you want to do, let us know we'll see what can be done.

[psctornado]

Hi macropod,

Sorry for my scatter brain responses. While I solved my prior issue, the main question I have now is how would I adjust my code so that my source file can be found in my documents. I believe my issue lies with with the environment variable. I'm not sure how one would write it. When I look at the path of the my documents folder I have : C:\Documents and Settings\psctornado\My Documents, but this will obviously change based on the user.

Below is what I currently have, but it continues to prompt me that my source file cannot be located.


Private Sub Document_Open()
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource Name:= _
"C:\My Documents\Generator.xls", _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:\My Documents\Generator.xls;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Pass" _
, SQLStatement:="SELECT * FROM `Master2$`", SQLStatement1:="", SubType _
:=wdMergeSubTypeAccess
ActiveDocument.MailMerge.ViewMailMergeFieldCodes = wdToggle
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
Application.WindowState = wdWindowStateMaximize
Windows("Details Template.doc").Activate
ActiveWindow.Close SaveChanges:=wdDoNotSaveChanges
End Sub


Again I appologize for my prior scatter brain responses...

Thanks again!

[macropod]

Change:
"C:\Documents And Settings\psctornado\My Documents\"
to:
"C:\Documents And Settings\" & Environ("UserName") & "\My Documents\"

[psctornado]

Excellent that worked like a charm!

Thank you!!:thumb