Batch-making PDFs with different excel rows
Batch-making PDFs with different excel rows
I am not sure if this is doable in excel, but since it has a lot of powerful features I am thinking there might be a way.
I have a Sheet of excel that has multiple rows of data.
I have sheet 2 that is basically a printable version of this data, however it only corresponds to 1 row of data.
Is there a way for me to mass-save PDF's of Sheet 2 but with each different row?
for example, A2, B2, C2, D2 contain certain data that is spread across different parts of Sheet 2. I want to populate them and save as PDF, then I want A3,B3,C3 & D3 to re-populate the fields & overwrite the previou sones and save a PDF with a new name (or just sequential).
Is this possible or not?
I have a little experience with VBA in Access and some experience with Swift & other programming languages so I know some basics.
1 Answer
1
The actual solution will depend on how your data is staged, but as an example consider the following data.
On Sheet 2 set the print area to the portion that you want the PDF to include.
Then this loop would create a PDF for each ID that is found in Column A on Sheet 1:
Private Sub exportToPDFLoop()
Dim rng As Range
Dim c As Range
With ThisWorkbook.Sheets("Sheet1")
Set rng = Intersect(.Range("A:A"), .UsedRange)
End With
For Each c In rng
'Skip Row 1 (headers)
If c.Row > 1 Then
With ThisWorkbook.Sheets("Sheet2")
.Range("B1").Value = c.Value
.Calculate
.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
ThisWorkbook.Path & "testfilename_" & CStr(c.Value) & ".pdf", Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=True
End With
End If
Next c
End Sub
Hi, how would I go about renaming the PDF as "Range"B1".value (say each PDF will be the ID on your example?
– dvd.Void
Sep 18 '18 at 15:14
Edited my solution to skip row 1, since that probably contains headers.
– Valon Miller
Sep 18 '18 at 15:30
I think I misunderstood your follow up question. In my solution above, the PDF is already being renamed using the ID by this line:
ThisWorkbook.Path & "testfilename_" & CStr(c.Value) & ".pdf". CStr(c.Value) is the Id that is going in B2 on each iteration. You can modify that file name concatenation as needed.– Valon Miller
Sep 18 '18 at 15:35
ThisWorkbook.Path & "testfilename_" & CStr(c.Value) & ".pdf"
CStr(c.Value)
Yes, Thank you. I dont know why i thought C was a counter not the actual value. That was my bad.
– dvd.Void
Sep 18 '18 at 15:38
As of now I'm modifying the entire worksheet using VLOOKUP and itearting over one value. But just as a question, if I would want the code to change more than one cell would I need a new rng array range and a new c counter (say rng2 and c2) and another for loop?
– dvd.Void
Sep 18 '18 at 15:40
Thanks for contributing an answer to Stack Overflow!
But avoid …
To learn more, see our tips on writing great answers.
Required, but never shown
Required, but never shown
By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy
This is totally doable, you need to iterate through the rows on sheet 1 to populate sheet 2 and export a PDF on each iteration. Can you please post the code that you have so far and identify any lines that are throwing errors?
– Valon Miller
Sep 17 '18 at 20:59