Convert Text File

[helen1977]

Hello - I need assistance creating a VBA module that converts a vertical bar delimited text file to tab delimited. I cannot use the DoCmd.TransferText method. I do not have to import the file into Access, just do the file conversion in VBA. Can this be done?
Thank you in advance

 

[boblarson]

This should do it for you:

Public Sub ConvertTextFile(strInputFile As String, strOutputFile As String)

    Dim strInputString As String
    Dim strOutputString As String
    Dim varSplit As Variant
    Dim intUB As Integer
    Dim intCount As Integer
    
    Open strInputFile For Input As #1
    Open strOutputFile For Output As #2

    Do Until EOF(1)
        Line Input #1, strInputString
        varSplit = Split(strInputString, "|", , vbTextCompare)
        intUB = UBound(varSplit)
        intCount = 0
        While intCount <> intUB + 1
            strOutputString = strOutputString & varSplit(intCount) & Chr(9)
            intCount = intCount + 1
        Wend
            Print #2, strOutputString
            strOutputString = ""
            intUB = 0
    Loop
    Close #1
    Close #2

End Sub

 

[helen1977]

Thank you Bob, this code was very helpful!

 

[boblarson]

Happy to hear it was helpful and, by the way, Welcome to Access World Forums!