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.

 

[Joe4]

Welcome to the Board!

You can get much of the VBA code that you need by turning on the Macro Recorder and recording yourself perform the steps you want manually.
Then, when finished, stop the Macro Recorder and view your code.

That will give you a good starting point. If you need/want to make change to it, post your code with an explanation of what needs to be added to it.
Be sure to include the important details! (i.e. folder locations, etc).

 

[Vasu88]

Hi ,

Thanks for reply. I am using below code for achieve my scenario. However, the column is inserting for the first file in the folder for other excel files not inserting.
Also, same like we have multiple folder and with in folder we have multiple .csv file. I am thinking that we should apply some For loop or Do while loop to achieve the scenario but I am not sure how to apply those loop for process folder by folder.


'Constants
Const xlOpenXMLWorkbook = 51 '(without macro's in 2007-2016, xlsx)
Const xlOpenXMLWorkbookMacroEnabled = 52 '(with or without macro's in 2007-2016, xlsm)
Const xlExcel12 = 50 '(Excel Binary Workbook in 2007-2016 with or without macro's, xlsb)
Const xlExcel8 =56 '(97-2003 format in Excel 2007-2016, xls)
Const xlToRight = -4161
const xlColumns = &H2
const xlLinear = -4132
const xlDay = 1

' Extensions for old and new files
strExcel = "xlsx"
strCSV = "csv"
strXLS = "xls"

' Set up filesystem object for usage
Set objFSO = CreateObject("Scripting.FileSystemObject")

strFolder = "C:\VB"

' Access the folder to process
Set objFolder = objFSO.GetFolder(strFolder)

' Load Excel (hidden) for conversions
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
objExcel.DisplayAlerts = False

