CODEWITHSUNDEEP
vbaexcel
Crumbo0
Crumbo0

Reputation: 49

Looping over text files in a folder to remove the quotation marks around every line in VBA

I am trying to loop over every text file in a folder so that the first and last character in every line is removed as these are quotation marks. Please note that my field separator/delimiter is #|#.

Currently: "Column 1#|#Column2#|#...column_n"

Desired: Column 1#|#Column2#|#...column_n

I have made some progress with the VBA but don't know how which operation to use:

Sub clean_text_file()

Dim WB As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim FolderPicker As FileDialog

'Optimise macro settings
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

'Get the target folder
Set FolderPicker = Application.FileDialog(msoFileDialogFolderPicker)
With FolderPicker
.Title = "Select the folder where you want to convert csv files to text files"
.AllowMultiSelect = False
    If .Show <> -1 Then GoTo NextCode
        myPath = .SelectedItems(1) & "\"
End With

'Cancelling the operation
NextCode:
myPath = myPath
If myPath = "" Then GoTo ResetSettings

'Target file extension
myExtension = "*.txt*"

'Folder which contains the files with the extension
myFile = Dir(myPath & myExtension)

'Loop through every files in the excel folder
Do While myFile <> ""
    'set variable to the opened workbook
    Set WB = Workbooks.Open(FileName:=myPath & myFile)
    'make sure that the workbook has opened
    DoEvents

    'Code to remove enclosing quotation marks from every line
    '''CODE TO REMOVE FIRST AND LAST CHARACTER 

    'Ensure that the workbook is closed
    DoEvents

    'Move onto the next file in the folder
    myFile = Dir
Loop

'Completion Message
MsgBox "Files have been converted to text format"

'Reset optimisation settings
ResetSettings:
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub

Would be grateful if a solution could be provided.

Upvotes: 1

Views: 226

Answers (1)

Paul Ogilvie
Paul Ogilvie

Reputation: 25286

Within your Do While loop, use:

Dim textline
Do While myFile <> ""
    Open myFile For Input As #1
    Open myPath & "tmp.txt" For Output As #2
    While Not EOF(1)
        Line Input #1, textline
        textline = Mid(textline, 2, Len(textline) - 2)
        Print #2, textline
    Wend
    Close #1
    Close #2
    Kill myPath & myFile
    Name myPath & "tmp.txt" As myFile
    myFile = Dir
Loop

One note: it could be that, because each file is now replaced with a new file, the new file shows up again in the Dir results. If that is the case, you first have to build an array of file names with the Dir command, and then process each file. That would be:

Dim filenames()
Dim i As Integer, j As Integer

ReDim filenames(0)

Do While myFile <> ""
    If (UBound(filenames, 1) <= i) Then
        ReDim Preserve filenames(UBound(filenames) + 10)
    End If
    i = i + 1
    filenames(i) = myPath & myFile
    myFile = Dir
Loop

For j = 1 To i
    myFile = filenames(j)
    Open myFile For Input As #1
    Open myPath & "tmp.txt" For Output As #2
    While Not EOF(1)
        Line Input #1, textline
        textline = Mid(textline, 2, Len(textline) - 2)
        Print #2, textline
    Wend
    Close #1
    Close #2
    Kill myPath & myFile
    Name myPath & "tmp.txt" As myFile
Next j

Upvotes: 1

Related Questions