Code to remove specific lines from a large number of .csv files

[spliu]

Hello all,

I have no prior knowledge of VBA code, so please bear withme if this question has been answered before in some way or another.

The context of the problem is that I have a relativelylarge (>100) number of .csv files which all follow the same format and the samethree rows need to be removed from each in order for data processing on them toproceed. The row lines are 19, 21 and 23 – up til now I have been using a macroand obviously it is rather tedious, but doesn’t take too much time. However, Iwould like to speed it up even more.

I was hoping to adapt this code from another topic: https://www.mrexcel.com/forum/excel-questions/553423-how-delete-4-lines-each-workbook-specific-folder-vba-code-required.html
Except I would make these changes:

• Alterthe preamble warning windows as these are .csv files, not .xls.
  
• ChangeDir(“.xls”) to Dir(“.csv”)
  
• Changethe rows in the parentheses to the correct coordinates, so WS.Rows(“1:4”)becomes WS.Rows(“19:19,21:21,23:23”).
  However when I ran this code in the editor it onlyprocessed one file out of 128 in the directory which contained all of my rawdata – what it did was open and close every other file without changing them atall!
  
  Can anyone help me with providing the corrected code formy purpose? Would also like to know where I went wrong.
  
  Thanks in advance.

 

[ranman256]

delete the rows ,yet keep them .csv?

ChangeDir(“.xls”) to Dir(“.csv”) , is not needed, just save using the full path: c:\folder\file.csv


usage:
FixAllFilesInDir "c:\folder\

Public Sub FixAllFilesInDir(ByVal pvDir)
Dim vFil, vTargT
Dim i As Integer
Dim fso
Dim oFolder, oFile
Dim vOutFile


On Error GoTo errImp
If Right(pvDir, 1) <> "\" Then pvDir = pvDir & "\"


Set fso = CreateObject("Scripting.FileSystemObject")
Set oFolder = fso.GetFolder(pvDir)


For Each oFile In oFolder.Files
    vFil = pvDir & oFile.Name
    If InStr(vFil, ".csv") > 0 Then      'ONLY DO CSV FILES
          workbooks.open vFil


              'PUT YOUR CODE HERE


          workbooks.close   
    End If
Next


Set fso = Nothing
Set oFile = Nothing
Set oFolder = Nothing
MsgBox "Done"
Exit Sub


errImp:
MsgBox Err.Description, vbCritical, "clsImport:ImportData()" & Err
Exit Sub
Resume
End Sub

 

[spliu]

Thanks for the response (and sorry about the lack of spaces in the first post).

In terms of usage, what do you mean by that? Is there a command window other than the Module window in the editor? Apologies for my ignorance as I'm not sure how to specify a file path.


Also, I'm guessing that I don't just copy and paste the entirety of the code because it already contains commands to open the .csv files is that correct? I've inferred this from the "Sub.OpenAll()" line, which parts should not be copied over?