Using an Excel Database to Autofill PDFs Part 2 (Auto-populating Multiple DA Form 4856s at Once)

With the previous tutorial, we managed to make a Macro that could auto-populate a single DA 4856's header in a single click. However, why was all that work necessary? We could have simply filled in the header directly. But let's say now we want to fill in 100, let's make it 1000 PDF headers instead. under normal circumstances, you'd have to make the template, copy and paste it into all of the folders and rename each of the files. If you do this process constantly, then look no further.

"But why was the first tutorial necesary?"

The first tutorial served as a base for what's to come. With it, we just have to modify a few lines of code, and we'll have it working. It will be a bit more complex now, so we'll take it easy and go step by step.

If you did not see part 1 of this tutorial, I recommend you take a look before digging into this part, as this one follows up where the previous one ended.

"Let's begin!"

Using the previous file we've made, we know it can properly save one PDF in a location of your choosing. Now instead of reading one row, we need it to be able to take data from multiple rows. Let's say 10 for starters. There is a command called For. This command basically states, "for every item in." In this context, we want it to be "For every item in rows 2 through 11" in order to get the 10 rows we want. This is how it should be.

This is stating that "i" is a new variable and it will become 2, 3, 4, and so forth, but during each change, it will complete the instructions that follow until it reaches the command Next.

"So we have the variable to indicate the rows. Now to impletement it."

Here comes the complex part. The variables we declared during the first tutorial have to be moved and slightly modified. If not, you will get the same row 10 times. Time to cut and paste the 7 lines declared into the For section. The modification will be shown below.

You'll notice the declarations have changed. Now they point to the column letter, followed by "& i". This is so that during each iteration of the For command, it will change the variable to the next cell in the row. Now we know that the 10 times this occurs, it'll have the information of each row. That brings us to yet another problem.

"It's going to save in the output file, then overwrite it 9 times!"

Indeed, this is what we have to avoid, so why not give the file a more useful name. We have Example 1 in our previous image. Why not call it "Initial Counseling for John Doe" where John Doe is the Soldier it is for? The i variable will again be useful in implementing this. Take a look at the new alteration.

What a mess, am I right?" But if you look at it, it actually makes sense. The file location did not change as we want the file to be in the same folder, however now the file name has the beginning of Initial Counseling for , and it concatenates the Soldier's rank, last name, first name, and the PDF extension. The underscore also has a purpose in this method. We could write a line that does not stop, but the underscore lets us go to the next line while still accepting it as one single line. It's not fun to scroll right then left constantly, and some codes are long in programming. So let's see the output!

"It works! Am I done?"

Well, there you go, big shots. You've done it. You've made an actual program and it works. I am proud of you, as you should be. With this simple database and Macro, you can just make a PDF template with an empty header, and simply auto-populate in a few seconds. However, this is only the beginning! A few flaws that are necessary for the testing stages. The message box showed up 10 times to annoy you. Good, to know it works, but now you know it does. You can change it to the end of the Macro now so it only tells you at the end that all 10 have been successful to improve speed. Do you have 100 Soldiers? Change the For a variable to as many as you want. Do note that if the next file has the same name as a previous one, it will overwrite the first one that came out. You can bypass this by maybe using a serial number or roster number. Maybe an ID number.

With these tutorials, you should now have a more than basic knowledge of how to auto-populate PDFs by using an Excel database. If you have any questions or doubts, be sure to comment in the section below and I'll get back to you ASAP. Again, if you've completed the tutorials successfully, congratulations! If not, we're here to help.

Next tutorial heads-up: How to send those PDFs via EMail at the same time to the corresponding Soldier. Like if you're interested.