help me code vba edit file csv!!!

Nguyen Anh Dung

Board Regular
Joined
Feb 28, 2020
Messages
84
Office Version
2016
Platform
Windows
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:
1596337236427.png

file result:
1596337262562.png
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

RayFrye

Board Regular
Joined
Jan 31, 2005
Messages
81
Office Version
365, 2019
I can help, what does your current code look like?
 

Nguyen Anh Dung

Board Regular
Joined
Feb 28, 2020
Messages
84
Office Version
2016
Platform
Windows
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:

IDtrksegIDlatloneletime

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

Nguyen Anh Dung

Board Regular
Joined
Feb 28, 2020
Messages
84
Office Version
2016
Platform
Windows
Code:
 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

Board Regular
Joined
Feb 28, 2020
Messages
84
Office Version
2016
Platform
Windows
Code:
 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

Board Regular
Joined
Feb 28, 2020
Messages
84
Office Version
2016
Platform
Windows
I can help, what does your current code look like?
When running code accumulated to column A
Code:
 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
 

Attachments

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,786
Office Version
365
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,561
Messages
5,487,571
Members
407,605
Latest member
PACULA

This Week's Hot Topics

Top