XL5: Cannot Set Consecutive Delimiter for TextWizard in Macro

• 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

- the file FILELIST.TXT exists in the current directory

  -or-

  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.