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

Vasu88

New Member
Joined
Aug 11, 2021
Messages
15
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Aug 11, 2021
Messages
15
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,886
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
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

New Member
Joined
Aug 11, 2021
Messages
15
Office Version
  1. 2016
Platform
  1. Windows
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:
VBA Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,886
Office Version
  1. 365
Platform
  1. Windows
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,125
Messages
5,768,250
Members
425,460
Latest member
Astros1243

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top