Microsoft KB Archive/125809

{|
 * width="100%"|

XL: Specifying Smart Parse Column Delimiter from a Macro

 * }

-

The information in this article applies to:


 * Microsoft Excel for Windows, version 4.0

-

SUMMARY
To specify a particular character to be used as the column delimiter when you run the Smart Parse command from a user-defined macro, you can use the SEND.KEYS command to specify the appropriate settings in the Smart Parse dialog box.

MORE INFORMATION
By default, the Smart Parse command (on the Data menu) uses a space as the column-delimiter. If you want to run Smart Parse from a user-defined macro and specify another character (a semicolon, for example) as the column delimiter, use a macro similar to the following.

Sample Macro
Microsoft provides macro examples for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. This macro is provided as is and Microsoft in no way guaranties that the following code can be used in all situations and will not support modifications of the code to suit specific customer requirements.

This macro opens FLATFILE.XLA and performs a Smart Parse on the range A1:A100. The data is parsed using semicolons for the column delimiters.

A1: My_Smart_Parse A2: =ERROR(FALSE)+OPEN("C:\EXCEL\LIBRARY\FLATFILE.XLA")+ERROR(TRUE) A3: =RUN("FLATFILE.XLA!Auto_Open") A4: =SELECT(!A1:A100) A5: =SEND.KEYS("o{TAB}{;}~") A6: =RUN("FLATFILE.XLA!mcp05.SmartParse") A7: =ALERT("Finished.") A8: =RETURN To use a different character as the column delimiter, change the semicolon inside the braces {} in line A5 to the character of your choice (for example, you could use a comma, space, pound sign, and so on).

If you want to select the Remove Extra Blank Spaces check box, modify line A5 to read as follows:

  A5: =SEND.KEYS("o{TAB}{;}%{r}~")

Description of macro code above
A1: Macro name

A2: Turns off error-checking prior to opening FLATFILE.XLA (thus avoiding the "Revert to saved 'FLATFILE.XLA'?" message that may appear), and then turns error-checking back on. If Microsoft Excel is not installed in C:\EXCEL, you need to modify the path in the OPEN statement accordingly.

A3: Runs the FLATFILE.XLA Auto_Open routine (required for Smart Parse to run properly).

A4: Selects the data to parse.

A5: Places keystrokes in the buffer which, upon execution of the next macro statement, will cause the 'Other' Column Delimiter field in the Smart Parse dialog box to be set to a semicolon. The last character inside the quotation marks command is a tilde.

A6: Performs a Smart Parse on the selected data.

A7: Displays a message in an alert box, indicating that the macro has finished.

A8: Ends the macro.