How to delete first lines of a csv file using VBA?

Question

I receive a csv file in email. I want to delete the first 5 or 6 rows with VBA code in Outlook.

I thought to put the csv file into a string, do what I want then save it again as a csv file. I have done the process of storing inside the string and saving it again as csv file .

I don't know how to delete the lines while the text is inside the string.

This is how I put the csv file in a string.

Dim iTxtFile As Integer
Dim strFile As String
Dim strFileText As String
strFile = "C:\Users\Hussein.azad\Desktop\4G.csv"
iTxtFile = FreeFile
Open strFile For Input As FreeFile
strFileText = Input(LOF(iTxtFile), iTxtFile)
Close iTxtFile
strFileText = Replace(strFileText, "NIL", "0")

Don't read the full file. Read the file line by line, count to five or six, then start writing the lines to a new file. — Gustav
– Gustav, Commented Nov 6, 2021 at 8:07

Elio Fernandes · Accepted Answer · 2021-11-06 17:46:10Z

1

Sub fs_DeleteRowsFromTextFile()
    Const FOR_READING = 1
    Const FOR_WRITING = 2
    
    ' Text file full path
    Dim strFileName As String: strFileName = "C:\deletefirstNlines.txt"
    
    ' Number of lines to delete
    Dim LinesToDelete As Long: LinesToDelete = 6
    
    Dim oFS As Object: Set oFS = CreateObject("Scripting.FileSystemObject")
    
    ' Set Text Stream for reading
    Dim oTS As Object: Set oTS = oFS.OpenTextFile(strFileName, FOR_READING)
    
    ' Copy txt stream contens to variable
    Dim strContents As String: strContents = oTS.ReadAll
    
    ' Close text stream
    oTS.Close
    
    ' Split file into array
    Dim arrLines() As String: arrLines = Split(strContents, vbNewLine)
    
    ' Set Text Stream for writing
    Set oTS = oFS.OpenTextFile(strFileName, FOR_WRITING)
    
    ' Write array back to file exluding 'LinesToDelete'
    Dim i As Long
    For i = 0 To UBound(arrLines)
       If i > (LinesToDelete - 1) Then
          oTS.WriteLine arrLines(i)
       End If
    Next
End Sub

answered Nov 6, 2021 at 17:46

Elio Fernandes

1,4302 gold badges16 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Pshdari Hosein Over a year ago

That's it Elio Thanks

Collectives™ on Stack Overflow

How to delete first lines of a csv file using VBA?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related