Hi Natalie
The site seem to have some problems. Here is copy-paste of its text, it may
be hard to read without the formatting:
Exporting To Text Files
You can also use VBA to write a procedure to export the active worksheet, or
just the selected cells, to a text file. The following VBA procedure will
do this. The parameters to this function are as follows:
FName The file name to write the data to
Sep The character with which cells will be delimited
SelectionOnly If TRUE, export only the selected cells. If FALSE, export the
entire worksheet.
Public Sub ExportToTextFile(FName As String, _
Sep As String,
SelectionOnly 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
Open FName For Output Access Write As #FNum
For RowNdx = StartRow To EndRow
WholeLine = ""
For ColNdx = StartCol To EndCol
If Cells(RowNdx, ColNdx).Value = "" Then
CellValue = Chr(34) & Chr(34)
Else
CellValue = _
Application.WorksheetFunction.Text _
(Cells(RowNdx, ColNdx).Value, _
Cells(RowNdx, ColNdx).NumberFormat)
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
If there is a blank cell, an empty string is put in the text file. For
example, if you are exporting the row
with a delimiter of |, this will be in the text file as
one|two|""|three|four|""|""|five|six
If you want consecutive delimiters, without the empty string, change the
code. Change
If Cells(RowNdx, ColNdx).Value = "" Then
CellValue = Chr(34) & Chr(34)
Else
to
If Cells(RowNdx, ColNdx).Value = "" Then
CellValue = ""
Else
With this change, the text file will contain
one|two||three|four|||five|six
You can call this macro from another VBA procedure as follows:
ExportToTextFile "c:\temp\test.txt", ";" , FALSE
Since this code has parameters, it will not appear in the standard "Macros"
dialog list (ALT+F8). The following procedure will prompt you for the
filename and delimiter character, and then run the ExportToTextFile
procedure.
Public Sub DoTheExport()
Dim FName As Variant
Dim Sep As String
FName = Application.GetSaveAsFileName()
If FName = False Then
MsgBox "You didn't select a file"
Exit Sub
End If
Sep = InputBox("Enter a single delimiter character (e.g., comma or
semi-colon)", _
"Export To Text File")
ExportToTextFile CStr(FName), Sep, _
MsgBox("Do You Want To Export The Entire Worksheet?", _
vbYesNo, "Export To Text File") = vbNo
End Sub
