|
| xlsgen > overview > Excel search tool |
|
In this step-by-step demo, we'll use the Excel search tool part of the xlsgen distribution package.
The Excel search tool finds cells containing keywords in Excel workbooks. Search results are presented in a way that enables navigating any matching workbook by just double-clicking. The appropriate worksheet of the Excel workbook is then open and positioned on the right cell.
Alternatively, results can be exported to a flat file for later reuse, or for further analysis in a separate tool. For instance Excel or Access can import those flat files.
The search mechanism applies to all Excel workbooks starting from Excel 95. In other words, keywords will be searched for in the following Excel workbook versions : 95, 97, 2000, XP, 2003 and 2007.
The search tool is based on search features exposed by xlsgen. While users will probably essentially use this tool, developers can also take advantage of the programmatic way to perform search among Excel workbooks.
Note that xlsgen also has search/replace capabilities available programmatically.
| Note : if you would like to purchase this product as a stand-alone product ($40 USD), please send an email at xlsgensupport at arstdesign.com. This will help plan for a product shipping timeframe. |
Find the Excel search tool in the xlsgen program group from the Start menu, or just take the Windows explorer, open the xlsgen application directory (c:\program files\xlsgen by default), and double-click xlssearch.exe.
The following application appears :
Let's find the hello word in the entire C:\ drive. Click on Select folder, then select C:\ and click OK. Now fill the keyword editbox with the hello keyword. As you type, the Search button on the right should automatically switch its state from grayed to enabled. When done, the user interface should look like :
Now click the Search button and see the results appear. Of course, results depend on your own Excel workbooks, so you are very likely to experience different results.
At the bottom, the Status text tells how many matches have been found. It should like the following :
Results are pretty much self-descriptive. Results are made of all matches of the keyword using the search options in all selected files (either independently, or from a folder). A single workbook can have more than one match, obviously. In fact, you can find more than one match in each worksheet of each workbook.
Unless explicitely told not to, you'll find all matches for each worksheet. If the No duplicates option is checked, you'll find at most one entry per worksheet.
Columns are as follow :
File : the short filename of a matching Excel workbook
Folder : the path where the Excel file exists
Date : the last modified date of the file
Worksheet : the name of the worksheet where the match is found
Cell : the reference of the matching worksheet cell
Because search is performed on worksheet names themselves as well as the content in cells, the results may or may not have a cell reference. Any time the keyword matches a worksheet name, there is no cell reference.
Results appear in red whenever they couldn't be opened for performing a search. In most cases, this happens when a file is opened by another process in such a way that the access is made exclusive to that process and, as a consequence, xlssearch cannot open this workbook.
The most obvious way to open a matching Excel workbook is by double-clicking a result. The Excel workbook opens in Excel and scrolled to the proper worksheet and cell. This navigation mechanism is at the heart of what makes such a strong difference with the Windows explorer where Excel files without any further details.
Other than double-clicking, you can alternatively right-click and select one of the options in the context menu :
The menu options are as follow :
open folder : opens the folder where you'll find the Excel workbook
open in Excel : opens the Excel workbook and scrolls it to the appropriate worksheet and cell. This option is the same than double-clicking a result.
open in New Excel : mostly similar to the option above, but opens the Excel workbook in a separate Excel instance.
Navigating the results assumes Excel is installed on the machine. Please note however that xlssearch will perform the search even if Excel is not installed. In fact, Excel is not a requirement for the search, it's only a requirement for navigating the results.
When there are results, the Export results button next to the Exit button is automatically enabled. If you click it, and enter a filename, the results are exported into a tabular format, that is specifically designed to be human readable and can be imported in a separate tool. When the results are exported, notepad automatically starts and shows the content of that file, as in :
Default options should satisfy most needs, but several of these can be customized :
case sensitive : when checked, this option will differentiate "hello" from "Hello"
uses wildcards : when checked, this options will regard * characters as replacement for arbitrary character sequences, just like in Windows explorer. Example : if you type "he*o", then both "hello" and "hebo" are matches.
no duplicates : when checked, this option ensures that at most one result is return per worksheet per workbook.
This scenario is often needed, and it is available in xlssearch. Just drop-down the file box, and select All local hard drives. This is a special sentence that will tells xlssearch to perform the search across all local hard drives. Please note neither floppy disk drives nor remote drives, nor CD-ROM drives are taken into account.
Bonus : the drop-down keeps track of your history search in the current session. You can use it to avoid selecting files or folders.
This scenario is useful as well. You can either type the file right in the file box, or click the Select file button and use the open file dialog to browse your drives.
xlsgen documentation. © ARsT Design all rights reserved.