The company I work for designs and manufactures machinery, and one of their biggest selling points is that each machine can be made to fit the customer's needs. Therefore, there are rarely two machines that are exactly the same. For each machine, a Parts Catalog has to be created. Currently, the Parts Department has around 40,000 Publisher documents, representing a part or an assembly of parts. In the past, they used a Macro in Excel to open each part's Publisher document, select all the shapes on that document, add a page to the Parts Catalog, and paste those shapes into that new page. It was a bit buggy, and really bogged down their system for a good 20 minutes. The original Macro was written for Office 2003 in Windows XP. I have been trying to update that to a VB.Net application that would work with Office 2010 in Windows 7.
The following code is what I have been using to replicate the old Macro. It creates two Publisher applications. The variable fileList is an ArrayList of Strings, and it stores the file names of the individual parts/assembly documents. One application creates the Parts Catalog document, the other is used to open the individual parts/assembly documents from the fileList. The code iterates a loop for the length of the fileList array, and creates a new Publisher document under the second Publisher application. It then sets that document equal to the parts/assembly document. Then it iterates a loop for the number of pages in that document, and selects all shapes on that page, and pastes it into the next page of the Parts Catalog. If the loop hasn't reached its end, it adds a new page to the Parts Catalog. When the process has reached 20, 40, 60 and 80 percent completion, it closes the Publisher applications, clears them from memory, and then re-opens them. This insures that the applications won't build up too much memory usage.
Private Sub createPC() Dim page As Integer = 1 Dim oPUB As New Publisher.Application Dim oPUB2 As New Publisher.Application With oPUB.ActiveDocument .PageSetup.PageHeight = oPUB.InchesToPoints(8.5) .PageSetup.PageWidth = oPUB.InchesToPoints(11) .MasterPages(1).Shapes.AddShape(Microsoft.Office.Core.MsoAutoShapeType.msoShapeRectangle, oPUB.InchesToPoints(0.4), oPUB.InchesToPoints(0.4), oPUB.InchesToPoints(10.21), oPUB.InchesToPoints(7.75)) .MasterPages(1).Shapes(1).Line.Weight = 4.5 .SaveAs(pcPushDirectory & "\" & machineName & "-PC.pub") End With Clipboard.Clear() GC.Collect() GC.WaitForFullGCComplete() For i As Integer = 0 To fileList.Count - 1 Dim oPUBDOC As New Publisher.Document oPUBDOC = oPUB2.Open(tempDirectory & "\" & fileList(i) & ".pub") For o As Integer = 1 To oPUBDOC.Pages.Count oPUBDOC.ActiveView.ActivePage = oPUBDOC.Pages(o) Try oPUBDOC.ActiveView.ActivePage.Shapes.SelectAll() oPUBDOC.Selection.ShapeRange.Copy() Catch ex As Exception End Try GC.Collect() GC.WaitForFullGCComplete() Try oPUB.ActiveDocument.Pages(page).Shapes.Paste() Catch ex As Exception End Try clearClipboard() If i <> fileList.Count - 1 Then oPUB.ActiveDocument.Pages.Add(1, page) End If page += 1 Next oPUBDOC.Close() System.Runtime.InteropServices.Marshal.ReleaseComObject(oPUBDOC) oPUB.ActiveDocument.Save() If i = Math.Ceiling(fileList.Count * 0.2) Or i = Math.Ceiling(fileList.Count * 0.4) Or i = Math.Ceiling(fileList.Count * 0.6) Or i = Math.Ceiling(fileList.Count * 0.8) Then releasePublisher(oPUB) oPUB = New Publisher.Application oPUB.Open(pcPushDirectory & "\" & machineName & "-PC.pub") releasePublisher(oPUB2) oPUB2 = New Publisher.Application End If Next releasePublisher(oPUB) releasePublisher(oPUB2) End Sub Private Sub releasePublisher(pubApplication As Publisher.Application) Try pubApplication.Quit() System.Runtime.InteropServices.Marshal.ReleaseComObject(pubApplication) GC.Collect() GC.WaitForFullGCComplete() Catch e As Exception End Try End Sub
The problem I'm having is that some pages will not copy over. The Parts Catalog will have random blank pages scattered throughout the document. If I don't include the Try statements, sometimes the application will fail, with an error saying that Publisher's clipboard is busy. If I don't use Garbage Collection, the application will skip even more pages.
What causes the clipboard to be busy? Is there a better way of executing the copy and paste method? Or does anyone know of a better method than the copy and paste method to combine multiple publisher documents into a single document?
- Edited by Silvus Valentine Thursday, December 06, 2012 10:22 PM
Hi Silvus Valentine,
Thank you for posting in the MSDN Forum.
I'll involve some experts who are more familiar with your issue. This may take some time.
Thank you for your patient and understanding.
Quist Zhang [MSFT]
MSDN Community Support | Feedback to us
Develop and promote your apps in Windows Store
Please remember to mark the replies as answers if they help and unmark them if they provide no help.