CODEWITHSUNDEEP
vbacsv
Pshdari Hosein
Pshdari Hosein

Reputation: 3

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

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")

Upvotes: 0

Views: 1368

Answers (1)

Elio Fernandes
Elio Fernandes

Reputation: 1420

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

Upvotes: 1

Related Questions