Create multiple PDFs based on a list in Excel | Excel Off The Grid

hello it's mark from excel off the grid here in this video we're looking at how we can save down multiple pdfs by looping through cells on a worksheet so you can download the example file and there are links in the descriptions box below then you'll be able to work along with this video so once you've done that if you're ready let's get started [Music] okay so here we are inside excel and what we have is a student exam record report so all that happens on here is that we have this data validation list and as we select items from that data validation list that report then updates for the student that has been selected this data validation list is based upon these student ids which are contained here so if i click on that cell and go to data validation you can see that it's a list based on cell i4 which is that cell up there now for this i have used the unique function also in this report i have used the filter function and i've also used the x lookup functions which means if you don't have a dynamic array enabled version of excel so excel 2019 and before then this report won't work for you however we're not really focusing on the functions that we're using we're focusing on how we save down those pdfs using a macro and the macro is not dependent on any dynamic array functionality so even if this main report doesn't work you can still work along with the example just using some of your own data now each of these student reports come from data that's based on this data report where we have student id first name last name exam score and classification finally on here we have a shape that's called save pdfs we will assign our macro to this shape so that each time we want to save down these pdfs we can simply click that button and it will run the macro for us so that's our scenario now let's head over and look at our macro to open up the visual basic editor you can come to the developer ribbon and then click visual basic if you have the developer ribbon enabled if not you can just press alt and f11 and that will open up the visual basic editor here we have my project here my workbook i'm just going to right click on there and go to insert and select a module and this will be the module that we use for our macro so let's start coding it now sub and i'm going to call it save pdf from list okay the first thing we need to do is to declare our variables the first variable that we want is the is for the worksheet so i'll go dim ws as worksheet and further down i'm going to determine what that worksheet is so w set because it's an object ws equals the active workbook dot sheets and it'll be the report and the sheets called report on my example workbook and that's how we reference the report sheet the next item we need to declare is the range where that student id is so that's the one that when we change that value it automatically updates the rest of that report so i'm going to dim call it rng id as range again because this is an object i'm going to say set rng id and that will equal my worksheet because it's on my report tab and it's range that's in cell g4 so let's just take a look at that it's that cell there so g4 next we want to reference our start point for our list so dim rng list start and that'll be as range as well so my list start is here in cell i4 and reference start of the student list so again this is an object so we set rng list start equals ws.range cell i4 now let's create the file path that we want to use for each of our pdfs so again we need another variable so dim pdf far path that'll be a string reference the pdf file path pdf file path equals now i've already got a folder set up that's this one here so c test folder pdf export let's say we want to call each of our pdfs that we've saved down as student report we then want say the student id to pull through and it should be dot p d f next we need to count the number of rows that we have within our list some call this dim rows count as i made that along just in case there's an exceptionally large number of pdfs that we will be using so count a number of student ids so row count equals the rng list start so this item up here dot and i want to take the current region so however many cells there are in that area dot rows dot count so what that will do is start cell i4 and then look at all of the cells that are in that contiguous range so that does also include the header row which means which means we will just minus one from that so minus one right it's now time for us to create our loop for that i'm going to create another variable that's just called i that just helps us as a counter i as long so 4 i equals 1 2 rows count so this will keep looping from number one all the way up to the number of rows that we have uh this item i have misnamed should be rows count and then at the end because it's a four let's go next i so it then loops to the next number so we'll start with number one once it's finished that process it'll then loop come to next die go to number two next i go to number three until we get to that total rows count number so let's start here in this loop by changing the cell value change the current student id so that's rng id dot value rng list start so it's offset how many rows do we want to offset well it's it's the i so it's the counter so we want to offset by one and the next time offset by two but because our first item is in that first cell we just need to minus one we don't want to offset by any columns so that's zero and then dot value so each time we look through that will change that cell value that cell g4 next we want to find a way of replacing the letters id with the student id that we're actually using for each of our loops for this i'll just create a new variable so we've got temp pdf file path a string to replace id with the current student id so we say that temp pdf file path equals the replace and we want to replace the pdf file path we're replacing id with our rng id dot value so we're going to replace that with the value that's in the cell so all that means is that each time it saves a file it's going to save in c test folder pdf export student report and then it's going to replace each time that id with our student id number so it creates a unique reference for each of our pdfs finally we now need to export or create the pdfs so for this we go to ws.export as fixed format the type it lists them there so we can have type xl type pdf or excel type xps curl on equals on excel type pdf comma and then we need to declare the file name my file name is going to be my temp pdf file path right let's lose that line there now let's just make this a little bit more efficient so we want to stop the screen updating during running so dot application.screen updating equals false and we'll turn the screen updating back on at the end so restart screen updating equals true okay let's see if this will now run let's assign it to our button so assign macro save from pdf list okay i've got my folder here let's click the button and see what happens shall we oh there we go i've got a named item that's not valid file file that wasn't the argument was it file name you can see it there file name stop that macro and re-run it which run quite quickly come back to our folder there we go we now have pdf saved for each of our individual students it only took probably a second to save eight of those so if you've got a lot if you've got tens hundreds even then it should only take a few seconds to create all of those pdf reports for us there are so many circumstances where you could use this type of approach so you will need to adapt this code to your specific scenario and the bits of code that you really want to look at changing are here where we define what the report is here where we define what that cell is that we change then drives the rest of the report this cell here where we define where that list starts and also where our pdf file should be saved and i've used id down here and then i've also used id here so those should be the same pieces of text and then here in this bit where we create our temp pdf file path we then say what we want to change that id code with so we've used the um the id value we could have equally used the student name or some other kind of information that's it from me hopefully you found this video useful and you'll be able to put it to good use so thanks for watching don't forget to subscribe like and comment and i'll catch you next time [Music]

Channel: Excel Off The Grid

Views: 32,093

Keywords:

Id: -miNt27LFcA

Length: 13min 2sec (782 seconds)

Published: Thu Mar 03 2022