Actions

ExcelPipe Search and Replace: Online Help Actions	Submit feedback on this topic
Menus: File Options Help Running ExcelPipe Screens: Actions Files to process Options Status dialog

Home
Up
Special characters

This tab determines the text to be searched for, or search/replaced.

Load replacement list from file

When checked, search/replace pairs are loaded from a file dynamically at run time - from Excel (.XLS files), Comma Separated Value (.CSV files) or Tab-delimited (.TAB files) formats. The current search type, search location and other options are used for every search/replace pair added. A list can also be imported using an option in the Options menu.

For the complete file format details, please click here. You can find sample import files in the Samples folder (in .tab, .csv and .xls format).

ExcelPipe can handle an unlimited number of replacements - but Microsoft Excel is limited to 65534 rows of data. You can work around this limit by loading multiple files from the command line.

Note: Loading large lists from Excel is very slow - export your list to .CSV or .TAB and then import that instead.

Using these values

Uses the search/replace pairs specified in the grid. Click the [Add] button to add a new row to the end of the grid. To delete rows, first select them and then click [Delete]. To edit a row, click it, and then press [F2], or click the cell a second time. You can [Tab] and [Shift-Tab] through the grid. You can change the search settings for multiple rows simultaneously by first selecting them and then changing the search settings below. You can move rows using drag and drop. Copy rows by selecting them and then clicking [Copy]. Clear All rows by clicking [Clear All].

Find what

Type the information you want to search for or paste it from the Clipboard. The Find text is limited by Microsoft Excel to be a maximum length of 255 characters. You can right click this field for a popup menu with Cut, Copy, Paste and Clear commands.

You can search for more than one line of text at a time. If you use the wildcards *, ? or ~ in your Find What string, see the special characters section.

Searching for multiple lines

Although the edit field can hold multiple lines of text, Microsoft Excel does NOT allow you to search for multiple lines at once (and ExcelPipe cannot get around this limitation). The best way to search for multiple lines is to break them up into several line-by-line or phrase-by-phrase searches.

Replace with

Type the text that you want to use as replacement text or paste if from the Clipboard. Leave the Replace With box empty to delete the Find What box from your document. You can right click this field for a popup menu with Cut, Copy, Paste and Clear commands.

Search Type

Normal search

Matches the text exactly as written.

EasyPattern wildcards

Important Note: This search type does NOT apply to the Cells search location. For wildcard searches in Cells, use Microsoft Excel's inbuilt wildcards, with the Search Type set to 'Normal search'.

An EasyPattern allows you to search for a class of text strings rather than specifying every single possibility. For example, to search for a dollar sign followed by a currency amount:

$[ capture(1+ digits, period, 2 digits) ]

Replace With:

USD $1

Click here for the EasyPattern basics and reference.

EasyPatterns are easier to learn and use than perl regex patterns, but to use them in other packages you need to use EasyPattern Helper to convert them to perl regex.

Perl regex wildcards

A perl regex pattern allows you to search for a class of text strings rather than specifying every single possibility. For example, to search for a dollar sign followed by a currency amount:

\$(\d+\.\d{2})

Replace With:

USD $1

Click here for the Perl regex reference.

Perl regex patterns are harder to learn and use than EasyPatterns, but they are support by a huge range of applications.

Search location

Cells - look in each cell
Hyperlink addresses (*) - look inside hyperlink addresses. To change the displayed text, use the 'Cells' option above as well
Shapes/Shapes and Text Boxes (*) - inside the text of shapes and grouped shapes and text boxes
Headers (*) - search inside the left, centre and right header of each sheet
Footers (*) - search inside the left, centre and right footer of each sheet
Built in properties (*) - inside Subject, Title, Author etc fields
Custom properties (*) - inside custom properties defined by the user
Links (OLE and DDE) (*) - inside links to external documents and databases
Chart Titles (*) - inside the titles of charts. Note - axis labels come from the work sheet itself, so use the Cells option to replace them
Defined Names (*) - search and replace the name of predefined names (the names of ranges, constants etc)
Code Modules (*) - search and replace code modules. See Security below.

Items with (*) support the use of EasyPatterns and Perl regex patterns.

Hyperlink addresses

If you want to change the displayed text (not the hyperlink itself), please use a Cells Search instead of, or in addition to this option. This option can be used to search and replace hyperlinks to update server names, UNC paths etc. We recommend that the Match Case option be unchecked, so that both upper case and lower case data can be found. You can use any search type, including EasyPatterns and Perl regex patterns.

If the hyperlink address and display text are identical they will be updated simultaneously. ExcelPipe treats the two representations of space (' ' and '%20') as equivalent, however other escaped characters (in %XX form) need to be handle manually.

Note also that sometimes the display text of a hyperlink is completely different to the actual link. To be sure that what you are replacing is correct, right click on the hyperlink in Excel and choose 'Edit Hyperlink'. You may have to perform one search for the display text and another search for the hyperlink text.

Match case

Distinguishes between UPPERCASE and lowercase letters. When Match Case is selected, ExcelPipe finds only those instances in which the capitalization matches the text you typed in the Find What box.

Match entire cell contents

Searches for matches where the entire cell has this value, rather than part of the cell.

Highlight replacement

Where possible, the replaced text is highlighted to make it easier to find.

Match half/full width forms

When checked, Unicode (double-byte) characters match only Unicode (double-byte) characters. When unchecked, Unicode (double-byte) characters match their ANSI (single-byte) equivalents.

You can use this argument only if you've selected or installed double-byte language support in Microsoft Excel, such as for Japanese.

[Start replacing (F9)]

When you click this button or press [F9], ExcelPipe will begin making replacements.

[Start searching (F5)]

When you click this button or press [F5], ExcelPipe will begin searching for the search text. No replacements will be made.

Security

If you get the error message Programmatic Access to Visual Basic Project is not trusted, you need to change your Microsoft Excel security settings. To turn on access, the user must follow these steps.

Office 2003 and Office XP

1.	Open the Office 2003 or Office XP application in question. On the Tools menu, click Macro, and then click Security to open the Macro Security dialog box.
2.	On the Trusted Sources tab, click to select the Trust access to Visual Basic Project check box to turn on access.
3.	Click OK to apply the setting. Restart the Office 2003 or Office XP application in question.

Office 2007

1.	Open the 2007 Microsoft Office system application in question. Click the Microsoft Office button, and then click `Application` Options.
2.	Click the Trust Center tab, and then click Trust Center Settings.
3.	Click the Macro Settings tab, click to select the Trust access to the VBA project object model check box, and then click OK.
4.	Click OK.