Exporting And Import Text With Excel
This page describes how to import text files to Excel and how to export Excel data out to text files.
This pages describes VBA procedures that you can use to export
data from an Excel worksheet to a text file and to import data from a text file
directly into an existing range of a worksheet.
These procedures are for delimited file, where each field is of (optionally) different
lengths and fields are separated by a separator character such as a comma. See
Exporting To A Fixed Width File for code to export
Excel data to a fixed width text file. See Importing A Fixed Width File
for code to import a fixed width text file into Excel.
This procedure allows you to export data from a worksheet range
to a text file. You may specify the character (e.g, a space, tab, pipe, comma,
etc) that separates the exported elements. Each row of cells in the worksheet is
written to one line within the text file, and each item in that line is
separated by the specified delimiter character. Any single character may be used
as the delimiter.
The ExportToTextFile procedure follows. The parameters to
ExportToTextFile are described in the following table:
Parameter |
Description |
FName |
The name of the file to which the data will be written.
The file will be created if it does not exist. See AppendData below.
|
Sep |
The character that is to separate the elements on each
row of the exported file. Typically, this is vbTab, a space, a comma,
semicolor, or pipe ( | ). Any character may be used.
|
SelectionOnly |
If True, only the currently selected cells are
exported. If False, the entire used range of the worksheet is exported.
|
AppendData |
If True and FName exists, data is written to the end of
the text file, preserving the existing contents. If False, the existing
contents of FName are destroyed and only the newly exported data will
appear in the output file.
|
Public Sub ExportToTextFile(FName As String, _
Sep As String, SelectionOnly As Boolean, _
AppendData As Boolean)
Dim WholeLine As String
Dim FNum As Integer
Dim RowNdx As Long
Dim ColNdx As Integer
Dim StartRow As Long
Dim EndRow As Long
Dim StartCol As Integer
Dim EndCol As Integer
Dim CellValue As String
Application.ScreenUpdating = False
On Error GoTo EndMacro:
FNum = FreeFile
If SelectionOnly = True Then
With Selection
StartRow = .Cells(1).Row
StartCol = .Cells(1).Column
EndRow = .Cells(.Cells.Count).Row
EndCol = .Cells(.Cells.Count).Column
End With
Else
With ActiveSheet.UsedRange
StartRow = .Cells(1).Row
StartCol = .Cells(1).Column
EndRow = .Cells(.Cells.Count).Row
EndCol = .Cells(.Cells.Count).Column
End With
End If
If AppendData = True Then
Open FName For Append Access Write As #FNum
Else
Open FName For Output Access Write As #FNum
End If
For RowNdx = StartRow To EndRow
WholeLine = ""
For ColNdx = StartCol To EndCol
If Cells(RowNdx, ColNdx).Value = "" Then
CellValue = Chr(34) & Chr(34)
Else
CellValue = Cells(RowNdx, ColNdx).Value
End If
WholeLine = WholeLine & CellValue & Sep
Next ColNdx
WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep))
Print #FNum, WholeLine
Next RowNdx
EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #FNum
End Sub
Because the ExportToTextFile procedure
accepts input parameters, you must call it from other VBA code, such as the
following:
Sub DoTheExport()
ExportToTextFile FName:="C:\Test.txt", Sep:=";", _
SelectionOnly:=False, AppendData:=True
End Sub
In the example DoTheExport procedure above, the file name and the separator character are hard coded
in to the code. If you want to prompt the user for the file name and the separator character, use code like the following:
Sub DoTheExport()
Dim FileName As Variant
Dim Sep As String
FileName = Application.GetSaveAsFilename(InitialFileName:=vbNullString, FileFilter:="Text Files (*.txt),*.txt")
If FileName = False Then
Exit Sub
End If
Sep = Application.InputBox("Enter a separator character.", Type:=2)
If Sep = vbNullString Then
Exit Sub
End If
ExportToTextFile FName:=CStr(FileName), Sep:=CStr(Sep), _
SelectionOnly:=False, AppendData:=True
End Sub
This code will prompt the user for a text file name in which to save the file and for the separator character. If the user cancels
either of these dialogs, the procedure is terminated and no export operation is carried out.
This procedure allows you to import data from a delimited text
file. Each line in the text file is written to one row in the worksheet. Items
in the text file are separated into separate columns on the worksheet row based
on the character you specify.
If your text file contains greater the 64K records, or you need to split the imported text across multiple worksheets, see
Importing Big Text Files Into Excel.
The ImportToTextFIle procedure is shown below.
FName is the name of the file to import and
Sep is the character that separates column data.
Public Sub ImportTextFile(FName As String, Sep As String)
Dim RowNdx As Long
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer
Application.ScreenUpdating = False
'On Error GoTo EndMacro:
SaveColNdx = ActiveCell.Column
RowNdx = ActiveCell.row
Open FName For Input Access Read As #1
While Not EOF(1)
Line Input #1, WholeLine
If Right(WholeLine, 1) <> Sep Then
WholeLine = WholeLine & Sep
End If
ColNdx = SaveColNdx
Pos = 1
NextPos = InStr(Pos, WholeLine, Sep)
While NextPos >= 1
TempVal = Mid(WholeLine, Pos, NextPos - Pos)
Cells(RowNdx, ColNdx).Value = TempVal
Pos = NextPos + 1
ColNdx = ColNdx + 1
NextPos = InStr(Pos, WholeLine, Sep)
Wend
RowNdx = RowNdx + 1
Wend
EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1
End Sub
Since ImportTextFile takes input parameters, it should be
called from other VBA code:
Sub DoTheImport()
ImportTextFile FName:="C:\Test.txt", Sep:="|"
End Sub
In this code, both the file name and the separator are hard coded into the code. If you want to prompt the user for a file name and
separator character, use code like the following:
Sub DoTheImport()
Dim FileName As Variant
Dim Sep As String
FileName = Application.GetOpenFilename(FileFilter:="Text File (*.txt),*.txt")
If FileName = False Then
Exit Sub
End If
Sep = Application.InputBox("Enter a separator character.", Type:=2)
If Sep = vbNullString Then
Exit Sub
End If
Debug.Print "FileName: " & FileName, "Separator: " & Sep
ImportTextFile FName:=CStr(FileName), Sep:=CStr(Sep)
End Sub
In this code, the user is prompted for the import file name and the separator character. If either of these prompts are cancelled,
the operation is terminated.
This page last modified: 17-November-2007.