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