Microsoft Access VBA - Edit Text File

Question

I am trying to edit a text file using Microsoft access vba. What I want to do is the remove all the comma from the text file.

I tried the some of the codes online and it works perfectly fine for my test file.

What the code is to replace the “,” in the file with “”

However, when I try to run it on my actual text file, the access will become not responding , I waited 2 hours , but still didn’t responded.

I not sure is it because of my data is too huge, the size of my text file is 417MB.

Is there anyone is able to advise me on this? Thank you!

The code i am using

Private Sub Click()

Dim sBuf As String
Dim sTemp As String
Dim iFileNum As Integer
Dim sFileName As String
    sFileName = "C:123\123\data.txt"
    iFileNum = FreeFile
    Open sFileName For Input As iFileNum
    Do Until EOF(iFileNum)
        Line Input #iFileNum, sBuf
        sTemp = sTemp & sBuf & vbCrLf
    Loop
    Close iFileNum
    sTemp = Replace(sTemp, ",", "")
    iFileNum = FreeFile
    Open sFileName For Output As iFileNum
    Print #iFileNum, sTemp
    Close iFileNum
End Sub

Answer 1

This line:

sTemp = sTemp & sBuf & vbCrLf

is probably what's killing your performance. See discussion here: Using Pre-Made Stringbuilder Class

You will find it's faster to process the file line-by-line and write out to a different file. Also keeps your original file intact in case you make a mistake...

Private Sub Click()

    Dim sBuf As String
    Dim iFileNum As Integer, iFileNum2 As Integer
    Dim sFileName As String, sFileNameOut As String

    sFileName = "C:123\123\data.txt"
    sFileNameOut = "C:123\123\data_out.txt"

    iFileNum = FreeFile
    Open sFileName For Input As iFileNum

    iFileNum2 = FreeFile
    Open sFileNameOut For Output As iFileNum2

    Do Until EOF(iFileNum)
        Line Input #iFileNum, sBuf
        Write #iFileNum2, Replace(sBuf, ",", "")
    Loop

    Close iFileNum
    Close iFileNum2

End Sub