Microsoft Office Tutorials and References
In Depth Information
Using Text Files to Store External Data
Set rng = Range(“A1”).CurrentRegion
Open “C:\YourFilePath\YourFileName.txt” For Output As #1
For iRow = 1 To rng.Rows.Count
For iCol = 1 To rng.Columns.Count
If Not Cells(iRow, iCol).Comment Is Nothing Then
strText = strText & Cells(iRow, iCol).Text & _
“(“ & Cells(iRow, iCol).Comment.Text & “)” & “;”
Else
strText = strText & Cells(iRow, iCol).Text & “;”
End If
Next iCol
strText = Left(strText, Len(strText) - 1)
Print #1, strText
strText = “”
Next iRow
Close
End Sub
If you want to know how many lines a particular text file has, this macro will tell you:
Sub Test1()
Dim MyObject As Object, LineCount As Variant
Set MyObject = _
CreateObject(“Scripting.FileSystemObject”)
With MyObject.OpenTextFile(“C:\YourFilePath\YourFileName.txt”, 1)
LineCount = Split(.ReadAll, vbNewLine)
End With
MsgBox UBound(LineCount) - LBound(LineCount) + 1
End Sub
Export each sheet in this workbook as a text file, with each file named as the sheet tab name. Text
file macros compile very quickly.
Sub TextExport()
Dim rng As Range
Dim iWks As Integer, LRow As Long, iCol As Long
Dim sTxt As String, sPath As String
sPath = “C:\YourFilePath\”
For iWks = 1 To Worksheets.Count
Open sPath & Worksheets(iWks).Name & “.txt” For Output As #1
Set rng = Worksheets(iWks).Range(“A1”).CurrentRegion
For LRow = 1 To rng.Rows.Count
For iCol = 1 To rng.Columns.Count
sTxt = sTxt & Worksheets(iWks).Cells(LRow, iCol).Value & vbTab
Next iCol
Print #1, Left(sTxt, Len(sTxt) - 1)
sTxt = “”
Search JabSto ::




Custom Search