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
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"



VBA Code:
[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
Also, Please find the attached error screen shot for your reference.
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Vasu88

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



VBA Code:
[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
Please ignore:
VBA Code:
[CODE=vba][CODE=vba] by mistake it added in the code
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,886
Office Version
  1. 365
Platform
  1. Windows
Where's the "End Sub" line at the end of your code?

Do you understand how to write code and how it needs to be structured?

Except for some global variables you may have at the very top of your code, ALL other code needs to be contained between Sub or Functions, i.e.
Rich (BB code):
Sub somename()
   'Your code here
End Sub

Function somename()
   'Your code here
End Function

You cannot have free floating code that is not contained in a Sub or Function.

You should have two Sub procedures. The one I helped you write, and your original one.
You can call one Sub from another, like you did with the "Call" statement.

If you are going to try to work with VBA, it is important to at least understand some basics about it, i.e. how it is is structured and how to call/run it.
There are lots of tutorials and videos you can find on-line to assist with this. Here is one: Excel VBA Sub Procedures: The Complete Tutorial
 

Vasu88

New Member
Joined
Aug 11, 2021
Messages
15
Office Version
  1. 2016
Platform
  1. Windows
Where's the "End Sub" line at the end of your code?

Do you understand how to write code and how it needs to be structured?

Except for some global variables you may have at the very top of your code, ALL other code needs to be contained between Sub or Functions, i.e.
Rich (BB code):
Sub somename()
   'Your code here
End Sub

Function somename()
   'Your code here
End Function

You cannot have free floating code that is not contained in a Sub or Function.

You should have two Sub procedures. The one I helped you write, and your original one.
You can call one Sub from another, like you did with the "Call" statement.

If you are going to try to work with VBA, it is important to at least understand some basics about it, i.e. how it is is structured and how to call/run it.
There are lots of tutorials and videos you can find on-line to assist with this. Here is one: Excel VBA Sub Procedures: The Complete Tutorial
Hi Joe,

Yes, you are right. But it is one only requirement and may will not use VBA in future
Could you please help me to give full code with proper syntax
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,886
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You need to put your original code inside a sub procedure, like I described in my previous post!
Right now, you just have it "floating out there in space", which will not work and return errors.

So it needs look something like this:
Rich (BB code):
Sub MyOriginalMacro()

'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

End Sub



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
 

Vasu88

New Member
Joined
Aug 11, 2021
Messages
15
Office Version
  1. 2016
Platform
  1. Windows
Hi Joe,

I have tried the code which you provided and it is working fine. However, it is not working the files which is in the folder "C:\VB" only conversation is happening. Its looks like the call function is not working fine.
Could you please help me with that issue
 

Vasu88

New Member
Joined
Aug 11, 2021
Messages
15
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

You need to put your original code inside a sub procedure, like I described in my previous post!
Right now, you just have it "floating out there in space", which will not work and return errors.

So it needs look something like this:
Rich (BB code):
Sub MyOriginalMacro()

'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

End Sub



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
Hi Joe,

I have tried the code which you provided and it is working fine. However, it is not working the files which is in the folder "C:\VB" only conversation is happening. Its looks like the call function is not working fine.
Could you please help me with that issue
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,886
Office Version
  1. 365
Platform
  1. Windows
I have tried the code which you provided and it is working fine. However, it is not working the files which is in the folder "C:\VB" only conversation is happening. Its looks like the call function is not working fine.
I am confused. Is it working or is it not?
How can it be working fine if it is not processing the file?
Exactly what is it doing?

I do not use File System Objects in my VBA code a lot, so I am certainly no expert in that realm, but maybe if I have a better idea what is happening, we can figure it out.
 

Vasu88

New Member
Joined
Aug 11, 2021
Messages
15
Office Version
  1. 2016
Platform
  1. Windows
I am confused. Is it working or is it not?
How can it be working fine if it is not processing the file?
Exactly what is it doing?

I do not use File System Objects in my VBA code a lot, so I am certainly no expert in that realm, but maybe if I have a better idea what is happening, we can figure it out.
Hi Joe,

Let me explain, I have ran this code in Macro where is in excel.
1. Open excel worksheet
2. Go to View --> Select View Macros --> then one POPUP window opened and asking for create Macro Name
3. I created one Macro --> It will open Microsoft Visual Basic application.
4. I pasted the code in that which you shared previously
5. The file conversion procedure is working for that particular folder "C:\VB"
6. Column insert procedure is working in the excel worksheet where we run the Macro code (Point 1)
 

Vasu88

New Member
Joined
Aug 11, 2021
Messages
15
Office Version
  1. 2016
Platform
  1. Windows
Hi Joe,

Let me explain, I have ran this code in Macro where is in excel.
1. Open excel worksheet
2. Go to View --> Select View Macros --> then one POPUP window opened and asking for create Macro Name
3. I created one Macro --> It will open Microsoft Visual Basic application.
4. I pasted the code in that which you shared previously
5. The file conversion procedure is working for that particular folder "C:\VB"
6. Column insert procedure is working in the excel worksheet where we run the Macro code (Point 1)
Basically the column insertion is not working on the particular path "C:\VB"
 
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