---- add-nav-code

Add Navigation Code Worksheet
Add Navigation Code Worksheet

This is a neat heavily used utility, and is used for this section. The facility has now been enhanced, so that there is the option of also creating the htm pages, simultaneously with creating the navigation. To accomplish this, several text files reside in each folder:
<ul>
<li><a href="FolderInfo.txt">FolderInfo.txt</a> (CSS strings, backlinks; options for sub-indexes, descriptions, and shortcut icons)
<li><a href="FolderData.txt">FolderData.txt</a> (the two titles and code for the page body)
<li><a href="FolderCSS.txt">FolderCSS.txt</a> (optional, specific for each htm)
<li>FolderDesc.txt (optional file that adds a description meta tag to the head section. specific for each htm)
<li>FolderTopLinks.txt (optional file with backlinks that are put above the regular backlinks. specific for each htm)
</ul>

There are several options for each path. They apply to both navigation only, and when also creating the files. The options are additive:
<ul>
<li>1 - Create htms for folder, in addition to creating the navigation
<li>2 - Process down one level.
<li>4 - Use htm files only (don't include sub-folder paths)
<li>8 - Strip common beginning of Titles without colon being present (default is navigation titles strip up to a common colon).
</ul>

Off to the right is a list of strings that we strip from the fronts of title tags, when the tags are used for navigation and for creating the site map.
<p>
You have the option of stopping and starting what code in the top file is to be used in the building of the circular navigation links. The strings to embed are in the top right.
<p>
The routines are often run by buttons on the dashboard, and not from this page.
<p>
This is one of three sheets where a macro on Ctrl-R puts the row number you are on into the Row cell.
<p>
If the navigation has changed, the site map for the site will have to be rebuilt.
<p>
The module with the <a href="VBA-nav-code.txt">VBA Code</a>.
<p>
<img src="images/add-nav-code.gif" alt="add nav code sheet">

---- find-replace

Find/Replace Worksheet
Find/Replace Worksheet

There are many options here, with numerous buttons to view and edit what you are working with. For a path, the Edit or View will find the index file.
<p>
The strings looked for can either be entered as a string, or taken from a file that is pointed to. This allows the inclusion of carriage returns in the search and/or replace strings. Instead of replacing, the facility can insert the second string before or after the first string.
<p>
Recursiveness is an option one usually wants turned on.
<p>
By default, the find is case sensitive. This can be turned off.
<p>You have a choice as to what filetypes you find and replace in:
<ul>
<li>htm
<li>txt
<li>htm+txt
<li>css
<li>log
<li>cue
<li>filenames - This combobox option looks in the filename and not in the file content. File type will be ignored. Searching does not search the path, but does search the file extension. There is no replace. There are two options: 1-string within filename, 2-entire filename.
</ul>

There is a facility that lets you search and replace on the results of a prior find. You create a TempList.txt file from the displayed results. Enter templist into the "Top Path or File" cell. Then everything works on that list.
<p>
The module with the <a href="VBA-find-replace.txt">VBA Code</a>.
<p>
<img src="images/find-replace.gif" alt="find/replace sheet">

---- image-utilities

Image Utilities Worksheet
Image Utilities Worksheet

There are many facilities here. All are run from the same path. There is the option to process recursively, but it is never used. There are two path inputs, but only one is in use at a time. This allows one path to remain pointing to a temp folder where scans are put and processed. The second for an ad hoc folder. Pasting and deleting in the input cells toggles the option buttons.
<p>
The buttons:
<ul>
<li><strong>Clean Folder JPGs.</strong> JPG files often contain a lot of junk that can be cleaned out. This calls IrfanView, but the command line call cannot choose the Progressive option, so not as small as could be. And if Jpegcrop is used, this has to be used, as Jpegcrop defaults to saving in Arithmetic coding, which no web browser supports.

<li><strong>Add -f to Ends of all JPG Filenames.</strong> The way my image file naming works, is the original image gets an -f suffix, for full-size. The websize has no suffix. And often the thumbnails have a -tn suffix, though for some thumbnails the suffix is the size.

<li><strong>Lowercase All File Extensions.</strong> Nowhere in my site code do I want capitalized file extensions.

<li><strong>Replace Spaces with Dashes in All Filenames.</strong> Spaces in filenames are very bad. For my file naming, I prefer to use dashes between the words.

<li><strong>Create Gleaner Clippings Workbook.</strong> Reads a bunch of Gleaner clippings and creates a worksheet with them listed in the format that the Clippings database wants them in. Row-by-row the entries can be pasted into the proper date sequence.

<li><strong>Create Tourism Links Workbook.</strong> Jamaica tourism publication scans are chopped up by hotel. Will all images in a folder this will create the entries for the Clippings file.

<li><strong>Auto Strip JPG Filenames.</strong> This looks at a folder of filenames and strips off from the from any characters that repeat. If input is blank, then auto selects what to strip off. If a number, then is the number of characters. If a string, it measures the string to get the number.

<li><strong>Change Beginning of File Names.</strong> Usually used for JPG files, but can be used for any filetype.

<li><strong>Convert Image Files to JPG.</strong> Most processing is done with JPG files, so we often convert over. One quality choice is to save the file in the original estimated quality.

<li><strong>Sizes of Images.</strong> This is a very useful listing utility. There are four columns: Width, Height, Size (KB) and Filename. When you have a slide show from a publication, you generally want all the pages to be the same size. Here you can see the sizes, and using Jpegcrop losslessly crop until all are the same. You can copy the filename column, paste into Notepad, and remove the extensions with Replace. There is also a filter.

<li><strong>Resize Images</strong>. There are two ways that images can be resized. For catalogs and books the reduction is usually to 0.25 or 0.375. Thumbnails are always a fixed size. Like the conversion above, original estimated quality is a choice, though after the reduction it is a new estimated quality. There is also a Sharpening option that can be used for thumbnails. This is useful when reducing a clipping that has text.

<li><strong>Create Link Text</strong>. An indispensible utility. While macros can create image links, this facility creates all others. You have a variety of link styles that can be created:
<ol class="ordlist">
<li>interlace with &lt;p&gt;'s. Default.
<li>surround with &lt;div class="cell"&gt;xx&lt;/div&gt;
<li>same as 1, but has leading &lt;br&gt; with expectation title will be added to each cell
<li>surround with &lt;td&gt;xx&lt;/td&gt;
<li>surround with &lt;span class="floatLeft"&gt;xx&lt;/span&gt;
<li>surround with &lt;span class="floatLeft"&gt;&lt;a href=".htm"&gt;xx&lt;/a&gt;&lt;/span&gt;
<li>surround with &lt;span class="floatRight"&gt;xx&lt;/span&gt;
<li>surround with &lt;span class="floatRight"&gt;&lt;a href=".htm"&gt;xx&lt;/a&gt;&lt;/span&gt;
<li>surround with &lt;p&gt; and &lt;hr&gt;
<li>interlace with &lt;br&gt;'s
<li>javascript slide show (all found in folder will be in slide show). all must be same size
</ol>
<p>
The optional file type allows for other than the default JPG.
<br>By default, images are set to max-width:100%;. But for wide images they get crushed. So this option adds a class telling the browser to not squeeze images.

</ul>
<p>
The module with the <a href="VBA-images.txt">VBA Code</a>.
<p>
<img src="images/image-utilities.gif" alt="image utilities sheet">

---- maintenance

Maintenance Utility
Maintenance Utility

<ul>
<li>The Hyperlinks throughout the workbook are to local files. This not recently tested facility changes them all to web links, so someone else using this spreadsheet can open the files.
<li>A link opens a file listing all the <a href="SpecialChars.txt">special text characters</a>.
<li>A facilty to move files between folders based on a filter string.
</ul>

<img src="images/maintenance.gif" alt="maintenance section on dashboard">

---- picture-albums

Picture Albums Worksheet
Picture Albums Worksheet

You will need to scroll over a bit to see all.
<p>
I bought my first digital camera in September 1999. I put my pictures on PhotoPoint.com. At the time, for picture hosting, they were the one. In December 2001 PhotoPoint abruptly shut down. Their bandwidth costs were too high.
<p>
Now I had all the pictures, but I didn't have my captions. For $25 they sold you a CD with your pictures and captions in an HTML slide show format. I used that as the basis in the spring of 2002 to write my own picture album slide show code in APL. Making it my first automation project. (Instagram started in 2010.)
<p>
The sheet is complicated, as the over 10,000 are organized into various groupings. And there are another 2,000 travel pictures that have not yet been added.
<p>
This is one of three sheets where a macro on Ctrl-R puts the row number you are on into the Row cell.
<p>
The module with the <a href="VBA-albums.txt">VBA Code</a>.
<p>
<img src="images/picture-albums.gif" alt="picture albums sheet">

---- site-analysis

Site Analysis Worksheet
Site Analysis Worksheet

There are many utilities here:
<ul>
<li>Collecting all the CSS code into one file for easy finding of past code examples. There is a button here to open the file, but having it saved to my Desktop, I click it there. There is a list of folders with CSS code that isn't mine, and I don't want included.
<li>There is a button that collects all the CSS file usage, and groups the folders that use that CSS file.
<li>A utility looks for images in a folder that are not used.
<li>Ampersands no longer need to be escaped. But other than in URLs, it is good practice to still escape them. This looks for the unescaped ones.
<li>There are three statistics listings that have buttons in the Dashboard. They are repeated here.
<ul>
<li>Statistics for each Slide Show:
<ul>
<li>Page Count
<li>Thumbnail Size
<li>Thumbnails Total KB
<li>Image Size
<li>Average Image KB
<li>Folder Path
</ul>

<li>Slide Show Option Usage. Three options are turned on by the presence of files in the show's folder. This lists which folders are using them:
<ul>
<li>Captions.txt - has a row for each image.
<li>Heading.txt - puts a heading on each image's page.
<li>Subheadings.txt - These are blocks of code that are inserted before a specified thumbnail.
</ul>

<li>Find Unused Jamaica Images. Goes through each of the sections.

<li>Folder Htm Stats. This lists all the various folder data files that are used to create the htms on the AddNav sheet. You can then edit the files from the sheet with Ctrl-R and the cursor on the row.

</ul>

<li>For the Picture Albums we have three utilities:
<ul>
<li>Listing ai_ files, lists the album information files. Has number of rows (some are optional), the date in the file, and whether the file is a group file.
<li>Info on Albums. The columns:
<ul>
<li>Web-size average
<li>Web-size maximum
<li>Original image average size
<li>Original image maximum size
<li>Picture Count
<li>Maximum length of picture data files (pd_ prefix)
<li>Album name
</ul>
<li>Find Unused Data. Lists all the folders, and their file contents, that were found on the hard drive and are not included in any picture albums.

</ul>

<li>Various buttons let you analyze and correct the results.

</ul>
<p>
The module with the <a href="VBA-site-analysis.txt">VBA Code</a>.
<p>
<img src="images/site-analysis.gif" alt="site analysis sheet">

---- site-map

Site Map Worksheet
Site Map Worksheet

This was the most difficult code to write. You have to recursively go through every folder and organize the results in nested unordered lists.
<p>
It was first written in APL over three weeks. Later it was rewritten on VBA, with optional additional post processing and speedups. The post processing:
<ul>
<li>Combining folders into contiguous blocks
<li>Moving sub-folder calls -- e.g. to PDFs -- to end of folder
<li>Making sure index file is first in folder
<li>Bring sub-subfolders to parent
</ul>
The site map is built in the order it finds the links. Your can add comments to your HTML to disregard code, knowing the links will be found later in a more appropriate position.
<p>
A report is returned with statistics and any error messages or warnings.
<p>
The module with the <a href="VBA-sitemap.txt">VBA Code</a>.
<p>
<img src="images/site-map.gif" alt="site map sheet">

---- slide-shows

Slide Shows Worksheet
Slide Shows Worksheet

The Picture Albums were developed first. But then I needed slide shows to display catalogs and books. The macro creates the individual files, and inserts the thumbnail code into the index page. Arrows in the upper left control movement between pages.
<p>
Initially each image was a page from some catalog, and each page needed no specific caption. Captions for each picture are now optional. Sub-heading sections in the thumbnail list are now implemented in two ways. Either blocks of code to insert, or strings are inserted into title containers.
<p>
This is one of three sheets where a macro on Ctrl-R puts the row number you are on into the Row cell.
<p>
Options available on the sheet in columns:
<ul>
<li>Publication Type Options:
<ul>
<li>blank is nothing (just folder or heading text name)
<li>1- Catalog
<li>2- Folder
<li>3- Pages
<li>4- Photo
<li>5- Slide
</ul>
<li>Cover Flag: 
<ul>
<li>A blank is always labeled Cover
<li>TRUE makes page 1 labeled Cover
</ul>
<li>Back Flag:
<ul>
<li>Blank the last page has a number
<li>TRUE the last page is labeled Back
</ul>
<li>Options are additive:
<ul>
<li>1- Put border around image on htms.
<li>2- Use "-f" files. Default is numbers with prefixes (tp - thumbnail, wp - web-size, p - full fize (optional))
<li>4- Put captions on Index page. Captions on Individual pages must exist first.
<li>8- Use Captions file for image order.
</ul>

</ul>

There is a utility to resize and rename the images. More image processing is on the <a href="image-utilities.htm">Images Utilities Sheet</a>.
<p>
The module with the <a href="VBA-slides.txt">VBA Code</a>.
<p>
<img src="images/slide-shows.gif" alt="slide shows sheet">