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.
  1. Using the Microsoft Word Menu go to File > Options.
  2. 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.
  3. On this page, press the Trust Center Settings button.
  4. The Trust Center window will now open. On this window’s menu, click on the Trusted Locations label.
  5. On this page, press the Add new location button.
  6. On the new window, press the Browse button.
  7. 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.
  8. Press OK on the remaining two windows and then close and reopen the entire database to refresh the trust settings.
  9. The form buttons should now work.