PATools XL Toolbox

Detail on all the component parts

PATools XL Toolbox

With just a couple of clicks ...
... you open your frequently used files and folders
... you can email just part of your open workbook - even just a range of cells
... you can run a simple mail merge (or a complicated one with lots of clever options)
... you can even make lots of folders or rename lots of files using Excel formulas
... and so much more besides ...

Take a look below at more detail on the components.

IMPORTANT NOTE on Microsoft Outlook security
When using this PATools product users of Outlook 2000 or later may encounter issues with Outlook security. If you do not wish to see these messages we suggest you try the MAPILab product at http://www.mapilab.com/outlook/security
This product is simple to install, and is free for private or commercial use. Although PATools does not recommend any particular product, we have successfully used this one ourselves.

PATools Merge Suite

Simple and easy to use
No need to know Microsoft Word or other programs
Merge single rows or multiple rows in each merge
Various output options including email and pdf files*
Use formulas, lookups, 'if' statements etc in your data and/or your merge template
Numerous advanced options
On-line help file and troubleshooting
Demo workbook with examples
FREE to try for as long as you wish with full functionality

You need just two worksheets to run a merge:

The source sheet will contain the rows of data to be merged. In row 1 you put the merge field names (eg firstname in cell A1), and below you have your data to be merged (eg John in cell A2)

The output sheet is your form or letter template, with the merge field names marked by << >> anywhere in any cells eg cell A6 might contain "Dear <>,".

The sheet can be named as you wish, and can contain any number of merge fields either in a formula or within text.
(For advanced merges there are special types of headers such as h.id and b.linedetail, or merge fields <> - see help file and demo workbook for examples and explanations.)

In addition you can use a Word document (open or saved) as your template and merge into that. The merge fields are marked the same as for Excel and you have all the same options.

The results can be output to:
1. print preview for testing
2. any of your printers (including different printers per merge)
3. individual emails, either in the email body or as attachments (text files, Excel workbooks, html and mht files, or pdf files*)

If you use Microsoft Outlook there are various options, and you can send the emails straight to your outbox
file(s) - individual Excel workbooks, pdf* or html/mht files, individual Word documents, a text file with page breaks, or individual text files
a new workbook with each merge result on a separate worksheet.

There are also a number of advanced options such as different numbers of printouts per merge, print to different printers, insert different images, selective source row merging, add a counter to each merge, and merge to different destination templates in one run, plus a whole range of options for emails if you use Outlook.

It is simple and intuitive to use, with an on-line help file and videos to watch.

Using PATools Merge Suite

PATools Easy Word Merge

Many organisations use templates such as legal agreements, quotations, purchase orders, invoices, lease agreements, school reports, exam results, booking confirmations, etc etc.

Typically they are created as per the examples on the right here, and people spend considerable time going through and manually replacing the highlighted sections with the data for the particular document.

PATools Easy Word Merge is a great alternative which will save you considerable time and avoid potentially costly mistakes:

1. You can use your existing document (you may wish to add some identifier such as square brackets [ ] around the sections to be replaced if not already there).
2. In an Excel worksheet you enter the field names in column A and the data you want in column B.
3. Open our software from the Excel menu and hit ' Run '.

Your Word document will be prepared ready for you to edit, send, save etc. Alternatively, it can be attached to an open email for you, in either Word or pdf format, ready for you to complete the email and send.

If you save your Excel workbook then it can be re-run at any time, if perhaps your Word template gets updated later on.

One of the great benefits is that you can also have formulae / data validation etc set up in column B, or save other data in columns C onwards that you can copy and paste into column B for any particular merge. Similarly, you can add or edit the merge fields so easily by simply typing into your Word document and then copying into a cell in column A - so quick and easy.

Also, because you are in control, you can mark your fields however you wish, such as with [ ] or with < > or with { } or with <<< >>>, or even a mix. All our software does is a "simple" search and replace (including in headers, footers, text boxes etc), so you don't even have to mark the fields at all - though be careful if you do not, as you may have other pieces of text being replaced.

Finally, our software will also check your Word template against the Excel sheet to see if any fields are missing in one or the other.

It is simple and intuitive to use, with an on-line help file and a video to watch.

PATools File Manager and File Lister

The PATools File Manager is an add-in for Microsoft Excel software to help you work with files and folders 'en masse' from within Excel.

Although deceptively simple it is also VERY POWERFUL since you have all the power of Excel spreadsheets and formulas to help with mass creating, copying, moving, renaming, deleting, graphing and analysis:

1. Use the power of Excel formulas to create, delete, copy, move or rename a few or many files and/or folders very quickly
2. Quickly and easily build large numbers of folder names using standard Excel formulas, and then create those folders in one sweep
3. Open flat files and do a customized search and replace in each one, and re-write or write to new files - using Excel formulas to generate both the data and the file names
4. List folder contents or grab a file name quickly, plus some handy built-in functions help you manipulate file/folder names easily
5. Run full listings (including zip file contents) and get started analysing them easily with the built in PATools File Lister
6. Find duplicate files across your disk, filter them out and then delete them with just 3 clicks
7. View the start of a large flat file

