Using an Excel Database to Autofill PDFs Part 1 (Tutorial with a Military DA-4856 PDF Form)

You read correctly. There is a way to fill limitless PDFs with a simple click in Excel. If you are a teacher that has to print 20 different reports for each of your students, this can be for you. If you work in a company that has to do receipts and would like to keep a record at the same time, this is for you. If you are a US Army Leader who has to fill out over 320 PDFs for your Soldiers, this is definitely for you.

Disclaimer: In no way am I trying to prevent Military leaders from interacting with their Soldiers. Use this knowledge in a professional and productive manner and never forget, a computer screen will never replace the true essence of face to face interaction during a developmental counseling session.

"Before we begin, the setup for this tutorial."

As I mentioned at the start, the form to be used is the DA Form 4856 (Developmental Counseling Form.) However, before we begin, I would strongly urge you to get a form from the Army Publishing Directorate. The reason I urge this is because the form has many copies and alterations already, and if you want to use an original one with the standard settings, this would be the best option. You'll see during the tutorial how to check if you have an original or altered one. This tutorial will suppose you have everything filled out EXCEPT the header section of the form. The rest will be used as a template, as in the specific counseling is to be made for limitless Soldiers. This is in no way a restriction. But other tutorials will go more in-depth into the complexity of it all. Once you have the form and Excel ready with Developer Options unhidden (If they are hidden, click here) you are ready to begin.

"We have our form and we have our Excel. Let us begin with the Excel and the basics."

At this time, you can open an excel and prepare it with the 7 columns you'll need. Below are examples of the names you can use for the column headers. (Only 5 are required, but the extra 2 are a demo of what can be done if you desire to separate first and last names on the Excel.) Once you have that, fill out at least one row. This tutorial will begin with auto-filling the PDF for only 1 Soldier, then we'll expand from there.

"We have the Excel with what we need for one person, now onto the magic, VBA."

Open the Developer Tab and go to the left where it says Visual Basic. A new window will open called Microsoft Visual Basic for Applications and your file name. You are now in the Interactive Development Environment (IDE). Once here, go to the left where it says Project- VBAProject and right-click anywhere within the box. In the dropdown, go to Insert, then Module. A new empty window opens within the main window. This is where we create our Macros. I will attempt to explain as best as can each portion, but I will focus specifically on the lines written. This is in no way a complete class for VBA. Inside here we will create a "Sub". Consider it a "Subtask" that will be run whenever we want. Do so by writing without the quotations, "Sub fillCounsel()" and press enter. You should see the following.

"So the canvas awaits us. What will it expect..."

Inside the Sub is where you will write your first program to autofill a PDF. You can change "fillCounsel" to whatever you desire, but it is good practice to title a Sub based on what it does. Before we continue writing and expecting things to happen, we should prepare VBA for what it should expect. We know we have 7 columns at this point and now would be a good time to turn those into "Variables". Consider a Variable a box. We will give that box a name, and store something within it. In this case, we will need 7 boxes. To initialize (Create) a variable, the word "Dim" is used. After Dim, you will use whatever name you want, unless it is a name that is already reserved for VBA, such as "Sub", or "Then". After that, you will have to be specific about what type of data it will store. Is it a number? A word? A letter? Yes, this must be specified. In this example, we will use "As String". Once that is done, the variables have been initialized. See below.

"Congratulations, you are one step closer to being a programmer! You initialized a variable. Time to declare it."

If you did any mistakes in syntax or the way it's meant to be written, a popup box will tell you so. (Popup can be disabled by going to Tools, Options, and unticking "Auto Syntax Check".) So you have 7 empty named boxes. Let's give them something to store. In other words, Declare what it will store. These may seem like extra unnecessary steps, but in the long run, they will save you so much time. Best to get used to it. Personally, I initialize everything I want in one section, then I make a new section separated by an extra new line for declarations. Feel free to give it 2 Enter spaces after the last variable initialization. We want those variables to store the info in the excel under each header, and we have to tell it to VBA just like that. Think of it like this. Variable, store the following. Go to this specific Excel sheet, then to this cell in specific, and what it has inside is what I want you to store. An example for the soldier's rank would be the following with quotations where placed, soldierRank = Sheets("Sheet1").Cells("A2").Text. That's it. Below is the example.

"Done with the basics of initlializing and declaring... for now. Let's check out that DA Form 4856. Hope it's original..."

