PDA

View Full Version : Problem using VBA with Powerpoint to combine multiple PDF's together



subduco
10-15-2014, 08:05 PM
I borrowed/wrote code that generates cover letter slides from column A of a excel worksheet, then converts them to a PDF and saves them to a folder called Coverletters. Then it is supposed to combine a Main Cover Letter in the Hubs folder, with the sub-cover letter in the Coverletters folder, and a As-Built package PDF in the HPDocs folder, and lastly it combines a Contact information sheet at the end all into one PDF document.

This works on the first time around, it grabs all the right documents and combines them together and names the file correctly and everything. Now on the second time around, it will name the file correctly and generate the correct coverletter and place it as a PDF in the coverletters folder but it will not grab the right Main Cover letter PDF in the Hubs folder or the right As-Built document in the HPDocs folder etc., but when you open the document it is actually just the first document, duplicated twice. So if you have Node1 document with all of the Node1 files, then the second document will name itself Node2 (acquired from the second row of the excel worksheet column A) however it will just have all of Node1's files in it, twice. The third document will name itself correctly to Node3, but it will have all of Node1 files in it THREE times, the fourth document will name itself correctly but will have all of Node1's documents in it FOUR times and so on and so forth.

I do not understand why its not grabbing the right files after the first time through, and why it is duplicating the first nodes documents, but is generating the correct cover letters in the coverletter folder. Hopefully someone out there smarter then me (pretty much everyone) can lend some help. I been working at this all day and can't seem to get it, thanks in advance!

***Added the full code in two PNG attachments, would not let me post it all.

subduco
10-15-2014, 08:10 PM
Sub CreateSlidesAndExportToIndividualPDF()
'Open the Excel workbook. Change the filename here.
Dim OWB As New Excel.Workbook
Set OWB = Excel.Application.Workbooks.Open("")
'Grab the first Worksheet in the Workbook
Dim WS As Excel.Worksheet
Dim sCurrentText As String
Dim sCurrentHub As String
Dim oSl As Slide
Dim oSh As Shape
Dim oPPT As Presentation, oSlide As Slide
Dim sPath As String, sExt As String
Dim dirPath As String
Dim gPDDoc1 As AcroPDDoc
Dim gPDDoc2 As AcroPDDoc
Dim gPDDoc3 As AcroPDDoc
Dim gPDDoc4 As AcroPDDoc
Dim i As Long
Dim abc As Long
Set gPDDoc1 = CreateObject("AcroExch.PDDoc")
Set gPDDoc2 = CreateObject("AcroExch.PDDoc")
Set gPDDoc3 = CreateObject("AcroExch.PDDoc")
Set gPDDoc4 = CreateObject("AcroExch.PDDoc")


Set WS = OWB.Worksheets(1)
Set oPPT = ActivePresentation
sPath = "Node Coverletter" & " "
sPath2 = " Closeout Package"
sExt = ".pdf"
dirPath = ""
abc = 1
'Loop through each used row in Column A
For i = 1 To WS.Range("A65536").End(xlUp).Row
abc = 1
'Copy the first slide and paste at the end of the presentation
ActivePresentation.Slides(1).Copy
ActivePresentation.Slides.Paste
Set oSl = ActivePresentation.Slides(i + 1)
sCurrentText = WS.Cells(i, 1).Value
sCurrentHub = WS.Cells(i, 2).Value
' find each shape with "NODEID" in text, replace it with value from worksheet

subduco
10-15-2014, 10:55 PM
Figured it out, had to repaste this below the abc +1 counter increment
Set gPDDoc1 = CreateObject("AcroExch.PDDoc")
Set gPDDoc2 = CreateObject("AcroExch.PDDoc")
Set gPDDoc3 = CreateObject("AcroExch.PDDoc")
Set gPDDoc4 = CreateObject("AcroExch.PDDoc")