Microsoft Office Forums - View Single Post - [Solved] Is it possilble to transfer data from Excel to Publisher?

macropod · 10-23-2011, 05:11 AM

Hi Mark,

That's rather like asking: how long is a piece of string? It all depends on your familiarity with vba, using vba to automate Office apps, and the Publisher Object Model.

FWIW, here's some code showing how you might update a Word document from Excel. The code copies Range "A1:J10" on the first worksheet as an OLE object to a bookmark named "xlTbl" in a Word document named "C:\My Documents\MyFile.doc", and leaves the document active & visible:

Code:

Sub SendRangeToDoc()
Dim wdApp As Word.Application, WdDoc As String
'Copy range
ActiveWorkbook.Sheets(1).Range("A1:J10").Copy
'Establish link to Word
WdDoc = "C:\My Documents\MyFile.doc"
If Dir(WdDoc) <> "" Then
  Set wdApp = New Word.Application
  wdApp.Visible = True
  With wdApp
    'open the Word Document
    Documents.Open Filename:=WdDoc
    Dim BmkNm As String
    BmkNm = "xlTbl"
    With ActiveDocument
      If .Bookmarks.Exists(BmkNm) Then
        .Bookmarks(BmkNm).Range.PasteSpecial Link:=False, DataType:=wdPasteOLEObject, _
        Placement:=wdInLine, DisplayAsIcon:=False
        .Save
      Else
        MsgBox "Bookmark: " & BmkNm & " not found."
      End If
    End With
  End With
Else
  MsgBox "File: " & WdDoc & " not found."
End If
Set wdApp = Nothing
End Sub

For Publisher, the approach would be similar. See also:
http://msdn.microsoft.com/en-us/office/aa905474.aspx