VB code in Excel macro

[Don]

Within an application that I am writing, I am trying to allow the user to select a text (.txt) file from the Open File dialog box in Excel. This is working.
The problem is, I want to eliminate the user from having to go through the 3-step Text Import Wizard, which appears right after user selects the (.txt) file from the Open File dialog box. I want to suppress this Wizard from appearing and do that work behind the scenes in the programming.

Code to display the Open File dialog box that works:

With Application
.DefaultFilePath = ("*.*") 'Show all file types in Open File dialog box
.Dialogs(xlDialogOpen).Show 'Show open dialog box to select text file
.DefaultFilePath = strMyFilePath 'Reset original default file path
End With

Code to suppress Wizard and do delimiting by Tab behind the scenes which doesn’t work:

strFileName = ActiveWorkbook.Name
Application.DisplayAlerts = False
Workbooks.OpenText FileName:= _
strFileName, Origin _
:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, Tab:=True
Application.DisplayAlerts = True

The second group of code works but the Wizard opens first and you have to go through the step process in the Wizard before the second group of code is executed.

Can you help?

Here is how I open all of the files in a folder, and
bypass the wizard.
Dim MyFile As String, Mydir As String
Mydir = CurDir()
MyFile = Dir("*.txt")
Do While MyFile <> ""
Workbooks.OpenText Filename:= _
Mydir & "\" & MyFile _
, Origin:=xlWindows, StartRow:=1,
DataType:=xlDelimited, TextQualifier _
:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, Semicolon:= _
False, Comma:=False, Space:=False, Other:=True,
OtherChar:="|", _
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array
(3, 1), Array(4, 1))

MyFile = Dir()
Loop
Hope that helps,

LeRoy

-----Original Message-----
Within an application that I am writing, I am trying to allow the user to select a text (.txt) file from the
Open File dialog box in Excel. This is working. The problem is, I want to eliminate the user from having to go through the 3-step Text Import Wizard, which
appears right after user selects the (.txt) file from the
Open File dialog box. I want to suppress this Wizard from
appearing and do that work behind the scenes in the
programming.
Code to display the Open File dialog box that works:

With Application
.DefaultFilePath = ("*.*") 'Show all file types in Open
File dialog box .Dialogs(xlDialogOpen).Show 'Show open dialog box to select text file .DefaultFilePath = strMyFilePath 'Reset original default file path End With

Code to suppress Wizard and do delimiting by Tab behind the scenes which doesnâ?Tt work:
strFileName = ActiveWorkbook.Name
Application.DisplayAlerts = False
Workbooks.OpenText FileName:= _
strFileName, Origin _
:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _ xlDoubleQuote, Tab:=True
Application.DisplayAlerts = True

The second group of code works but the Wizard opens first and you have to go through the step process in the
Wizard before the second group of code is executed.
Can you help?

.

[Don]

Thanks for the suggestion, I will try it.

----- an*******@discussions.microsoft.com wrote: -----

Here is how I open all of the files in a folder, and
bypass the wizard.
Dim MyFile As String, Mydir As String
Mydir = CurDir()
MyFile = Dir("*.txt")
Do While MyFile <> ""
Workbooks.OpenText Filename:= _
Mydir & "\" & MyFile _
, Origin:=xlWindows, StartRow:=1,
DataType:=xlDelimited, TextQualifier _
:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, Semicolon:= _
False, Comma:=False, Space:=False, Other:=True,
OtherChar:="|", _
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array
(3, 1), Array(4, 1))

MyFile = Dir()
Loop
Hope that helps,

LeRoy

-----Original Message-----
Within an application that I am writing, I am trying to allow the user to select a text (.txt) file from the
Open File dialog box in Excel. This is working. The problem is, I want to eliminate the user from having to go through the 3-step Text Import Wizard, which
appears right after user selects the (.txt) file from the
Open File dialog box. I want to suppress this Wizard from
appearing and do that work behind the scenes in the
programming.

Code to display the Open File dialog box that works:
With Application

.DefaultFilePath =

("*.*") 'Show all file types in Open
File dialog box .Dialogs(xlDialogOpen).Show 'Show open dialog box to select text file .DefaultFilePath = strMyFilePath 'Reset original default file path End With

Code to suppress Wizard and do delimiting by Tab behind the scenes which doesnâ?Tt work: strFileName = ActiveWorkbook.Name Application.DisplayAlerts = False
Workbooks.OpenText FileName:= _
strFileName, Origin _
:=xlWindows, StartRow:=1, DataType:=xlDelimited,

TextQualifier:= _ xlDoubleQuote, Tab:=True
Application.DisplayAlerts = True

The second group of code works but the Wizard opens first and you have to go through the step process in the
Wizard before the second group of code is executed. Can you help?
.