Consulting

Results 1 to 3 of 3

Thread: Opening and editing mutiple csv files

  1. #1
    VBAX Regular
    Joined
    Feb 2015
    Posts
    79
    Location

    Opening and editing mutiple csv files

    Looking for help.
    I am fairly new to VBA and am trying to accomplish the following:

    I have multiple csv files (up to 80) in a central location, that I need to open and possibly edit (by deleting column "A" if it has "FileName" in "A1").

    I then need to rename the file to be able to use it in another application.
    An example of the file name that I receive is:

    PPS_PROJECT1_F18_January_XXX_10JAN2018.csv

    I need it to be renamed (using the Main workbook for the conversion):

    POA01_Q1_10JAN18.csv

    I am using Main workbook to initiate the conversion and hold the lookup list for the projects.

    Attached are 4 example files (3 that need the first column deleted and one that does not) That I receive and the Main workbook.

    Any help/tie yo can give is greatly appreciated. Please let me know if you require more information.

    Sincerely,

    Chunk

  2. #2
    VBAX Regular
    Joined
    Feb 2015
    Posts
    79
    Location
    PPS_PROJECT1_F18_January_XXX_10JAN2018.csv

    should read

    PPS_PROJECT1_FY18_Q1_January_XXX_10JAN2018.csv

  3. #3
    VBAX Regular
    Joined
    Feb 2015
    Posts
    79
    Location
    Here is what I ended up using.

    Hope it helps.

    Chunk

    Sub UpdateFiles()
    
    Dim wb As Workbook
    Dim myPath As String
    Dim myFile As String
    Dim myExtension As String
    Dim rng As Range
    Dim wbrng As Range
    Dim proj As String
    
    
    Dim nwproj As String
    Dim nwtype As String
    Dim nwdate1 As String
    Dim nwdate2 As String
    Dim nwdate3 As String
    Dim nwFile As String
    
    
    
    
    Set rng = Worksheets("LookUpList").Range("D2:D40")
    
    
    
    
    'Optimize Macro Speed
      Application.ScreenUpdating = False
      Application.EnableEvents = False
      Application.Calculation = xlCalculationManual
    
    
            For Each cell In rng
            
                If cell.Value = "Y" Then
                    'Target File Extension (must include wildcard "*")
                    myExtension = "*.csv"
                    
                    proj = cell.Offset(0, -2)
                    
                    
                    myPath = "C:\ChunksStuff\datfiles\"
                    'Target Path with Ending Extention
                    myFile = Dir(myPath & "*" & proj & myExtension)
    
    
                    'Loop through each Excel file in folder
                    Do While myFile <> ""
                        'Set variable equal to opened workbook
                        Set wb = Workbooks.Open(myPath & myFile)
        
                        'Ensure Workbook has opened before moving on to next line of code
                        DoEvents
        
                        'Delete first column
                        With wb
                            If Range("A1") = "FileName" Then
                                Range("A:A").Select
                                Selection.Delete
                              Else
                                
                            End If
                                
                        End With
    
    
                        'Configure variables to rename file
                        nwproj = cell.Offset(0, 1)
                        
                       
                        If InStr(myFile, "_TL_") Then
                            nwtype = "_TL_"
                          ElseIf InStr(myFile, "_NN_") Then
                            nwtype = "_NN_"
                          ElseIf InStr(myFile, "_NU_") Then
                            nwtype = "_NU_"
                        End If
    
    
                        
                        nwdate1 = Right(myFile, 13)
                        nwdate2 = Left(nwdate1, 5)
                        nwdate3 = Mid(nwdate1, 8, 9)
                        
                        If InStr(myFile, "PPS") Then
                            nwFile = ("PO" & nwproj & nwtype & nwdate2 & nwdate3)
                          ElseIf InStr(myFile, "PWL") Then
                            nwFile = ("WO" & nwproj & nwtype & nwdate2 & nwdate3)
                        End If
    
    
                        'Save and Close Workbook
                        wb.SaveAs FileName:=myPath & "Converted\" & nwFile
                        wb.Close SaveChanges:=True
          
                        'Ensure Workbook has closed before moving on to next line of code
                        DoEvents
    
    
                        'Get next file name
                        myFile = Dir
                    Loop
                    
                End If
    
    
            Next cell
    
    
    'Message Box when tasks are completed
      MsgBox "Task Complete!"
    
    
    ResetSettings:
      'Reset Macro Optimization Settings
        Application.EnableEvents = True
        Application.Calculation = xlCalculationAutomatic
        Application.ScreenUpdating = True
    
    
        
        
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •