HELP: Export data to word template and save it as a seperate file

S

sam

Hi All,

I want to export details from excel userform to a word template and save it
at a different file. So basically once the user clicks the Export to word
button this should happen:

-Opens up a dialog window(to select the word template)
- User Selects the word template (I have a specific word template designed)
- the template is then "SAVED AS" a seperate word file at a specific
location(mentioned in code below)

But what is happening is, When user clicks the button and selects the
template, the data is exported to that template, and its not saved in a
different word file at the specified location in the code.

Private Sub Export_To_Word_Click()

Dim wdApp As Object
Dim doc As Object
Dim fdialog As FileDialog
Dim pathAndFile As String
Dim filePath As String
Dim shortName As String

On Error Resume Next
Set wdApp = GetObject(, "Word.Application")

If Err.Number <> 0 Then
Set wdApp = CreateObject("Word.Application")
End If
On Error GoTo 0

wdApp.Visible = True

Set fdialog = wdApp.FileDialog(msoFileDialogFilePicker)

With fdialog
.AllowMultiSelect = False
.Filters.Clear
.InitialFileName = filePath & "\*.dotx*"

If .Show Then
pathAndFile = .SelectedItems(1)
shortName = Right(pathAndFile, _
Len(pathAndFile) - InStrRev(pathAndFile, "\"))
Else
MsgBox "User cancelled. Did not select a file"
GoTo Pastcode
End If
End With

Set fdialog = Nothing

Set doc = wdApp.Documents.Open(pathAndFile)

On Error Resume Next
With doc
..variables("Address").Value = Me.Property_Address.Value
..variables("City").Value = Me.City.Value
..variables("State").Value = Me.State.Value
..variables("Zipcode").Value = Me.Zip_Code.Value
..Fields.Update
End With

doc.SaveAs FileName:=filePath

filePath = "C:\My Documents\" _
& "Property Details" & " - " & Me.File_Number.Value & ".doc"

Pastcode:

wdApp.Quit
Set doc = Nothing
Set wdApp = Nothing

End Sub


Thanks in advance
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top