Background
While the main goal of the historical index
conversion to electronic data was a central database with all HI pages, we
still wanted to provide a way to present this data in a more readable format.
The HI database provides a way to export a historical index for an entire
township at once, however this data is saved in a .csv format which is not
easily readable nor does it comply with BLM regulations for a final product.
Therefore, the need for a word template arose. The goal of this template is to
have a bare bones document that follows formatting restrictions for historical
indices listed in the 1275 manual. This document would allow for data from the
exported .csv files to be uploaded into it, updating the formatting of the data
and creating a publicly viewable version in word or pdf.
Overview
The HIET is a word template built using Microsoft Word
(2007-2010). The main function used to upload data into the template is a mail
merge. This tool has its limitations; however, it was determined to be the best
possible way of quickly and easily uploading data into the template without
needing to make further changes.
Like the HICT, this export template must have specific
elements tailored to the state whose data is being worked with. Therefore, we
have slightly different versions of the HIET for different states. Because of
this I will cover the basic structure of the HIET, what you can expect to stay
the same and what you can expect to change between each states version.
Common Elements
In this section, we’ll look at
the main components in the HIET that will not change between any version. Please
make yourself familiar with Microsoft Word and VBA before attempting to make
any changes.
Template Formatting
This section may be subject to change in the
future, however, currently all states who are participating in the HI
conversion process have opted to use the Oregon HI format, and therefore all
templates will have the same formatting rules as Oregon’s historical indices.
Mail Merge Macro (VBA)
All states will use a macro that points to VBA code to run
the mail merge function for importing data into the template. Much of this
process will not change between states, I’ll talk about those elements here.
Initiating Macro
This is the sub that is called when you first run the macro.
Its job is to prompt the user for a list of .csv files that they wish to
convert into word documents following the template formatting rules. Once at
least one file has been selected, the ConvertData function is called.
VBA Handler: ConvertToWord()
Source: ThisDocument, line 99
Convert Data
This sub procedure was creating using the Record Macro
option within Microsoft Word’s developer tab. The output has been slightly
altered to allow additional work to be done. After running the Mail Merge, we
also call another sub FixData before saving
and closing the merged document.
VBA Handler: ConvertData(filePath)
Source: ThisDocument, line 1
Fixing and Updating Data
The cleanup done in this function is limited to issues with
the Mail Merge as well as updating page headers with the township information.
This function does not update data according to standards or fix common issues
found in data. These updates should be made in the Access Database so that this
part of the process can be as simple, streamlined, and universal as possible.
Instead this function performs operations like cleaning up fractions and div
tags which occur due to the Access export process. To update the header, the
function GetHeader (see the state specific
elements section) is called.
VBA Handler: FixData(filePath)
Source: ThisDocument, line 27
Generating a New File name
Uses the upload file name (which should be the PLSSID if the
file was exported from the HIUD) to generate the new file name (which should
also be the PLSSID with lowercase township and range direction letters). Uses
the FolderFromPath and GetFilenameFromPath functions that were found
online.
VBA Handler: GetFileName(filePath)
Source: ThisDocument, line 214
State Specific Elements
In this section, we will cover any elements
which are subject to change based on the state whose data we are processing.
Generating Page Header Text
Using the uploaded file name, attempts to generate the
Historical Index header text. Currently, states participating in the HI
conversion process have different versions of this sub procedure as the state
and meridian values are handled lazily. In the future, this function will be
updated to pull this information from the upload file name as well. At that
point this would no longer be a state specific element.
VBA Handler: GetHeader(filePath)
Source: ThisDocument, line 133
Troubleshooting
During regular use, the user may run into certain issues.
Below is a list of commonly experienced problems and how to go about fixing
them.
Macro doesn't appear to do anything
This is usually a trust center issue. Follow the steps below
to solve it.
- Using the Microsoft Word Menu go to File >
Options.
- The pop-up options window will have a menu on
the left side. At the bottom of this menu is the label Trust Center. Click
this.
- On this page, press the Trust Center Settings
button.
- The Trust Center window will now open. On this
window’s menu, click on the Trusted Locations label.
- On this page, press the Add new location button.
- On the new window, press the Browse button.
- Browse to the folder in which the HIUD Access
Database is located and select it. To be safe, you can also check the
‘Subfolders of this location are also trusted’ checkbox before pressing the OK
button.
- Press OK on the remaining two windows and then
close and reopen the entire database to refresh the trust settings.
- The form buttons should now
work.