For now, we can pause the Excel. Save it, close it, and take a breather. You've earned it if you made it this far. Let's open that DA Form 4856 that we'll use for this demonstration. you can actually use any PDF you want, but know you'll have to alter the code so that it works for you. Once you open the form with Adobe Acrobat Reader (free or paid), take a look at it. Sometimes, it's near impossible to find the difference between an original updated one, and an altered or outdated one. Once the form is open, click on View, then Show/Hide, after that goes Navigation Panes. followed by Tags. You'll see a list of tags on the screen now. This part can seem awkward if you don't understand the fundamentals of programming, so I'll guide you in a simple way. The order goes, Tags, <Document>, <Part> Page 1, <Sect>, <Div>. You'll see plenty of <Div> at this point, so go down the list one at a time and it will highlight specific sections of the PDF. Stop when you see the first fillable highlighted. Open that one which shows <Form>. Open that and it shows you an odd name such as "form1[0].Page1[0].Name[0] - OBJR". That is exactly what you want. Note that this name is the correct variable for the original DA Form 4856. If yours shows differently, it may be an altered version. Save that entire name except for the dash and OBJR. In other words, you'll end up with "form1[0].Page1[0].Name[0]". That is actually the variable of the fillable block. If you repeat the process, you can find the variables for every fillable block. Repeat the steps till you have all 5 header fillable variables saved somewhere. They will be necessary for Excel to interact correctly with the form.

"Before we head out..."

Once done saving the information required, go to close the PDF but make sure you save its exact location on your hard drive. You can do this by saving the folder path and adding the file name with an extension. It would look somewhat like "C:\Users\YourName\Documents\DA 4856.pdf". With that, we have enough to complete the macro now. So back we go.

"Back to coding."

We have assigned the variables to be read from Excel, so now we will program the Application Program Interface (API) for Excel to communicate with PDFs. This part is a bit more tricky. Again, this is in no way a full-scale tutorial of APIs or VBA, so it'll be a bit more direct at this point.

You'll want to again initialize some variables, but with no declarations yet. Not only that, but we will also "set" variables to objects as well. We will now play more with different data types. We will allow VBA to determine the data type for two variables, and we will create an object. Then we will set both variables that were left without a datatype to objects with the PDF API. See the example below.

"We've made it far. Just a little more. Time to unite what we have in VBA with the PDF."

Luckily you saved the variables and path of the DA Form 4856 because here is where we're gonna need it. I will now tell VBA what to do with the PDF, as well as where to save the new completed file.

The form has 5 fillable text boxes in the header, therefore we have 5 variables we need to initialize to later declare. You can not declare a variable in VBA that has not been initialized, even though other programming languages do permit it. We will also set the jso variable in order to get the PDF form with a specific line. First, we will verify the path we saved works by performing a simple "if" statement, and if it exists, then the next procedure will run. Here is an example. Note that the template path and output path added as well. Beneficial to have them early on as well.

When it comes to programming, I like to teach that your first perfect result will never be perfect. You can usually add variables and more functions to make it more efficient and what programmers call optimized. Never feel bad if you have thousands of lines and someone else has a fraction of it. When it comes to program sizes, honestly, size does not matter. If you know how to fully optimize a program, it can run much faster and look cleaner than someone who just writes nonstop.

"We have the Excel, we have the PDF, we have everythng. And now?"

Now we'll start making magic happen. We will "set" the PDF variables to the VBA field variables. Sounds complicated, but if you've followed this far, you should understand it to an extent. The same applies as it did with variables. You want to set the VBA variable with the PDF variable. Remember the variables we took from the original form. Here is where they come into play. See below.

"It all starts to come together now."

At this point, everything we did earlier starts making sense. We have set our variables to those of the PDF form and want to put the value we want into them. To do so, we will start with the rank. The order at this point does not matter. I will be starting with rank because it will be the easiest. Simply add "fieldSoldierRank.Value = soldierRank. That simple. With that, the value inside the PDF fillable field will become the result of soldierRank from Excel. The same goes for the rest, however, the PDF requires the last name, first name, then middle initial all in one field. How can we do that when we have it split?

"Concatenation"

Concatenation is the fusion of two or more collections of letters. In simple terms, multiple sentences fused into one. In the scenario of the first and last names, we'll want to place them together properly. The same would apply as the previous except here, we would need to unite both the last and first name, with space in between. The code would go from this point on with quotations, fieldSoldierFullName.value = soldierLastName & " " & soldierFirstName. The same would go for the counselor which requires two in one and with a forward slash (/) Below is the example.

