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.
REFERENCES
"Function Reference," version 4.0, pages 314-315, 385
Additional query words:
4.00a text textfile
Keywords :
Version : WINDOWS:4.0
Platform : WINDOWS
Issue type :
Technology :