Code Issue - Debugging error

[psctornado]

My setup is this :

Run macro from excel workbook, which runs a merge into a word template. The merge pulls all the fields from tab 'Master2'. I was looking to have the populated word doc have the file name specified in the Master2 tab, cell AZ2. This file name changes depending on the value found in AZ2.

This file name should be populated once the user selects 'Save as' from Word. Does anyone know what I'm missing here? The error I'm receiving is 'User-defined type not defined' & it's highlighting. Thanks in advance!

Dim xlApp As Excel.Application

When trying to run the macro from excel, the code in the word template gives me a debug error.
Private Sub Document_Open()Application.DisplayAlerts = wdAlertsNone
Dim MMSource As String
MMSource = "C:\Documents And Settings\" & Environ("UserName") & "\My Documents\Lit Generator\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 `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
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("Master2")
FName = xlSheet.Range("AZ2").Value
ChangeFileOpenDirectory "C:\Documents And Settings\" & Environ("UserName") & "\Desktop\"
ActiveDocument.SaveAs FileName:=FName
.Saved = True
.Close SaveChanges:=wdDoNotSaveChanges
End With
End Sub

[SamT]

the code in the word template gives me a debug error.
It would help to know the text of the error and where it occurs.

[psctornado]

Hi samt,

I noted where the error occurred in my initial post.

[SamT]

My bad, I didn't read the 'Fine Print.'


'User-defined type not defined' & it's highlighting.
Dim xlApp As Excel.Application

[gmaxey]

You need a reference in your Word project to the Excel object library, or you need to use late binding and chage

Dim xlApp As Object 'Excel.Application
Dim xlBook As Object 'Excel.Workbook
Dim xlSheet As Object 'Excel.WorkSheet

[Paul_Hossler]

Would you need to create the object?




Set xlApp = Create.Object("Excel.Application")

[psctornado]

Thanks for the help! I tried adding the objects noted above to the original code & it's works. However, it's saving a doc rather than letting me select 'save as instead'. Is it just a matter of updating something in the below code?:

ChangeFileOpenDirectory "C:\Documents And Settings\" & Environ("UserName") & "\Desktop\"
ActiveDocument.SaveAs FileName:=FName
.Saved = True

So currently for testing in cell AZ2 I have a file name of Apple123. I want when I select "SaveAs" from the file menu to already have Apple123.docx in the FileName Row, so that the user can save the doc wherever they wish (see example pic).
21572

I feel like I'm close, but just unsure where to go.

[SamT]

Dim xlApp As Object 'Excel.Application
Set xlApp = Create.Object("Excel.Application")

[psctornado]

Hi Sam,

Sorry I edited my post at around the same you posted your response.

Still looking for a little bit of help :)

[psctornado]

Still looking for help with the above if anyone has any ideas / suggestions? Thank you!!

[macropod]

Aside from needing to supply the correct path for the save (in your SaveAs line), there is no need to change folders. In any event, the
SaveAs method doesn't open the Save As dialog - you need to use the SaveAs dialog for that.For example:

With Dialogs(wdDialogFileSaveAs)
.Name = "C:\Documents And Settings\" & Environ("UserName") & "\Desktop\" & FName
.Show
End With