Microsoft KB Archive/113910

{|
 * width="100%"|

XL5: Cannot Set Consecutive Delimiter for TextWizard in Macro

 * }

-

The information in this article applies to:


 * Microsoft Excel for Windows, version 5.0

-

SUMMARY
In Microsoft Excel version 5.0, you can use either the Visual Basic Workbooks.OpenText command or the Microsoft Excel version 4.0 OPEN.TEXT macro command to open a text file from a macro. Both commands are equivalent to using the TextWizard to open a text file. However, setting the Consecutive Delimiter argument for either of these commands will have no effect in TextWizard.

STATUS
Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. This problem was corrected in Microsoft Excel for Windows version 5.0c.

MORE INFORMATION
When you run a macro using either the Workbook.OpenText or OPEN.TEXT command, the text file will be opened using the Consecutive Delimiter setting that was last set in TextWizard manually. The Consecutive Delimiter argument in the command in a macro is ignored.

WORKAROUND
The following Visual Basic subroutine will, when run, turn on (or off) the "Treat consecutive delimiters as one" check box in the Text Import Wizard:

Sub ToggleConsecDelims 'Toggles "Treat consecutive delimiters" setting. If off, this line 'turns it on. If already on, this line turns it off. Application.SendKeys "%fofilelist.txt{enter}%d{enter}%r{esc}"

End Sub To use this subroutine, make sure that either

 the file FILELIST.TXT exists in the current directory  you replace the characters "filelist.txt" in the above subroutine with the name of a text file located in the current directory. For example, you could use:     Application.SendKeys "%fosample.txt{enter}%d{enter}%r{esc}" if SAMPLE.TXT is a text file located in the current directory. 

You can then run code containing the Workbooks.OpenText or OPEN.TEXT commands.

Additional query words:

Keywords : kbprg

Version : WINDOWS:5.0

Platform : WINDOWS

Issue type :

Technology :