Auto name & Save document with unique name

[psctornado]

Hi All,

I was browsing the forum and found solutions that were similar, but not quite. My goal is to run a mail merge linked to an excel source, but have it saved automatically to a field in the mail merge source.

For example : mail merge source has a file name field of : XYZ123, I would like the document to save using that name. The name will obviously change, based on whatever the file name is.

Can anyone help me modify my code to accomplish this?
:think:


Private Sub Document_Open()
Application.DisplayAlerts = wdAlertsNone
Dim MMSource As String
MMSource = "C:\Documents And Settings\" & Environ("UserName") & "\My Documents\My Workbook\Workbook.xls"
With ActiveDocument
With .MailMerge
.MainDocumentType = wdFormLetters
.OpenDataSource Name:=MMSource, ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, Connection:= _
"Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=MMSource;Mode=Read;Extended Properties=" & _
"HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=35;" & _
"Jet OLEDB:Database Loc", 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
Application.WindowState = wdWindowStateMaximize
Application.DisplayAlerts = wdAlertsAll
ChangeFileOpenDirectory "C:\Documents And Settings\" & Environ("UserName") & "\My Documents\My Workbook\"
ActiveDocument.SaveAs FileName:="Tested File.doc"
.Close SaveChanges:=wdDoNotSaveChanges
End With
End Sub

[psctornado]

Maybe I should elaborate or better explain what the goal is. Is there a way to have a merge field be the name of the newly created word file? The above code will save this file as 'Tested File.doc', however I'm more interested in if its possible to have a merge field dictate how a the new word file is saved.

[westconn1]

however I'm more interested in if its possible to have a merge field dictate how a the new word file is saved.
where is the mergefield you want to use? is it the same for every record? is it printed to the merged documents?

if the merge field is printed in the document, then can read the document to find the value
else you may need to open the data source, as a recordset, to read the correct record specifically

[psctornado]

The mergefield will be will be found in the same source xls file. The file name will not be in the printed merge doc, thats why I was curious how to auto name the word file if the name i would like is in the source xls.

[psctornado]

Anyone have any further thoughts with this case?:dunno

[fumei]

If the data is in a cell in an Excel file, then just use that cell value.

[psctornado]

Hi Fumei,

Thats what I was thinking as well. How would I be able to code that so that the merge cell would be the name of the word doc?

[westconn1]

try like

set xl = getbject(,mmsource)
fname = xl.sheets(1).range("x99").value ' change range and sheet to suit,
if xl.application.workbooks.count = 1 then
xl.application.quit
else
xl.close
end if
ActiveDocument.SaveAs FileName:= fname

[psctornado]

Hey Westconn1,

So I tried to give a go on your code, and think I'm getting somewhere, but may have applied it slightly in error. I'm getting an error stating : Compile Error, Sub or Function not defined.


Private Sub Document_Open()
Application.DisplayAlerts = wdAlertsNone
Dim MMSource As String
MMSource = "C:\Documents And Settings\" & Environ("UserName") & "\My Documents\My Workbook\Workbook.xls"
With ActiveDocument
With .MailMerge
.MainDocumentType = wdFormLetters
.OpenDataSource Name:=MMSource, ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, Connection:= _
"Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=MMSource;Mode=Read;Extended Properties=" & _
"HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=35;" & _
"Jet OLEDB:Database Loc", SQLStatement:="SELECT * FROM `Mail_Merge$`", SQLStatement1:="", SubType:=wdMergeSubTypeAccess
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
.MainDocumentType = wdNotAMergeDocument
End With
Application.WindowState = wdWindowStateMaximize
Application.DisplayAlerts = wdAlertsAll
Set xl = getbject(, MMSource)
FName = xl.Mail_Merge.Range("I2").Value ' change range and sheet to suit,
If xl.Application.workbooks.Count = 1 Then
xl.Application.Quit
Else
xl.Close
ChangeFileOpenDirectory "C:\Documents And Settings\" & Environ("UserName") & "\My Documents\My Workbook\"
ActiveDocument.SaveAs FileName:=FName
.Saved = True
.Close SaveChanges:=wdDoNotSaveChanges
End With
End If
End Sub

[fumei]

You have to declare and Set an Excel object.

[psctornado]

Fumei,

Sorry...could you expand on what your eluding to? I understand some parts of VBA and coding, but sometimes I need a bit more help...as in this case :)

[fumei]

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.WorkSheet
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open Filename:= MMSource
Set xlSheet = xlBook.Worksheets(xxxxxxx) ' put your sheet name
Now use the cell range that has your filename for the Word doc.

Try doing a search for how to use application objects, and\or how to use cell references in Excel.

[westconn1]

typo
should have been
Set xl = getObject(,mmsource)

to use fumeis code you would need to have a reference to excel


also this line is incorrect
FName = xl.Mail_Merge.Range("I2").Value
it maybe should be
FName = xl.sheets("Mail_Merge").Range("I2").Value

[psctornado]

Thanks guys for the help! I seem to be a bit closer. It still giving me a debug error at on the set xl line. Below is what I have that particular area. I tried to copy / paste what WestConn had above, but it seems to insert a space in the , MMSource row. Any thoughts?


Set xl = GetObject(, MMSource)
FName = xl.sheets("Mail_Merge").Range("I2").Value
If xl.Application.Workbooks.Count = 1 Then
xl.Application.Quit
Else
xl.Close
ChangeFileOpenDirectory "C:\Documents And Settings\" & Environ("UserName") & "\My Documents\My Workbook\"
ActiveDocument.SaveAs FileName:=FName
.Saved = True
.Close SaveChanges:=wdDoNotSaveChanges
End If
End With

