XL5: Cannot Set Consecutive Delimiter for TextWizard in Macro

ID: Q113910

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

  -or-

- 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: 5.00

Keywords          : kbprg
Version           : 5.00
Platform          : WINDOWS

Last Reviewed: July 2, 1997