K_rex14
07-28-2010, 03:14 AM
Hi All
Firstly thanks to all the members whom with out the info found on the forum I would not be anywhere with my VBA code.
I'm trying to export a named field from excel (Access_PT) and copy it into word using a bookmark (Access_Import). The paste method is Metafile. The 1st run time error occurs when I run the macro for a second time, Run-time error '462': "The remote server machine does not exist or is unavailable" and occurs at the following line:
Set BMRange = ActiveDocument.Bookmarks("Access_Import").Range
I believe I'm getting the issue as I'm not using the WordApp object to open and close the word file. Really not sure as I have tried to exit the application at the end of the code.
The other run-time error ('5941': The requested member of the collection does not exist) occurs at the same location in the macro:
Set BMRange = ActiveDocument.Bookmarks("Access_Import").Range
but only when I have Word already open. I'm using Office 2007, and the code for the entire macro is below:
Private Sub CommandButton_Click()
Dim WordApp As Object
Dim WordDoc As Word.Document
'-----------------------------------------------------------------------------
'select range and copy to memory
Sheets("Pricing").Select
Range("Access_PT").Select
Selection.Copy
'select range and copy to memory
'-----------------------------------------------------------------------------
Set WordApp = CreateObject("Word.Application")
WordApp.Documents.Add "c:\temp\export.docm"
WordApp.Visible = True
Set WordDoc = WordApp.ActiveDocument
'-----------------------------------------------------------------------------
'Unlock Word doc
If WordDoc.ProtectionType <> wdNoProtection Then WordDoc.Unprotect Password:="password"
'End If
'------------------------------------------------------------------------------
'Find and Paste at bookmark
Dim BMRange As Word.Range
Set BMRange = ActiveDocument.Bookmarks("Access_Import").Range
With BMRange
.PasteSpecial Link:=False, DataType:=wdPasteMetafilePicture, Placement:=wdInLine, DisplayAsIcon:=False
WordDoc.Application.Run MacroName:="Formats"
'------------------------------------------------------------------------------
'Protect Word doc
ActiveDocument.Protect Password:="password", _
NoReset:=False, Type:=wdAllowOnlyFormFields
'-----------------------------------------------------------------------------
WordApp.Visible = True
'Clean up
WordDoc.Application.Quit SaveChanges:=wdPromptToSaveChanges, OriginalFormat:=wdOriginalDocumentFormat
Application.CutCopyMode = False
WordApp.Application.Quit
Set WordApp = Nothing
Set WordDoc = Nothing
End With
End Sub
Thank you in advance.
k_rex14
Firstly thanks to all the members whom with out the info found on the forum I would not be anywhere with my VBA code.
I'm trying to export a named field from excel (Access_PT) and copy it into word using a bookmark (Access_Import). The paste method is Metafile. The 1st run time error occurs when I run the macro for a second time, Run-time error '462': "The remote server machine does not exist or is unavailable" and occurs at the following line:
Set BMRange = ActiveDocument.Bookmarks("Access_Import").Range
I believe I'm getting the issue as I'm not using the WordApp object to open and close the word file. Really not sure as I have tried to exit the application at the end of the code.
The other run-time error ('5941': The requested member of the collection does not exist) occurs at the same location in the macro:
Set BMRange = ActiveDocument.Bookmarks("Access_Import").Range
but only when I have Word already open. I'm using Office 2007, and the code for the entire macro is below:
Private Sub CommandButton_Click()
Dim WordApp As Object
Dim WordDoc As Word.Document
'-----------------------------------------------------------------------------
'select range and copy to memory
Sheets("Pricing").Select
Range("Access_PT").Select
Selection.Copy
'select range and copy to memory
'-----------------------------------------------------------------------------
Set WordApp = CreateObject("Word.Application")
WordApp.Documents.Add "c:\temp\export.docm"
WordApp.Visible = True
Set WordDoc = WordApp.ActiveDocument
'-----------------------------------------------------------------------------
'Unlock Word doc
If WordDoc.ProtectionType <> wdNoProtection Then WordDoc.Unprotect Password:="password"
'End If
'------------------------------------------------------------------------------
'Find and Paste at bookmark
Dim BMRange As Word.Range
Set BMRange = ActiveDocument.Bookmarks("Access_Import").Range
With BMRange
.PasteSpecial Link:=False, DataType:=wdPasteMetafilePicture, Placement:=wdInLine, DisplayAsIcon:=False
WordDoc.Application.Run MacroName:="Formats"
'------------------------------------------------------------------------------
'Protect Word doc
ActiveDocument.Protect Password:="password", _
NoReset:=False, Type:=wdAllowOnlyFormFields
'-----------------------------------------------------------------------------
WordApp.Visible = True
'Clean up
WordDoc.Application.Quit SaveChanges:=wdPromptToSaveChanges, OriginalFormat:=wdOriginalDocumentFormat
Application.CutCopyMode = False
WordApp.Application.Quit
Set WordApp = Nothing
Set WordDoc = Nothing
End With
End Sub
Thank you in advance.
k_rex14