Need to convert .csv files from different folders to .xls file with add two column using VB Script

[Vasu88]

Hi VBA Experts,

I am not good with VBA scripts. I am looking for VBA scirpt to convert .csv files from different folder and convert into .xls file while converting the excel file should have additional two column with title (XYZ and XYZ1). Once column should have blank and another column should have drop down at cell level.

I found many script and did not work properly.

Could someone please help me to get the VB Script for achieve the above scenario. Please let me know if any query on the same.

 

[Vasu88]

Basically the column insertion is not working on the particular path "C:\VB"

The column insertion is working in excel work sheet where I am running the Macro

 

[Joe4]

As I mentioned earlier, using FileSystem Objects is really not my forte. I usually loop through file like shown here: VBA Loop Through All CSV Files in a Folder or Directory | VBAF1

If we were to use that method, then I think your code would look something like this:

Sub FileLoopingMacro()
    
    'Variable Declaration
    Dim sFilePath As String
    Dim sFileName As String
    Dim sFullName As String
    Dim sNewName As String
    
    'Specify File Path
    sFilePath = "C:\VB"
    
    'Check for back slash
    If Right(sFilePath, 1) <> "\" Then
        sFilePath = sFilePath & "\"
    End If
        
    sFileName = Dir(sFilePath & "*.csv")
    
    Do While Len(sFileName) > 0
        If Right(sFileName, 3) = "csv" Then
            'Build full name
            sFullName = sFilePath & sFileName
            'Open CSV file
            Workbooks.Open sFullName
            'Call macro to add columns
            Call ColAdd
            'Build Excel file name
            sNewName = Left(sFullName, Len(sFullName) - 3) & "xlsx"
            'Save and close CSV as xlsx file
            ActiveWorkbook.SaveAs Filename:=sNewName, FileFormat:= _
                xlOpenXMLWorkbook, CreateBackup:=False
            ActiveWorkbook.Close
        End If
        'Set the fileName to the next available file
        sFileName = Dir
    Loop
   
    MsgBox "Macro Complete!"
    
End Sub


Sub ColAdd()

    Application.ScreenUpdating = False
    
    Columns("E:E").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("E1") = "E"

    Columns("F:F").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("F1") = "F"

    With Range("F:F").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="=$I$1:$I$2"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    
    Range("I1") = "yes"
    Range("I2") = "no"
    Columns("I:I").Hidden = True

    Application.ScreenUpdating = True
    
End Sub

I ran a few simple tests and it seemed to work.

 

[Vasu88]

As I mentioned earlier, using FileSystem Objects is really not my forte. I usually loop through file like shown here: VBA Loop Through All CSV Files in a Folder or Directory | VBAF1

If we were to use that method, then I think your code would look something like this:

Sub FileLoopingMacro()
   
    'Variable Declaration
    Dim sFilePath As String
    Dim sFileName As String
    Dim sFullName As String
    Dim sNewName As String
   
    'Specify File Path
    sFilePath = "C:\VB"
   
    'Check for back slash
    If Right(sFilePath, 1) <> "\" Then
        sFilePath = sFilePath & "\"
    End If
       
    sFileName = Dir(sFilePath & "*.csv")
   
    Do While Len(sFileName) > 0
        If Right(sFileName, 3) = "csv" Then
            'Build full name
            sFullName = sFilePath & sFileName
            'Open CSV file
            Workbooks.Open sFullName
            'Call macro to add columns
            Call ColAdd
            'Build Excel file name
            sNewName = Left(sFullName, Len(sFullName) - 3) & "xlsx"
            'Save and close CSV as xlsx file
            ActiveWorkbook.SaveAs Filename:=sNewName, FileFormat:= _
                xlOpenXMLWorkbook, CreateBackup:=False
            ActiveWorkbook.Close
        End If
        'Set the fileName to the next available file
        sFileName = Dir
    Loop
  
    MsgBox "Macro Complete!"
   
End Sub


Sub ColAdd()

    Application.ScreenUpdating = False
   
    Columns("E:E").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("E1") = "E"

    Columns("F:F").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("F1") = "F"

    With Range("F:F").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="=$I$1:$I$2"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
   
    Range("I1") = "yes"
    Range("I2") = "no"
    Columns("I:I").Hidden = True

    Application.ScreenUpdating = True
   
End Sub

I ran a few simple tests and it seemed to work.

Hi Joe,

Yes, it is working now perfectly and it really appreciate Joe. Very thank you so much.

I have last two more doubt on the same.

1. If I want to schedule this code via batch file so that it will process like every one hour or daily or weekly. How can I do that
2. If we have .csv files from multiple folder what change we need to do in the scripts and where we need to change in scripts.
Do we need to do any loop again?

Thanks again Joe

 

[Joe4]

1. If I want to schedule this code via batch file so that it will process like every one hour or daily or weekly. How can I do that

Funny, I am involved in a similar conversation on a thread here: auto run vba module at 09:10:05
There are a few ways of going about this. What I have typically done is put my code in the "Workbook_Open" event in VBA, so that the code runs automatically upon opening the file (and add code to automatically close the file when done). I then use Windows Scheduler on the Server to open the file at my desired days/times.

But there are also other ways of doing it, as mentioned in that link. If you need help doing this, please start a new thread on it, as it really is an entirely different question.

2. If we have .csv files from multiple folder what change we need to do in the scripts and where we need to change in scripts.
Do we need to do any loop again?

I would probably store all my different folder names in an array, and loop through them in VBA.
See the first reply here (just for the array/loop part, not the FileSystem Object part, as we did not use that method): Loop through a specific list of folders and their subFolders VBA Macro