Convert Army PDFs to Excel
MORPHO DESIGNS SOFTWARE
"Powered by ParserMonster!"
Mitchell Allen – Support-Report date: Friday, January 12, 2018
SAWT USER'S GUIDE
Table of Contents
Package Contents .................................................................................................................................................... 3
Quick-start............................................................................................................................................................... 3
Excel Control Panel ................................................................................................................................................ 4
Folder Picker ....................................................................................................................................................... 4
New / Edit Forms ................................................................................................................................................ 5
Data Entry: Navigation ................................................................................................................................... 5
Data Entry: Field widths ................................................................................................................................. 5
Data Entry: Formats ........................................................................................................................................ 6
Import Forms ...................................................................................................................................................... 6
Save ALL Forms ................................................................................................................................................. 7
Save DA ##### ................................................................................................................................................... 7
Close ................................................................................................................................................................... 7
PDF Status .......................................................................................................................................................... 7
VB.Net Data Entry Form ........................................................................................................................................ 9
Clear Current Form ........................................................................................................................................... 10
Clear ALL Forms .............................................................................................................................................. 10
Save Forms And Return To Excel .................................................................................................................... 10
Return To Excel Without Saving ...................................................................................................................... 10
SAWT.csv ............................................................................................................................................................. 11
MORPHO DESIGNS SOFTWARE
"Powered by ParserMonster!"
Mitchell Allen – Support-Report date: Friday, January 12, 2018
Design Notes For User .......................................................................................................................................... 11
Font Choices and Limitations ........................................................................................................................... 11
Audit File .......................................................................................................................................................... 11
Developer's Notes ................................................................................................................................................. 12
MORPHO DESIGNS SOFTWARE
"Powered by ParserMonster!"
Mitchell Allen – Support-Report date: Friday, January 12, 2018
PACKAGE CONTENTS
Your project includes the following files:
•
Soldier APFT and Weight Tracker.xlsm (referred to herein as CP, for Control Panel)
•
DataEntry_SAWT.exe (referred to herein as DEF, for Data Entry Form)
•
SAWT Users Guide.pdf (this document)
•
DataEntry_SAWT 1.0.0.nnnnn.zip (For developer use only: source code for DEF)
QUICK-START
SAWT refers to the project named Soldier APFT and Weight Tracker. It was designed with future projects in
mind. The future projects may well change elements of this ad hoc framework; therefore, do not rely on this
document to resolve issues in other projects.
The framework will be referred to informally as XDTP, short for Excel Desktop Publisher. It consists of three
main components: an Excel-based Control Panel (CP), a VB.Net-based Data Entry Form (DEF) and the raw
data file that is shared between CP and DEF (CSV). The output of the framework is several PDF files.
To use XDTP for SAWT, double-click the CP file: Soldier APFT and Weight Tracker.xlsm.
If you get the Microsoft Excel Security Notice, click Enable Macros button and then click Enable Content:
MORPHO DESIGNS SOFTWARE
"Powered by ParserMonster!"
Mitchell Allen – Support-Report date: Friday, January 12, 2018
After a few seconds of initialization, you should see the CP window.
Each part of the CP is explained in the following sections. Always ensure that the version is the latest.
EXCEL CONTROL PANEL
FOLDER PICKER
Current Path is used by CP to communicate with DEF, as well as to save the PDFs. To change Current Path,
click the folder to open the standard Windows Folder Picker Dialog. If you press the Cancel button on the
dialog, CP will set the Current Path to your local \Desktop.
Current File is the name of the file shared between CP and DEF.
Current Name is the soldier who is being processed. All PDFs related to the current soldier begin with that
name. If you forget to enter a soldier's name in DEF, the file name will be _Test. You can go back into DEF and
type the name, then repeat the Import Forms task.
MORPHO DESIGNS SOFTWARE
"Powered by ParserMonster!"
Mitchell Allen – Support-Report date: Friday, January 12, 2018
When you first open CP, the folder picker's data area is blank. After you click any button, the Current Path
will be updated. The Current File is always SAWT.csv, whether the actual file exists or not. Current Name
remains blank; it is updated after a successful Import Forms operation.
NEW / EDIT FORMS
When you click New / Edit Forms, CP launches the VB.Net program DataEntry_SAWT.exe in full-screen
mode. DEF is explained in the VB.Net Data Entry Form section.
Each time you click this button, a separate DEF is launched. This behavior is by design, as it prevents
accidentally losing unsaved data. However; no matter how many DEFs are running, they all save to the same
SAWT.csv file. In general, it's safer to close the duplicate DEF if you click this button more than once.
DATA ENTRY : NAVIGATION
[insert notes about tabs, enter, etc.]
DATA ENTRY : FIELD WIDTHS
The amount of text you can type into the yellow fields on the DEF tabs bear no relation to the amount of text
that can be display on the final PDF. Please keep the following points in mind:
•
The yellow fields had to be shoe-horned into the available screen space, so some are absurdly smaller
than their PDF counterparts
•
Because of the smaller sizes, DEF allows you to "type past the boundaries", but be sure to check the
PDF preview to see where your input is truncated
•
DEF uses a fixed-width font, so a maximum length can be associated with each field, based on PDF
MORPHO DESIGNS SOFTWARE
"Powered by ParserMonster!"
Mitchell Allen – Support-Report date: Friday, January 12, 2018
•
Formatted fields (dates, times) may cause truncation in the PDF
•
If the results are unsatisfactory, consider using terser language, shorter synonyms and/or less
punctuation in formatted fields
DATA ENTRY : FORMATS
Formatted fields (dates, times) can simplify data entry. All dates use a date picker pop-up:
Several fields use a format for time. Just type the number pairs without punctuation. DEF calculates the proper
location of the colon, based on these rules:
You type
DEF responds
Output is
One digit
Appends :00
m:00
Two digits
Appends :00
mm:00
Three to five digits
Inserts : before last two digits
mm:ss
Anything else
Changes background to red
unchanged
IMPORT FORMS
When you click Import Forms, CP looks for SAWT.csv, the data file written to Current Path by DEF. If the
file is not found, you will see this error message:
MORPHO DESIGNS SOFTWARE
"Powered by ParserMonster!"
Mitchell Allen – Support-Report date: Friday, January 12, 2018
Either change to the correct folder path or perform the New / Edit Forms operation.
SAVE ALL FORMS
This is a time-saving button for the few instances where you know you'll need to print all three forms. The
PDFs are saved to Current Path as Last Name, First Name DA Form Name YYYY MM DD.pdf.
SAVE DA #####
When you click any of the Save buttons, a PDF is generated for Current Name, as described above.
CLOSE
When you click Close, CP removes itself from your Excel application. Note: if you try to close CP using
Windows' methods, you will get this message:
Close prevents that message from popping up.
PDF STATUS
MORPHO DESIGNS SOFTWARE
"Powered by ParserMonster!"
Mitchell Allen – Support-Report date: Friday, January 12, 2018
The PDF Status area will remind you which forms have been saved. If you click the folder icon, you will clear
the reminders. Doing so will not delete any files.
MORPHO DESIGNS SOFTWARE
"Powered by ParserMonster!"
Mitchell Allen – Support-Report date: Friday, January 12, 2018
VB.NET DATA ENTRY FORM
When CP calls DataEntry_SAWT.exe, you'll see it flashing for several seconds before it opens in full-screen
mode.
This data entry form (DEF) has tabs for each page of the forms. The tabs are not exact replicas of the PDFs.
They simply squeeze all the fields onscreen so that you can quickly tab from field to field.
Here are some usage notes:
•
The Enter key on your keyboard only works in comment boxes; however, multiple lines are reformatted
as a single line before saving to SAWT.csv. This allows Excel to apply Wrap Text to the comment
boxes. So, don't expect the same line breaks to occur in the output.
•
When you tab into a "radio button", you won't see where you are. You can press the Space bar on your
keyboard to select the current radio button. You can press the Right arrow button on your keyboard to
MORPHO DESIGNS SOFTWARE
"Powered by ParserMonster!"
Mitchell Allen – Support-Report date: Friday, January 12, 2018
toggle between the choices (except on forms DA 5500 and DA 5501, because those radio buttons are not
grouped together.)
•
Each field has properties that can limit what you type. If a field does not allow you type needed
information, be sure to notify the developer and request a fix. Due to the presence of over 200 fields,
some are likely to be improperly formatted!
CLEAR CURRENT FORM
Self-explanatory. The action is immediate and cannot be undone.
CLEAR ALL FORMS
DEF immediately clears every input field on each tab and resets all Date fields to the current date.
SAVE FORMS AND RETURN TO EXCEL
DEF will not save forms if a gender has not been selected. Instead, it will display this error message:
DEF writes all fields to SAWT.csv, including blanks. It then minimizes itself so that you can see Excel and the
CP. The DEF is just an icon on your taskbar:
If you discover mistakes after importing the data to CP, click that icon to bring up DEF with the current
soldier's data intact. Do not click the New / Edit Forms button on CP! That will open a new, blank copy of DEF.
RETURN TO EXCEL WITHOUT SAVING
DEF abandons the current task and closes itself.
MORPHO DESIGNS SOFTWARE
"Powered by ParserMonster!"
Mitchell Allen – Support-Report date: Friday, January 12, 2018
SAWT.CSV
This file contains the data from DEF in a format that allows CP to properly import and map to the appropriate
PDFs for saving. A future version of the XDTP framework will provide for saving and reusing SAWT.csv on a
per-soldier basis.
DESIGN NOTES FOR USER
FONT CHOICES AND LIMITATIONS
During development, fixed-width fonts, such as Courier New, MS Gothic and FixedSys were compared to the
proportional font, Calibri 11. While Calibri looked better than any of the fixed-width choices, it was difficult to
pin down the maximum character length for data entry fields. This was further complicated by the independent
field sizes in DEF with respect to the actual space available for PDF printing.
The drawback to using fixed-width fonts is that maximum character lengths are smaller than the corresponding
proportional font. However, the grids on the forms further reduce maximum character lengths, due to
compromises required to shoehorn Excel worksheet cells into the non-uniform grid layouts.
Later, these limitations might be resolved. There are several options, not all of which are currently available:
•
Switch framework to Microsoft Access
•
Switch framework to a full-blown PDF authoring system (fillable forms would solve everything)
•
Software-calculated character counts using proportional font, tied to PDF maximum widths
AUDIT FILE
Eventlog.csv currently records every session indefinitely. You have to manually delete it to clear the log. This
document will be updated if the retention policy changes.
One other thing, which is more of a concern for your developer: the audit file displays semicolons in place of
commas in data fields. So, where the PDF displays Smith, John, the audit file displays Smith; John.
MORPHO DESIGNS SOFTWARE
"Powered by ParserMonster!"
Mitchell Allen – Support-Report date: Friday, January 12, 2018
12. Here is the formula, which you might be able to paste, but be sure to remove line feed and space at
beginning from Evernote
13.
14. =IF(IFNA(INDEX(SAWT,MATCH("rad705p2Wt5Go",SAWT[ControlID],0),7),"")="True","","")
15.
16. Radio buttons related to check boxes must be given a descriptive name. They only get exported if true.
So,avoid confusion by naming like example in #14 above.
Clean up camera shots:
1. remove line objects and checkbox objects, as they will be replaced with textboxes and radio buttons on
the form
2. Note any data format reminders, such as shown in the screen shot, below. Set VB form datepickers to
use Custom format. Then remove reminder from snap-shot to make room.
Tips for ensuring page 2 matches page 1:
1. For forms having multiple pages of similar layout (such as DA Form 705 p1 and p2), it's actually better
to take ONE camera shot and save multiple copies.
2. Edit the copies in IrfanView to excise dissimilar text and overwrite with the page 2 text
3. You won't be able to overwrite edited images while VB.net is open, so just save revisions to copies as
v2, v3, etc.
4. After completely populating the table layout grid for page one, copy it to page two and update the
background image.
MORPHO DESIGNS SOFTWARE
"Powered by ParserMonster!"
Mitchell Allen – Support-Report date: Friday, January 12, 2018
Comparing Official table to online product