Copy cells from excel to word bookmarks

Hi there,

The situation:

I have a list of people with characteristics (Name, Age, Address...) that I'm creating on excel. I want to be able to select a cell, click a button and:

  • open a word template
  • populate it's bookmarks
  • save it with the person's name
  • return to the excel workbook
  • I've pretty much patched up the code to function but I'm running into an interesting problem: the code breaks exactly once in every two times during 

ActiveDocument.SaveAs Filename:=SavePath & Name & ".doc"

with Run-time error '462': The remote server machine does not exist or is unavailable.

 

This is the code:

 

Sub copyToWord()

 

SavePath = Worksheets("Sheet2").[B3]

TemplatePath = Worksheets("Sheet2").[B6]

 

Dim Name As String

Dim wrdApp As Word.Application

 

 

Set wrdApp = CreateObject("Word.Application")

Name = Selection.Value

Application.ScreenUpdating = False

Selection.Copy

 

With wrdApp

.Documents.Open TemplatePath

.Selection.GoTo What:=wdGoToBookmark, Name:="Name"

.Selection.PasteSpecial DataType:=wdPasteText

.Visible = True

ActiveDocument.SaveAs Filename:=SavePath & Name & ".doc" 'WORKS!!

.Application.Quit

End With

 

Application.CutCopyMode = False

Application.ScreenUpdating = True

 

MsgBox ("File successfully created for " & Selection.Value & "!")

End Sub

 

I'm new with VBA programming. The way I manage is by hacking other people's codes and patching it all up (often using cellotape) to achieve my goals. I'm hoping this will explain inconsistencies on my code...

I also welcome any hints, tips and suggestions in general =)

Thank you for your time and attention!

 

Hi,Well this error is

Hi,
Well this error is comming because you have to qualify each call of Word methods or properties.

Primo, you mustn't declare variable with a name like
Dim Name as String
Name is a reserved Keyword
Use instead (with an appropriate prefix accorded to the type)
Dim strSelectionValue as String

In fact you forgot a dot (.) near the method "ActiveDocument" and this is why you have the error 462.

Sub CopyToWord()
Dim strSelectionValue As String
Dim strTargetPath As String
Dim oWordApp As Word.Application
On Error GoTo L_ErrcopyToWord

strTargetPath = Worksheets("Sheet2").[B3]
TemplatePath = Worksheets("Sheet2").[B6]

If Right$(strTargetPath, 1) = "\" Then
strTargetPath = strTargetPath & "\"
End If

Set oWordApp = CreateObject("Word.Application")
strSelectionValue = Selection.Value
If Len(strSelectionValue) Then
Application.ScreenUpdating = False
Selection.Copy
With oWordApp
.Documents.Add TemplatePath
.Selection.Goto What:=wdGoToBookmark, Name:="bkmName"
.Selection.PasteSpecial DataType:=wdPasteText
.Visible = True
.ActiveDocument.SaveAs FileName:=SavePath & strSelectionValue & ".doc" 'WORKS!!
.Quit
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
MsgBox "File successfully created for " & strSelectionValue & "!", vbInformation
Else
MsgBox "The selection is empty !", vbExclamation
End If

On Error GoTo 0
L_ExcopyToWord:
Set oWordApp = Nothing
Exit Sub

L_ErrcopyToWord:
MsgBox Err.Description, vbExclamation, Err.Source
Resume L_ExcopyToWord

End Sub

In the same way, you mustn't name your bookmarks "Name".
See, I renamed it "bkmName".
It's more convenient for code readers