Is it possilble to transfer data from Excel to Publisher?

[markhuges]

Hello i am new here



i have a excel spreadsheet, which is used for a shop which has simple stock and items

the spreadsheet, has different items, their prices and the stock amount.

it currently takes in customer details, and then selects the items.

The question is that, If i create a template on Microsoft Publisher which is a Template like a receipt

would it be possible to get the data from excel (the customer names and items ) and somehow ( this is the part i dont know or understand) paste that data onto the publisher Template and than print out that receipt.

i have a userfrom and macros recorded, on the excel spreadsheet

would it be possible to create a macro from the userfrom menu - which take the data (customer recipt ) and automatically entere that data on a pre set templete reciept - on publisher


is this possible? Thank You
Thank You

[macropod]

Hi Mark,

I'm not familiar with Publisher, but this can be done fairly easily with Word. Is there a particular reason for using Publisher?

[markhuges]

Yes one of the requirments is that it has to be done on publisher

how would you do it on word? and would it be possible to do the same thing on publisher

[macropod]

Hi Mark,

For Word, what you'd do is to first define an Excel range that will always hold the data that Word will link to (eg a Sheet named Xfer, in which whatever data are to be linked will appear in A1:C3). Then you'd copy that range and paste it into Word using Edit|Paste Special with the 'Paste Link' option and the paste format you desire. From then on, whenever the data in Xfer A1:C3 changes, the link in Word will update.

[markhuges]

@MacroPod - Thank You for that will run a mini test and give feedback Thank You


Yes it does work with word but not on Publisher

i was thinking would it be possible to do that using VBA and doing that process automatically? Thank You

[macropod]

Hi Mark,

I don't understand why Publisher is the requirement, since Word can create receipt-like documents perfectly well. Still, that's your (organisation's?) decision. I'm suppose you could automate Publisher from Excel, or vice-versa, but I have no experience with that - only automating Word from Excel, and vice-versa.

[markhuges]

Ok thanks, how long and how hard do you think it would be to create a code to automate Publisher from Excel?

[macropod]

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:

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

[markhuges]

Thank You for your help will try that soon around in a week and give feedback