VB code in Excel macro

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
Click to expand...
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
Click to expand...
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 =
Click to expand...
("*.*") 'Show all file types in Open
File dialog box
.Dialogs(xlDialogOpen).Show 'Show
Click to expand...
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
Click to expand...
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
Click to expand...
first and you have to go through the step process in the
Wizard before the second group of code is executed.
 
Thanks for the suggestion, I will try it.

----- (e-mail address removed) 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
Click to expand...
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
Click to expand...
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.
.DefaultFilePath =
Click to expand...
("*.*") 'Show all file types in Open
File dialog box
.Dialogs(xlDialogOpen).Show 'Show
Click to expand...
open dialog box to select text file
.DefaultFilePath = strMyFilePath 'Reset original default file path
End With
Application.DisplayAlerts = False
Workbooks.OpenText FileName:= _
strFileName, Origin _
:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, Tab:=True
Application.DisplayAlerts = True
Click to expand...
first and you have to go through the step process in the
Wizard before the second group of code is executed.
 
Back
Top