[fumei]

If you are use Set xl, you need a DECLARE statement.

[psctornado]

Fumei,

I declared my statement using your suggestion above, but it's still giving me an error on the 1st declare line. Thoughts anyone? :banghead:


Private Sub Document_Open()
Application.DisplayAlerts = wdAlertsNone
Dim MMSource As String
MMSource = "C:\Documents And Settings\" & Environ("UserName") & "\My Documents\My Workbook\Workbook.xls"
With ActiveDocument
With .MailMerge
.MainDocumentType = wdFormLetters
.OpenDataSource Name:=MMSource, ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, Connection:= _
"Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=MMSource;Mode=Read;Extended Properties=" & _
"HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=35;" & _
"Jet OLEDB:Database Loc", SQLStatement:="SELECT * FROM `Mail_Merge$`", SQLStatement1:="", SubType:=wdMergeSubTypeAccess
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
.MainDocumentType = wdNotAMergeDocument
End With
Application.WindowState = wdWindowStateMaximize
Application.DisplayAlerts = wdAlertsAll
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.WorkSheet
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open Filename:= MMSource
Set xlSheet = xlBook.Worksheets("Mail_Merge").Range("I2").Value
ChangeFileOpenDirectory "C:\Documents And Settings\" & Environ("UserName") & "\My Documents\My Workbook\"
ActiveDocument.SaveAs FileName:=FName
.Saved = True
.Close SaveChanges:=wdDoNotSaveChanges
End If
End With
End Sub

[fumei]

but it's still giving me an error on the 1st declare line What is the "1st declare line", do you mean Dim xlApp As Excel.Application ?

Do you have a reference to the Excel library? You need that for early binding.
Did you do any research on this?

BTW: in the forum, whenever you mention getting an error, ALWAYS mention what the error is exactly, including the error message. Is it the same as your first one, Compile Error, Sub or Function not defined.

[westconn1]

try omitting the comma
Set xl = GetObject(MMSource)

[psctornado]

Hey Fumei,

I should have checked the reference to the excel library. It looks like the only error I am receiving now is the following :

11134

The syntax error seems to be referencing the following line :



Set xlBook = xlApp.Workbooks.Open Filename:= MMSource

Sorry to have not been as thorough in my last post :).

[westconn1]

needs file name argument to be enclosed in brackets

[psctornado]

Hey westconn,

I added the brackets, but still getting the same syntax error as noted above. Any other thoughts?:banghead:

[fumei]

As you do not mention what KIND of brackets you used...

Set xlBook = xlApp.Workbooks.Open(FileName:=MMSource)

[psctornado]

Thanks Fumei! I was just applying the brackets around the 'MMSource' and not on the Filename as well. The last error I am getting now after making that adjustment is a runtime error - 424, 'Object Required'.



Set xlSheet = xlBook.Worksheets("Mail_Merge").Range("I2").Value


I looked at my my references and verified I have Excel 14.0 Object library selected, as well as MS Office 14.0 Object & MS Word 14.0 Object. Any thoughts on this???

[westconn1]

you are trying to set an object variable with a string or numeric value (from a range or cell)
if xlsheet is defined as a worksheet, it can only be set to a worksheet object, anything else would give type mismatch

try

myval = xlBook.Worksheets("Mail_Merge").Range("I2").Value where myval is a variant or (presumably) string

[fumei]

As stated previously, you need to do some research on using objects. As westconn1 mentions you are mixing objects. xlSheet is declared as a Sheet, and therefore MUST only be Set as a Sheet. Range("I2").Value is a string value, NOT a Sheet. You can do as westconn1 posted, or

' assign worksheet object to the declared Sheet object
Set xlSheet = xlBook.Worksheets("Mail_Merge")

' assign cell value to the FName string variable
FName = xlSheet.Range("I2").Value

' use it
ActiveDocument.SaveAs FileName:=FName

[psctornado]

I got it now. I think I was combining contradictory code. I'm not too familar with using objects, but this exercise has certainly and I appreciate all the help!

[fumei]

Hey it is all a learning moment. Glad it seems to be working for you.

[pselva01]

Can you please share the final code that worked? It would be greatly appreciated.

[psctornado]

Hi Pselva01,

The final code was the following :



Private Sub Document_Open()
Application.DisplayAlerts = wdAlertsNone
Dim MMSource As String
MMSource = "C:\Documents And Settings\" & Environ("UserName") & "\My Documents\My Workbook\My_Generator.xls"
With ActiveDocument
With .MailMerge
.MainDocumentType = wdFormLetters
.OpenDataSource Name:=MMSource, ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, Connection:= _
"Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=MMSource;Mode=Read;Extended Properties=" & _
"HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=35;" & _
"Jet OLEDB:Database Loc", SQLStatement:="SELECT * FROM `Mail_Merge$`", SQLStatement1:="", SubType:=wdMergeSubTypeAccess
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
.MainDocumentType = wdNotAMergeDocument
End With
Application.WindowState = wdWindowStateMaximize
Application.DisplayAlerts = wdAlertsAll
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.WorkSheet
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open(FileName:=MMSource)
Set xlSheet = xlBook.Worksheets("Mail_Merge")
FName = xlSheet.Range("I2").Value
ChangeFileOpenDirectory "C:\Documents And Settings\" & Environ("UserName") & "\Desktop\Manual Lit\"
ActiveDocument.SaveAs FileName:=FName
.Saved = True
.Close SaveChanges:=wdDoNotSaveChanges
End With
End Sub


A small thing to note..

1) Make sure the excel object library is selected in word, otherwise it cannot link with the Excel properly.