Use VBA code to open a text file with the import wizard

  • Thread starter Thread starter George Wilson
  • Start date Start date
G

George Wilson

I am wanting to use VBA code to open a text file with the
import wizard. Does anyone know how I would invoke the
import wizard to open a text file in VBA? Any assistance
on this matter would be greatly appreaciated.
 
George,

The following code (borrowed from this forum) will open one or more
text files and convert them to Excel. The text wizard portion is set for
Tab delimited (adjust as needed).

=========================
Sub OpenMyFile()
Dim GetFiles As Variant
Dim iFiles As Long
Dim nFiles As Long
Dim pth As String
Dim wbnm As String

'Open *.txt file and convert to *.xls
' for tab and , delimited text

Application.DisplayAlerts = False
Application.ScreenUpdating = False

pth = ActiveWorkbook.path
ChDir pth
GetFiles = Application.GetOpenFilename _
(FileFilter:="Text Files (*.txt),*.txt", _
Title:="Select Files To Open", MultiSelect:=True)
If TypeName(GetFiles) = "Boolean" Then
''' GetFiles is False if GetOpenFileName is Canceled
MsgBox "No Files Selected", vbOKOnly, "Nothing Selected"
End
Else
'' GetFiles is Array of Strings (File Names)
'' File names include Path
nFiles = UBound(GetFiles)
For iFiles = 1 To nFiles
' List Files in Immediate Window
Workbooks.Open FileName:=GetFiles(iFiles)
Columns("A:A").Select

' text wizard for tab delimited. modify as needed
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited,
_
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo
_
:=Array(1, 1)

' saveas *.xls file in same folder
wbnm = ActiveWorkbook.Name
wbnm = Left(wbnm, Len(wbnm) - 4)
ActiveWorkbook.SaveAs FileName:= _
pth & "\" & wbnm & ".xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False _
, CreateBackup:=False

Range("A1").Select
Next
End If

Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
 
Thanks
-----Original Message-----
George,

The following code (borrowed from this forum) will open one or more
text files and convert them to Excel. The text wizard portion is set for
Tab delimited (adjust as needed).

=========================
Sub OpenMyFile()
Dim GetFiles As Variant
Dim iFiles As Long
Dim nFiles As Long
Dim pth As String
Dim wbnm As String

'Open *.txt file and convert to *.xls
' for tab and , delimited text

Application.DisplayAlerts = False
Application.ScreenUpdating = False

pth = ActiveWorkbook.path
ChDir pth
GetFiles = Application.GetOpenFilename _
(FileFilter:="Text Files (*.txt),*.txt", _
Title:="Select Files To Open", MultiSelect:=True)
If TypeName(GetFiles) = "Boolean" Then
''' GetFiles is False if GetOpenFileName is Canceled
MsgBox "No Files Selected", vbOKOnly, "Nothing Selected"
End
Else
'' GetFiles is Array of Strings (File Names)
'' File names include Path
nFiles = UBound(GetFiles)
For iFiles = 1 To nFiles
' List Files in Immediate Window
Workbooks.Open FileName:=GetFiles(iFiles)
Columns("A:A").Select

' text wizard for tab delimited. modify as needed
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited,
ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo
_
:=Array(1, 1)

' saveas *.xls file in same folder
wbnm = ActiveWorkbook.Name
wbnm = Left(wbnm, Len(wbnm) - 4)
ActiveWorkbook.SaveAs FileName:= _
pth & "\" & wbnm & ".xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False _
, CreateBackup:=False

Range("A1").Select
Next
End If

Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

--
sb



.
Click to expand...
 
Back
Top