(PDF VBA and Excel) export cover sheet w/ a loop, attach spec sheet from local drive.

[Terriblarius]

Hi there. First post here, hopefully it isn't too much for a first shot. I've queried around and this forum seemed like the best shot at getting closer to finishing my project.

So i have a process that i'm trying to automate. For each project that I do, i'm required to submit a compendium of the devices I have used. Each device spec sheet requires a coversheet. Currently, we generate the cover sheets with microsoft word using mail merge, export the result as a pdf, then we use adobe acrobat to manually hunt and insert the spec sheet to each appropriate coversheet.

So, in going forward with automating, i've retooled the database to what you see below:

database example.jpg

There is a field to determine what devices you want (Merge? column), the information that needs to be displayed on the coversheet, and a hyperlink to the specsheet on the local drive.

On another worksheet, i've made the template for the cover page that forwards each spec sheet. See below.

coversheet example.jpg

At the end of the magic vba loop, this is what is the desired output i want/need.

Desired assembly.jpg

My attempt to code this has been slow and unsuccessful. below is the code i've made so far, But it doesn't do what i need it to and is a long way away from the desired end.

Option Explicit

Sub Merger()

'Cutsheet Combiner
'this macro is an attempt to create a cutsheet cover using a database, and then attach the actual pdf cutsheet from the hyperlink to X drive.

'declare and variables and dimension

Dim i As Integer
Dim y As Integer
Dim x As Integer
Dim z As Integer

Dim pdfcs As Worksheet
Dim pdfnm As String
Dim flnm As String

Dim dir As String



x = Sheet2.Range("D3") 'starting record in database
y = Sheet2.Range("D4") 'final record in database
'z = Sheet2.Range("D2") 'current record row
dir = Sheet1.Range("E1") 'Base directory as formulated in cell E1 in database
pdfnm = "Combined Cutsheets"
flnm = dir & pdfnm
Set pdfcs = Sheets("Coversheet Template")

'loop thru each row in the data base.

For i = x To y

Sheet2.Cells(2, 4).Value = i
'if range("J3") = "y" then export the cover sheet as pdf to an existing pdf
If Sheet2.Range("J3") = "y" Then

'export as pdf cutsheet to a specific file in directory
    pdfcs.ExportAsFixedFormat Type:=xlTypePDF, Filename:=flnm & i - 3, quality:=xlQualityStandard


    'also attach the pdf from the hyperlink at range("J9") specific in directory
    'not sure how to do this
    

End If

'Increment the coversheet database row reference by one


Next i


'end loop

'save fully combined pdf document, not sure how to do this.


End Sub

So that's what i have so far, i'm not married to my existing code and open to tearing it down and starting over. I've also attached the .xlsm workbook a workable example.

Thanks in advance, let me know if this is too much for a first post or if you need more info to help with this.