Export to and Import from Tab delimited Text file

Vishesh's picture
Following are two functions to Export to and Import from Tab delimited Text file. Alternatively, you can download the attached file to see how it works.

Export:

Sub SaveRangeToTabDelimitedTextFile(rngToCopy As Range, strTargetFilePath As String)
    Dim wbkNew As Workbook
 
    Set wbkNew = Workbooks.Add(1)
 
    rngToCopy.Copy
    wbkNew.Worksheets(1).Range("A1").PasteSpecial xlPasteValues
    Application.CutCopyMode = False
 
    Application.DisplayAlerts = False
    wbkNew.SaveAs strTargetFilePath, -4158
    wbkNew.Close False
    Application.DisplayAlerts = True
 
    Set wbkNew = Nothing
End Sub

Import:

Public Sub ImportTextFile(strFileName As String, strSeparator As String, rngTgt As Range)
    Dim lngTgtRow As Long
    Dim lngTgtCol As Long
    Dim varTemp As Variant
    Dim strWholeLine As String
    Dim intPos As Integer
    Dim intNextPos As Integer
    Dim intTgtColIndex As Integer
    Dim wks As Worksheet
 
    Set wks = rngTgt.Parent
 
    intTgtColIndex = rngTgt.Column
    lngTgtRow = rngTgt.Row
 
    Open strFileName For Input Access Read As #1
 
    While Not EOF(1)
        Line Input #1, strWholeLine
        If Right(strWholeLine, 1) <> strSeparator Then
            strWholeLine = strWholeLine & strSeparator
        End If
        lngTgtCol = intTgtColIndex
        intPos = 1
        intNextPos = InStr(intPos, strWholeLine, strSeparator)
        While intNextPos >= 1
            varTemp = Mid(strWholeLine, intPos, intNextPos - intPos)
            Cells(lngTgtRow, lngTgtCol).Value = varTemp
            intPos = intNextPos + 1
            lngTgtCol = lngTgtCol + 1
            intNextPos = InStr(intPos, strWholeLine, strSeparator)
        Wend
        lngTgtRow = lngTgtRow + 1
    Wend
 
    Close #1
    Set wks = Nothing
End Sub
AttachmentSize
RangeToTabDelimitedTextFile.xls37 KB