EXCEL VBA read / write . dft file (IBM Data Tranfer)

Question

I would like to edit a .dtf (IBM Data Transfer file) via VBA. Opening it in excel is causing me problems so I was hoping to read the file line-by-line and export to a new .dtf. However something goes wrong when I try to Print #1 the string to the .dtf

Sub test()
    Dim TemplateFilePath As String
    TemplateFilePath = "C:\TemplateFile.dtf"
    Call CreateDataTranderFile(TemplateFilePath)
End Sub



Public Sub CreateDataTranderFile(TemplateFilePath As String)
    Dim output As String
    Dim OutFullPath As String
    Dim Line As String
    Dim FileNum As Integer

    OutFullPath = "C:\tmpTranferfile.dtf"


    'Read from template
    FileNum = FreeFile()
    Open TemplateFilePath For Input As #FileNum
        While Not EOF(FileNum)
            Line Input #FileNum, Line
            output = output & Line
        Wend
    Close #FileNum

    'Write .dtf
    Open OutFullPath For Output As #1
        Print #1, output
    Close
End Sub

With output = output & Line you skip CRLF. Is that on intention? Although I do not believe it has anything to do with your problem. Can you check what the encoding of your input file is? — Storax, Mar 08 '17 at 18:35
Indeed was UNICODE. Just learned how to check http://stackoverflow.com/questions/6947749/how-to-check-if-a-txt-file-is-in-ascii-or-utf-8-format-in-windows-environment — BuckTurgidson, Mar 09 '17 at 09:58

Storax · Accepted Answer · 2017-03-08T19:16:04.670

So, I assume your input file is an UNICODE file then you coud use the following function

Function ReadUniCodeTextFile(inpFile As String) As String

Dim sText As String
Dim objFSO As Object
Dim objFile As Object

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFile = objFSO.OpenTextFile(inpFile, 1, False, -1)
    ReadUniCodeTextFile = objFile.ReadAll
    objFile.Close

End Function

And replace

   'Read from template
    FileNum = FreeFile()
    Open TemplateFilePath For Input As #FileNum
        While Not EOF(FileNum)
            Line Input #FileNum, Line
            output = output & Line
        Wend
    Close #FileNum

just with

output = ReadUniCodeTextFile(TemplateFilePath)

So your code should look like this then

Public Sub CreateDataTranderFile(TemplateFilePath As String)
Dim output As String
Dim OutFullPath As String
Dim Line As String
Dim FileNum As Integer

    OutFullPath = "C:\tmpTranferfile.dtf"

    'Read from template
    output = ReadUniCodeTextFile(TemplateFilePath)

    'Write .dtf
    Open OutFullPath For Output As #1
    Print #1, output
    Close
End Sub

EXCEL VBA read / write . dft file (IBM Data Tranfer)

1 Answers1