help me code vba edit file csv!!!

[Nguyen Anh Dung]

help me code vba:
delete row A1:A48
delete column G49-Z49
replace time: 2019-08-29T02:28:44Z->2019-08-29 02:28:44 (character 'T' and 'Z' ='')
insert column time_n
column time_n=column time+time(7,0,0)
link file: File.rar

file old:

file result:

 

[RayFrye]

I can help, what does your current code look like?

 

[Nguyen Anh Dung]

I can help, what does your current code look like?

i want delete row 1 to row 48 and delete column G49-Z49. after replace time: 2019-08-29T02:28:44Z->2019-08-29 02:28:44 (repleace character 'T' and 'Z' ='')
Keep header:

ID

trksegID

lat

lon

ele

time

and insert column time_n at column G1. column time_n=column time +time(7,0,0)

 

[Nguyen Anh Dung]

 Sub ProcessMultipleFiles()
 Dim FolderPath As String, FilePath As String, NewFileName As String
    Dim FSO As Object
    Set FSO = CreateObject("Scripting.FileSystemObject")
    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = True
        .Filters.Clear
        .Filters.Add "CSV (MS-DOS)", "*.csv"
        If .Show Then
            FolderPath = FSO.GetParentFolderName(.SelectedItems(1)) & "\"
            For i = 1 To .SelectedItems.Count
                FilePath = .SelectedItems(i)
                NewFileName = FSO.GetBaseName(FilePath)
                NewFileName = Left(NewFileName, Len(NewFileName) - 4) & "_N.csv"
                FSO.CopyFile FilePath, FolderPath & NewFileName, True
                CSVAmend2 FolderPath, NewFileName
            Next
        End If
    MsgBox "Ho" & ChrW(224) & "n Th" & ChrW(224) & "nh !!!"
    End With
   End Sub
    
-------------------------------------------------------------------------------------------------------------    
    Sub CSVAmend2(FolderPath As String, FileName As String)
    Dim wb As Workbook, ws As Worksheet, rng As Range
    
    Set wb = Workbooks.Open(FolderPath & FileName)
    Set ws = wb.Sheets(1)
    With rng.Offset(, 7)
        .Formula = "=F50"
        .Resize(, 2).NumberFormat = "YYYY-MM-DD hh:mm:ss"
        .Value = .Value
        .Offset(, 1).Value = .Value
    End With
     ws.Range("G:Z").Delete Shift:=xlToLeft
    wb.Close SaveChanges:=True                        
  
End Sub

I can help, what does your current code look like?

 

[Nguyen Anh Dung]

 Sub ProcessMultipleFiles()
 Dim FolderPath As String, FilePath As String, NewFileName As String
    Dim FSO As Object
    Set FSO = CreateObject("Scripting.FileSystemObject")
    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = True
        .Filters.Clear
        .Filters.Add "CSV (MS-DOS)", "*.csv"
        If .Show Then
            FolderPath = FSO.GetParentFolderName(.SelectedItems(1)) & "\"
            For i = 1 To .SelectedItems.Count
                FilePath = .SelectedItems(i)
                NewFileName = FSO.GetBaseName(FilePath)
                NewFileName = Left(NewFileName, Len(NewFileName) - 4) & "_N.csv"
                FSO.CopyFile FilePath, FolderPath & NewFileName, True
                CSVAmend2 FolderPath, NewFileName
            Next
        End If
    MsgBox "Ho" & ChrW(224) & "n Th" & ChrW(224) & "nh !!!"
    End With
   End Sub
        
Sub CSVAmend2(FolderPath As String, FileName As String)
    Dim wb As Workbook, ws As Worksheet, rng As Range
    Set wb = Workbooks.Open(FolderPath & FileName)
  
    Set ws = wb.Sheets(1)
    Application.DisplayAlerts = False
    ws.Range("A1:AE48").SpecialCells(xlCellTypeVisible).Delete
    ws.Range("G49:Z49").EntireColumn.Delete
    Application.DisplayAlerts = True
    wb.Close SaveChanges:=True
        
End Sub

 

[Nguyen Anh Dung]

I can help, what does your current code look like?

When running code accumulated to column A

 Sub ProcessMultipleFiles()
 Dim FolderPath As String, FilePath As String, NewFileName As String
    Dim FSO As Object
    Set FSO = CreateObject("Scripting.FileSystemObject")
    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = True
        .Filters.Clear
        .Filters.Add "CSV (MS-DOS)", "*.csv"
        If .Show Then
            FolderPath = FSO.GetParentFolderName(.SelectedItems(1)) & "\"
            For i = 1 To .SelectedItems.Count
                FilePath = .SelectedItems(i)
                NewFileName = FSO.GetBaseName(FilePath)
                NewFileName = Left(NewFileName, Len(NewFileName) - 4) & "_N.csv"
                FSO.CopyFile FilePath, FolderPath & NewFileName, True
                CSVAmend2 FolderPath, NewFileName
            Next
        End If
    MsgBox "Ho" & ChrW(224) & "n Th" & ChrW(224) & "nh !!!"
    End With
   End Sub
        
Sub CSVAmend2(FolderPath As String, FileName As String)
    Dim wb As Workbook, ws As Worksheet, rng As Range
    Set wb = Workbooks.Open(FolderPath & FileName)
    Set ws = wb.Sheets(1)
    Application.DisplayAlerts = False
    Columns("F").Replace What:="T", Replacement:=" "
    Columns("F").Replace What:="Z", Replacement:=""
    wb.Worksheets(1).Columns("f:f").NumberFormat = "yyyy-mm-dd hh:mm:ss"
    ws.Range("A1:AE48").SpecialCells(xlCellTypeVisible).Delete
    ws.Range("G49:Z49").EntireColumn.Delete
    Application.DisplayAlerts = True
    wb.Close SaveChanges:=True
End Sub

 

[Fluff]

Cross posted Help VBA code edit file csv!!!

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.