XL: Specifying Smart Parse Column Delimiter from a Macro

• 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.

    run properly).

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.

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

    finished.

REFERENCES

"Function Reference," version 4.0, pages 314-315, 385