troubleshooting Question

MS Access VBA to convert File types

Avatar of CFMI
CFMIFlag for United States of America asked on
Microsoft AccessMicrosoft Excel
7 Comments1 Solution734 ViewsLast Modified:
Hello Experts,

I wrote some VBA code to switch from pipe delimited to a Tab delimited file but my VAB doesn't debug and I hope it works; can you please help?


Public Function ConvertFile(tcFileIn As String, tcFileOut As String)
    'Declare variables.
    Dim fso As New FileSystemObject
    Dim ts As TextStream
    Dim objFSO As Scripting.FileSystemObject
    Dim objStreamRead As Scripting.TextStream
    Dim objStreamWrite As Scripting.TextStream

    Dim lcLine As String
    Dim laLine() As String
    Dim lcField As String
    Dim i As Integer
    Set objStreamWrite = objFSO.CreateTextFile(tcFileOut)
    Set objStreamRead = objFSO.OpenTextFile(tcFileIn)
    Do Until objStreamRead.AtEndOfStream
        lcLine = objStreamRead.ReadLine
        laLine = Split(lcLine, "/")
        lcLine = ""
        For i = 1 To UBound(laLine)
            lcField = Trim(laLine(j))
            If Right(lcField, 1) = Chr(10) Then
                lcField = Trim(Left(lcField, Len(lcField) - 1))
            End If
            lcLine = lcLine & lcField & Chr(9)
            i = i + 1
        Next i
        objStreamWrite.Write (lcLine) & Chr$(10)
End Function
IT Manager

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros