PDA

View Full Version : Controlling settings in Publisher from Excel



Cowlers
01-06-2011, 03:22 AM
Hello all,

I guess this is technically a "Publisher" questions but I could not find the board for it so I have put it here because I am trying to control Publisher from Excel.

I am able to open a file in Publisher from Excel and perform a mailmerge function on this file so that it exports to a new document:

Sub OpenPubDoc(FilePath)
Dim pApp As Publisher.Application
Dim pDoc As Publisher.Document
Dim pMailMerge As Publisher.MailMerge
Set pApp = New Publisher.Application
Set pDoc = pApp.Documents.Application.Open(FilePath)
Set pMailMerge = pDoc.MailMerge
If pMailMerge.DataSource.ConnectString <> "" Then
pMailMerge.Execute pause:=False, Destination:=pbMergeToNewPublication
End If
End Sub

I am happy with the result this gives me as I would like the user to save this document for future reference once we are at this stage.

My problem is that this document produces a set of labels and whenever I go to print in this newly created document it is set to print to a single page instead of printing "Multiple Pages per Sheet".

I do not want this document to automatically print so I can't use the pbprintstyle piece of code to do this (I don't think?!?), all I want to do is change the setup for this new document so that if the user did go to print the settings would be correct already for them!

I also need to change the paper type so if this can be included in the same code that would be greatly appreciated!

I did find a piece of code as below:

With Publisher.ActiveDocument.PageSetup
.MultiplePagesPerSheet = True
End With

But when I tried this I got an error message (I did see that the "MultiplePagesPerSheet" option is "Hidden" in Publisher 2007 but I couldn't see a reason why this would throw up an error.

Many thanks in advance for any help you can provide me with!

Best regards,

Dan

Bob Phillips
01-06-2011, 05:36 AM
Can you post the Publisher file Dan?

Cowlers
01-06-2011, 06:13 AM
5183

The above is a zip containing the publisher file and the spreadsheet used for the mailmerge.

You will see that the original publisher file has the "Multiple Pages Per Sheet" option in it but the new document created when you export the mailmerge to new document does not retain this setting.

Best regards,

Dan