To use the software you simply highlight your selected cells containing file and/or folder names, select your options and click 'Run'.

It is simple and intuitive to use, with an on-line help file and a demo workbook.

PATools Range Manager

Compare or align 2 adjacent tables of data 
Send your selected range of cells straight to an email body
Instantly create a csv file of your selected range of cells - no need to SaveAs
Quickly clear all duplicates from your selected range
Quickly clear all error cells from your highlighted range
Create or remove hyperlinks from your range
Remove double (and multiple) spaces from text in your range
Fill in missing items in your data eg copy into empty cells from the nearest cell value above
Add rounding to all your formulae in your range ie add the =ROUND formula to all formula cells
Adjust values, flip the sign (ie positive to negative), round all numbers in your range
Flip words around in text eg LastName FirstName > FirstName LastName
Create a table of data from 1 column of data, and vice versa
Reverse a range of cells
Pick a cell from a range at random
Find missing items from lists of data
Line up and compare two sets of data next to each other on one worksheet
Add a clickable index page to your workbook
and lots more besides ...

In addition it takes just one click to:

see instantly the sum, average, sum of positives and negatives separately (useful for accountants to see Debits & Credits), maximum and minimum values of your selected area even if it contains error cells.

see instantly an analysis of your selected cells in terms of the number of cells, and the number that contain formulae, values, positive values, negative values, zeros, errors, text, blank and non-blank cells.

The reason this is so useful is that (unlike the bar at the bottom of the normal screen) it shows you all the figures in one go, and it copes with error cells (see how the built-in Excel bar changes as soon as you shade over an error cell?).

PATools Advanced Replace

Search and replace throughout multiple open or closed workbooks.
Easily work with special characters such as tab, asterisk, question mark, line feed (input as ALT+ENTER in a cell), etc.
Get a full history of formulae before and after, by using the Tracking facility.
Get a summary of how many were found and/or replaced in each worksheet. 

Each time you run a find or replace a summary text file is created in your temporary file folder (eg c:\windows\temp). This file logs a summary count of the results per worksheet, and logs any errors encountered.

In addition the Tracking facility will create a new spreadsheet in which it will record the workbook, worksheet, cell, and cell formulae for find/replace. This workbook will be given a unique name and saved in your temporary file folder (eg c:\windows\temp).

These files are available for copying to a folder of your choice. In addition you can view the last one created at any time from the software interface.

PATools Data Toolkit

Find almost any selection of cells on a sheet (by their appearance, contents or location), and carry out some selected function on them, such as change the appearance, the contents or the location

Work easily with duplicates, links, errors, colours, formats, hyperlinks etc

Convert numbers or formulae easily

Tidy up text and convert numbers which have been input as text (aren't they annoying?!!!)

PATools Data Analyser

It is most likely to be used for two main purposes:

1. to assist in checking a workbook for integrity and for errors in logic and content, and

2. to help you understand and find your way around a workbook created by somebody else

It is essentially a tool that makes a copy of your worksheet and then changes the appearance of each cell based on its contents. This allows you to see at a glance whether cells contain a formula, text, number, or an error. So it is easy to understand how a worksheet is constructed, irrespective of the actual values displayed.

Imagine how this might help with large and complicated worksheets!

FACT: Users believe that there is only an 18% chance that their completed spreadsheets contain errors, but 86% actually did contain errors!

[Panko R & Halverson R 1996 'Spreadsheets on Trial: A Survey of Research on Spreadsheet Risks', Proceedings of the Hawaii International Conference on System Sciences]

How much might errors in your spreadsheets cost you?

Can you afford not to check your critical work?

PATools Data Copier

Copy data quickly and easily within Microsoft Excel software between different workbooks and worksheets, including closed workbooks. This is particularly useful if you have large volumes of such data to copy.

You can also amalgamate data from multiple sheets (and workbooks) into one sheet.

You can copy values only, ranges of data, or whole sheets.

Simple but powerful!

PATools xlEmailer

Email in Microsoft Outlook any part of your workbook such as

a range of cells
multiple ranges
a single worksheet
selected worksheets
a whole workbook

There is a quick one-click facility to email a single worksheet, or you can use a separate screen to select options (eg remove formulas, remove font colours etc) and create the email.

You can save email templates, together with prompts, for repetitive or regular emails eg you may wish to create a monthly email to send out sales results, where this program prompts you to enter the month each time you open the email template ie the email subject might read 'Monthly sales results for {{month}}' in the template which will convert to 'Monthly sales results for July' in the email.

© International Copyright 2018 PATools - All Rights Reserved

PATools, Alton Lane, Four Marks, Alton, Hampshire GU34 5AL UK
PATools is a trading style of Smada Ltd, Registered in England and Wales No 9122922.
Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.