"Time to save the result."

We are nearing the final steps. From here, we just need to know where to save our product, which luckily you chose a folder path. At the end of the folder path, just add the file name you desire with the extension ".pdf". The examples given for the paths in my examples should be changed to match your actually file paths. Attempting to copy and paste the paths on the examples will not work. Once you have made it this far, you'll want to make a Message Box show up to tell you it was successful. Once that's done, you'll just have to ensure to PDF API closes correctly. You will see the code at the end of the following example.

"Almost done. How do I activate these codes without having to use Visual Basic?"

So now we have the complete Subtask ready for action. We can always call it from Visual Studios, but that would be kind of annoying each time. So here is the trick. Return to the actual Excel and under the Developer Tab, look for Controls and Insert. There, you will see Form Controls. Look for the Button and add it to the spreadsheet. Once you do so, it will require you to select a Macro or a Sub for it. Select the one we just made which is fill Counseling and click Ok.

"And voala!"

The project is done. For now. This is only part one. No one said programming is easy, but for those that love it and want to make things happen, it is a part of life. Give it a test. Below you will see the full script for the VBA portion.

Sub fillCounsel()

    Dim soldierRank As String
    Dim soldierLastName As String
    Dim soldierFirstName As String
    Dim soldierDate As String
    Dim soldierOrganization As String
    Dim counselorName As String
    Dim counselorTitle As String
    
    soldierRank = Sheets("Sheet1").Range("A2").Value
    soldierLastName = Sheets("Sheet1").Range("B2").Value
    soldierFirstName = Sheets("Sheet1").Range("C2").Value
    soldierDate = Sheets("Sheet1").Range("D2").Value
    soldierOrganization = Sheets("Sheet1").Range("E2").Value
    counselorName = Sheets("Sheet1").Range("F2").Value
    counselorTitle = Sheets("Sheet1").Range("G2").Value
    
    Dim templatePath As String
    Dim outputPath As String
    
    Dim AcroApp, PdDoc
    Dim jso As Object

    'templatePath is YOUR template path.
    templatePath = "C:\Users\Clanc\Documents\Cintron's Folder\RTI\FY-21\Original DA 4856.pdf"
    
    'outputPath is where YOU want it to save the updated PDF.
    outputPath = "C:\Users\Clanc\Documents\CintronAutomations\Blog Tests\Example 1.pdf"
    
    Set AcroApp = CreateObject("AcroExch.App")
    Set PdDoc = CreateObject("AcroExch.PDDoc")

    If PdDoc.Open(templatePath) Then

        Set jso = PdDoc.GetJSObject
        
        Dim fieldSoldierRank As Object
        Dim fieldSoldierFullName As Object
        Dim fieldSoldierDate As Object
        Dim fieldSoldierOrganization As Object
        Dim fieldCounselor As Object
        
        Set fieldSoldierFullName = jso.getField("form1[0].Page1[0].Name[0]")
        Set fieldSoldierRank = jso.getField("form1[0].Page1[0].Rank_Grade[0]")
        Set fieldSoldierDate = jso.getField("form1[0].Page1[0].Date_Counseling[0]")
        Set fieldSoldierOrganization = jso.getField("form1[0].Page1[0].Organization[0]")
        Set fieldCounselor = jso.getField("form1[0].Page1[0].Name_Title_Counselor[0]")

        fieldSoldierRank.Value = soldierRank
        fieldSoldierFullName.Value = soldierLastName & " " & soldierFirstName
        fieldSoldierDate.Value = soldierDate
        fieldSoldierOrganization.Value = soldierOrganization
        fieldCounselor.Value = counselorName & " / " & counselorTitle
        
        jso.SaveAs (outputPath)
        
        MsgBox "Counseling Form filled out successfully."
        
    End If

    AcroApp.Exit
    
End Sub

"How does it look?"

There are some minor errors in the final product such as no purpose, no key points of discussion, or the rank does not have the grade as well. With this basic knowledge shown in this tutorial, those fixes should be simple for you to resolve. For the purpose and key points of discussion, make sure the template form already has those prefilled. As for the rank, I'll leave it up to you, programmer.

I hope you all enjoyed this tutorial. Be sure to leave a like and a comment down below if you did like the tutorial or if you'd like to know more. Part 2 will come soon enough, Thank you for your support.