' Process all files
For Each objFile In objFolder.Files
' Get full path to file
strPath = objFile.Path
' Only convert CSV files
If LCase(objFSO.GetExtensionName(strPath)) = LCase(strCSV) Or LCase(objFSO.GetExtensionName(strPath)) = LCase(strXLS) Then
' Display to console each file being converted
Wscript.Echo "Converting """ & strPath & """"
' Load CSV into Excel and save as native Excel file
Set objWorkbook = objExcel.Workbooks.Open(strPath)
objWorkbook.Worksheets("XXX").Range("B:C").EntireColumn.Insert
strNewPath = objFSO.GetParentFolderName(strPath) & "\" & objFSO.GetBaseName(strPath) & "." & strExcel
objWorkbook.SaveAs strNewPath, xlOpenXMLWorkbook
objWorkbook.Close False
Set objWorkbook = Nothing

End If

Next

'Wrap up
objExcel.Quit
Set objExcel = Nothing
Set objFSO = Nothing

 

[Vasu88]

Also, not sure for file by file inside the each folder.
It would be great help to get the exact code

 

[Vasu88]

Also, I don't have developer tab in my excel version so I am not able to record macro since I am not developer. The above requirement one of the part in my project

Also, not sure for file by file inside the each folder.
It would be great help to get the exact code

 

[Joe4]

Also, I don't have developer tab in my excel version so I am not able to record macro since I am not developer. The above requirement one of the part in my project

It is probably just hidden.
See here: Show the Developer tab

 

[Joe4]

Where did you get your code that you are using?
It looks to be pretty sophisticated object oriented code (not my forte).
It looks like it should already be looping through all the files in your folder, but I do not see any code for inserting any columns.

How many columns does your original data have?
Are you inserting the columns in the middle of the data, or at the end of it?
Also, you talk about having one of the inserted columns be a drop-down, but you don't mention what the available values in it should be.

 

[Vasu88]

Where did you get your code that you are using?
It looks to be pretty sophisticated object oriented code (not my forte).
It looks like it should already be looping through all the files in your folder, but I do not see any code for inserting any columns.

How many columns does your original data have?
Are you inserting the columns in the middle of the data, or at the end of it?
Also, you talk about having one of the inserted columns be a drop-down, but you don't mention what the available values in it should be.

Hi Joe,

I got the code from google. As you mentioned I did the macro recorder that whatever I needed and club with exiting conversion code see below. However, I am executing the file and getting error on this line (Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove).

Currently, I am doing POC of my project and I have created three .csv file with four column name like A,B,C,D in folder "C:\VB". I want to convert my .CSV file to excel file after convert need to add two column in excel like E and F and also in F column I need data validation for Yes or No


Function ColAdd()
Columns("E:E").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("E1").Select
ActiveCell.FormulaR1C1 = "E"
Columns("F:F").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("F1").Select
ActiveCell.FormulaR1C1 = "F"
Range("F:F").Select
With Selection.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").Select
ActiveCell.FormulaR1C1 = "yes"
Range("I2").Select
ActiveCell.FormulaR1C1 = "no"
Columns("I:I").Select
Selection.EntireColumn.Hidden = True
End Function

'Constants
Const xlOpenXMLWorkbook = 51 '(without macro's in 2007-2016, xlsx)
Const xlOpenXMLWorkbookMacroEnabled = 52 '(with or without macro's in 2007-2016, xlsm)
Const xlExcel12 = 50 '(Excel Binary Workbook in 2007-2016 with or without macro's, xlsb)
Const xlExcel8 =56 '(97-2003 format in Excel 2007-2016, xls)


' Extensions for old and new files
strExcel = "xlsx"
strCSV = "csv"
strXLS = "xls"

' Set up filesystem object for usage
Set objFSO = CreateObject("Scripting.FileSystemObject")

strFolder = "C:\VB"

' Access the folder to process
Set objFolder = objFSO.GetFolder(strFolder)

' Load Excel (hidden) for conversions
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
objExcel.DisplayAlerts = False

' Process all files
For Each objFile In objFolder.Files
' Get full path to file
strPath = objFile.Path
' Only convert CSV files
If LCase(objFSO.GetExtensionName(strPath)) = LCase(strCSV) Or LCase(objFSO.GetExtensionName(strPath)) = LCase(strXLS) Then
' Display to console each file being converted
Wscript.Echo "Converting """ & strPath & """"
' Load CSV into Excel and save as native Excel file
Set objWorkbook = objExcel.Workbooks.Open(strPath)
strNewPath = objFSO.GetParentFolderName(strPath) & "\" & objFSO.GetBaseName(strPath) & "." & strExcel
objWorkbook.SaveAs strNewPath, xlOpenXMLWorkbook
objWorkbook.Close False
Set objWorkbook = Nothing

End If

Next


For Each objFile In objFolder.Files
' Get full path to file
strPath = objFile.Path
' Only convert CSV files
If (LCase(objFSO.GetExtensionName(strPath)) = LCase(strXLS) Then
Set objWorkbook = objExcel.Workbooks.Open(strPath)
Call ColAdd()
objWorkbook.Save
objWorkbook.Close False
Set objWorkbook = Nothing
End If

Next
'Wrap up
objExcel.Quit
Set objFSO = Nothing
Set objExcel = Nothing

 

[Joe4]

OK, a few things.

First, it is important to understand the difference between Procedures and Functions:
- Procedures: typically used when you want to perform a bunch of different actions on your worksheet
- Functions: typically used when you want to take some paramters are return a singular result (like Excel's built-in functions)

So, for what you are trying to do, you want to use a "Sub" Procedure and not a Function.

Also, while the Macro Recorder is great for getting code, it is very literal and records each and every cell selection. Usually, you can clean up your code a little by combining all rows that end in "Select" or "Activate" with the following row if it begins with "Selection" (it is typically not necessary to select ranges in order to work with them). This will help it run faster and get rid of a lot of the annoying screen flashing.

So if I clean-up your recorded code a bit, it might look like this:

Sub ColAdd()

    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

End Sub

So, try replacing what you have for "ColAdd" with this code, and give it a test run and see how it works.

One final note. When posting code on this forum, please use the Code Tags (like I have done). It makes the code much easier for us to read and follow.
See: How to Post Your VBA Code

 

[Vasu88]

OK, a few things.

First, it is important to understand the difference between Procedures and Functions:
- Procedures: typically used when you want to perform a bunch of different actions on your worksheet
- Functions: typically used when you want to take some paramters are return a singular result (like Excel's built-in functions)

So, for what you are trying to do, you want to use a "Sub" Procedure and not a Function.

Also, while the Macro Recorder is great for getting code, it is very literal and records each and every cell selection. Usually, you can clean up your code a little by combining all rows that end in "Select" or "Activate" with the following row if it begins with "Selection" (it is typically not necessary to select ranges in order to work with them). This will help it run faster and get rid of a lot of the annoying screen flashing.

So if I clean-up your recorded code a bit, it might look like this:

Sub ColAdd()

    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

End Sub

So, try replacing what you have for "ColAdd" with this code, and give it a test run and see how it works.

One final note. When posting code on this forum, please use the Code Tags (like I have done). It makes the code much easier for us to read and follow.
See: How to Post Your VBA Code

Hi Joe,

I have tried to execute your script and I am getting an error like "Only comments may appear after End Sub, End Function and End Property"

[CODE=vba][CODE=vba][CODE=vba]Sub ColAdd()

    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

End Sub

'Constants 
Const xlOpenXMLWorkbook = 51             '(without macro's in 2007-2016, xlsx)
Const xlOpenXMLWorkbookMacroEnabled = 52 '(with or without macro's in 2007-2016, xlsm)
Const xlExcel12 = 50                     '(Excel Binary Workbook in 2007-2016 with or without macro's, xlsb)
Const xlExcel8 =56                       '(97-2003 format in Excel 2007-2016, xls)


' Extensions for old and new files
strExcel = "xlsx"
strCSV = "csv"
strXLS = "xls"

' Set up filesystem object for usage
Set objFSO = CreateObject("Scripting.FileSystemObject")

strFolder = "C:\VB"

' Access the folder to process
Set objFolder = objFSO.GetFolder(strFolder)

' Load Excel (hidden) for conversions
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
objExcel.DisplayAlerts = False

' Process all files
For Each objFile In objFolder.Files
    ' Get full path to file
    strPath = objFile.Path
    ' Only convert CSV files
    If LCase(objFSO.GetExtensionName(strPath)) = LCase(strCSV) Or LCase(objFSO.GetExtensionName(strPath)) = LCase(strXLS) Then
        ' Display to console each file being converted
        Wscript.Echo "Converting """ & strPath & """"
        ' Load CSV into Excel and save as native Excel file
        Set objWorkbook = objExcel.Workbooks.Open(strPath)
        strNewPath = objFSO.GetParentFolderName(strPath) & "\" & objFSO.GetBaseName(strPath) & "." & strExcel
        objWorkbook.SaveAs strNewPath, xlOpenXMLWorkbook
        objWorkbook.Close False
        Set objWorkbook = Nothing
                
    End If
    
Next


For Each objFile In objFolder.Files
    ' Get full path to file
    strPath = objFile.Path
    ' Only convert CSV files
    If (LCase(objFSO.GetExtensionName(strPath)) = LCase(strXLS) Then
       Set objWorkbook = objExcel.Workbooks.Open(strPath)
       Call ColAdd()
       objWorkbook.Save
       objWorkbook.Close False
       Set objWorkbook = Nothing               
    End If
    
Next
'Wrap up
objExcel.Quit
Set objFSO = Nothing
Set objExcel = Nothing