Change Multiple csv files into xlsx files

Hi All,

I have a folder that receives many csv file extracts which I need to convert into xlsx files instead.

I have the code below which I found on one site, but I receive an error at line 3
Dim CSVfolder As String 

Open in new window


Error:
Expected end of statement

I haven't been able to test it yet, so I actually have no idea if this is even on the correct path or not...

Sub CSVtoXls() 

Dim CSVfolder As String 
Dim XlsFolder As String 
Dim fname As String 
Dim wBook As Workbook 

CSVfolder = "C:\Users\Desktop\3rd Party\Work Folder\" 
XlsFolder = "C:\Users\Desktop\3rd Party\Work Folder\xlsx\" 
fname = Dir(CSVfolder & "*.csv") 

Do While fname <> "" 

Set wBook = Workbooks.Open(CSVfolder & fname, Format:=6, Delimiter:=",") 
wBook.SaveAs XlsFolder & Replace(fname, ".csv", ".xls") 
wBook.Close False 
fname = Dir 
Loop 

End Sub 

Open in new window


Thank you in advance
Eitel DagninIT Security AdministratorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
Bill PrewCommented:
Short on time today, but here is a VBS approach (not VBA) I did for another question a short while ago.  Sharing it here in case it's useful for you...

Save as a VBS file, and then run as follows, where the parameter passed in to the VBS is the folder where the CSV files exist (and where the new XLSX files will be placed).

cscript EE29088597.vbs "B:\EE\EE29088597\Files"

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

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

' Get folder name to process off the command line, make sure it's valid
If (WScript.Arguments.Count > 0) Then
    strFolder = Wscript.Arguments(0)
    If Not objFSO.FolderExists(strFolder) Then
        WScript.StdErr.WriteLine "Specified folder does not exist."
        WScript.Quit
    End If
Else
    WScript.StdErr.WriteLine "No folder name specified to process."
    WScript.Quit
End If

' 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) 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, False, True)
        objWorkbook.SaveAs Replace(strPath, strCSV, strExcel), xlOpenXMLWorkbook
        objWorkbook.Close False
        Set objWorkbook = Nothing
    End If
Next

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

Open in new window


»bp
1
 
Roy CoxGroup Finance ManagerCommented:
I'm short of time too and I haven't tested it, but this is Excel VBA. Save in a Standard module in Excel

Option Explicit

Public Sub UpdateCSV()

    Dim fso, sFolder, sFil
    On Error GoTo exit_proc
    Application.ScreenUpdating = False
    Set fso = CreateObject("Scripting.FileSystemObject")

    ''///Change this folder to the one you use.
    Set sFolder = fso.getFolder("C:\Users\Roy Cox\Documents\Shred")

    For Each sFil In sFolder.Files
        If (sFil.Name Like "*.csv") Then
            Workbooks.OpenText Filename:=sFil, _
                               DataType:=xlDelimited, Comma:=True, Local:=True
            sFil.SaveAs sFolder & Application.PathSeparator & sFil & ".xlsx", FileFormat:=51
            sFil.Close True
        End If
    Next sFil
exit_proc:
    Application.ScreenUpdating = True
End Sub

Open in new window

0
 
Eitel DagninIT Security AdministratorAuthor Commented:
Hi Bill,

Thank you for your response, this is what is currently happening:

1) I copied the code above and saved it as csv.vbs
2) I went to CMD and typed in cscript csv.vbs "C:\Users\Eitel\Desktop\3rd Party\Work Folder" - This is the path where the csv files are
3) I received this error: Input Error: Can not find script file "C:\Users\Eitel\csv.vbs"
4) I went to "C:\Users\Eitel\csv.vbs" and pasted the csv.vbs file in this location
5) I ran the command again and this is what was displayed:

  1. "C:\Users\Eitel\Desktop\3rd Party\Work Folder\TestFile.CSV"
  1. C:\Users\Eitel\csv.vbs(44.9) Microsoft Excel: Cannot save as that name. Document was opened as read-only.

I am quite new to scripting and VBA, and I am not the one going to be making use of this script in the future, so would you be able to assist me in making this a "clickable" script rather than requiring someone to open CMD and type in a command? Or perhaps advise me how I can achieve this please?

Thank you.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Eitel DagninIT Security AdministratorAuthor Commented:
Hi Roy,

Thank you for your response, this is what is currently happening:

1) I copied the code above and saved it as csv.vbs
2) I run the script and receive the following error:

  1. Line 6: Syntax Error
This line is the On Error GoTo exit_proc

3) I comment out Line 6 and run the code again - this time I receive the following error:

  1. Line 15: Expected Statement
This line is the Workbooks.OpenText Filename:=sFil, _
0
 
Roy CoxGroup Finance ManagerCommented:
You need Bill's code for VBS, my code is to run from Excel VBA
0
 
Bill PrewCommented:
You either need to CD to the folder where the VBS script is, or specify its fully qualified path on that command line, enclosed in double quotes if there are spaces in it.

~bp
0
 
Bill PrewCommented:
Did you get this working?


»bp
0
 
Eitel DagninIT Security AdministratorAuthor Commented:
Hi Bill,

No I have not yet got this working. I apologize for not updating the thread as of yet, was planning to do so once I had a solution.

I have 3 issues:

1) Using CMD to execute the code wouldn't be practical, because it wont be me using it. I am trying to "automate" as much of the workload we have and the other users are not as technically inclined as what I am. Therefore, the easiest (I think - but could be wrong) way of doing this is by having the string path hard coded in the script for execution. That way the user only needs to click on the script file and it will execute.

2) I noticed that while most of the files are csv extensions, some of the files extensions are displayed as .CSV and some are .csv. I am wondering if this will affect the way in which the script is executed?

3) The script above only includes csv extension and not xls. I presume that for me to include xls extension, I would need to have a second IF Statement within the For Each loop?

I have not yet made any progress since my last feedback as I have been working on the rest of this task by automating some of the excel tasks that need to be completed.

I also have this code which works perfectly, however, it's not very dynamic as it literally needs to look for specific file names. I was hoping to shorten this code and make it more dynamic to be able to simply scan the directory based on file type (rather than file name) then perform the task.

Const xlDelimited                =  1
Const xlTextQualifierDoubleQuote =  1
Const xlOpenXMLWorkbook          = 51

Set xl = CreateObject("Excel.Application")

xl.Workbooks.OpenText "C:\Users\xy56079\Desktop\Terminations\Daily Terminations Non HR.csv", , , xlDelimited _
  , xlTextQualifierDoubleQuote, True, False, False, True, False, False, _
  , Array(Array(1,2), Array(2,2), Array(3,2), Array(4,1), Array(5,2) _
  , Array(6,1), Array(7,1), Array(8,1), Array(9,1), Array(10,1), Array(11,1))
Set wb = xl.ActiveWorkbook

wb.SaveAs "C:\Users\xy56079\Desktop\Terminations\Daily Terminations Non HR.xlsx", xlOpenXMLWorkbook, , , , False
wb.Close

xl.Workbooks.OpenText "C:\Users\xy56079\Desktop\Terminations\Daily Terminations SP TOOL", , , xlDelimited _
  , xlTextQualifierDoubleQuote, True, False, False, True, False, False, _
  , Array(Array(1,2), Array(2,2), Array(3,2), Array(4,1), Array(5,2) _
  , Array(6,1), Array(7,1), Array(8,1), Array(9,1), Array(10,1), Array(11,1))
Set wb = xl.ActiveWorkbook

wb.SaveAs "C:\Users\xy56079\Desktop\Terminations\Daily Terminations SP TOOL.xlsx", xlOpenXMLWorkbook, , , , False
wb.Close

xl.Workbooks.OpenText "C:\Users\xy56079\Desktop\Terminations\Daily Terminations.csv", , , xlDelimited _
  , xlTextQualifierDoubleQuote, True, False, False, True, False, False, _
  , Array(Array(1,2), Array(2,2), Array(3,2), Array(4,1), Array(5,2) _
  , Array(6,1), Array(7,1), Array(8,1), Array(9,1), Array(10,1), Array(11,1))
Set wb = xl.ActiveWorkbook

wb.SaveAs "C:\Users\xy56079\Desktop\Terminations\Daily Terminations.xlsx", xlOpenXMLWorkbook, , , , False
wb.Close

xl.Quit

Open in new window


And I am aware it's not good programming practice to have 3 IF Statements doing the same job, I just haven't had the chance to try create a piece of code that will loop through it.
0
 
Bill PrewCommented:
With regard to your "three issues" above:

1) Using CMD to execute the code wouldn't be practical, because it wont be me using it. I am trying to "automate" as much of the workload we have and the other users are not as technically inclined as what I am. Therefore, the easiest (I think - but could be wrong) way of doing this is by having the string path hard coded in the script for execution. That way the user only needs to click on the script file and it will execute.

Sure, just replace this code:

' Get folder name to process off the command line, make sure it's valid
If (WScript.Arguments.Count > 0) Then
    strFolder = Wscript.Arguments(0)
    If Not objFSO.FolderExists(strFolder) Then
        WScript.StdErr.WriteLine "Specified folder does not exist."
        WScript.Quit
    End If
Else
    WScript.StdErr.WriteLine "No folder name specified to process."
    WScript.Quit
End If

Open in new window

With this code (assuming you know the folder will exist):

strFolder = "c:\yourfolderpath"

Open in new window

2) I noticed that while most of the files are csv extensions, some of the files extensions are displayed as .CSV and some are .csv. I am wondering if this will affect the way in which the script is executed?

No, that will not be a problem, all .csv files will be processed, case insentive of extension.  Notice the following line that uses LCase() to effectively do a case insensitive compare of the two extensions.

    If LCase(objFSO.GetExtensionName(strPath)) = LCase(strCSV) Then

Open in new window

3) The script above only includes csv extension and not xls. I presume that for me to include xls extension, I would need to have a second IF Statement within the For Each loop?

No, you could do this with a single IF statement, with a compound condition, such as (make sure you define strXLS above as well):

    If LCase(objFSO.GetExtensionName(strPath)) = LCase(strCSV) Or LCase(objFSO.GetExtensionName(strPath)) = LCase(strXLS) Then

Open in new window


»bp
0
 
Bill PrewCommented:
I also have this code which works perfectly, however, it's not very dynamic as it literally needs to look for specific file names. I was hoping to shorten this code and make it more dynamic to be able to simply scan the directory based on file type (rather than file name) then perform the task.

I don't think I understand this.  Based on the code I provided it doesn't need the full paths for each file.  Or this a different effort?  A little confused, sorry...


»bp
0
 
Eitel DagninIT Security AdministratorAuthor Commented:
Hi Bill,

Thank you for your comments. I had incidentally made the changes you suggested above on Tuesday shortly after I read your reply.. I took a bit of time to read the code a little and understand what is going on.

Here is the code I put together (before your latest reply):


' Require variables to be defined
Option Explicit

' Global variables
Dim strBaseFolder
Dim strDestFolder
Dim objFSO		
Dim objFolder
Dim objFile

' Define folders to work with
strBaseFolder = "C:\Users\Desktop\3rd Party"
strDestFolder = "C:\Users\Desktop\3rd Party\Work Folder"

' Create filesystem object
Set objFSO = CreateObject("Scripting.FileSystemObject")

' Exit if base folder does not exist
If Not objFSO.FolderExists(strBaseFolder) Then
    Wscript.Echo "Missing base folder : """ & strBaseFolder & """"
    Wscript.Quit
End If

' Exit if dest folder does not exist
If Not objFSO.FolderExists(strDestFolder) Then
    Wscript.Echo "Missing dest folder : """ & strDestFolder & """"
    Wscript.Quit
End If

' Look at each subfolder of base folder
For Each objFolder In objFSO.GetFolder(strBaseFolder).SubFolders
    
        For Each objFile In objFolder.Files
            ' Continue if we want this file
            If IncludeFile(objFile) Then
                
            End If
        Next
Next

' Logic to determine if we process a file
Function IncludeFile(objFile)
    
	IncludeFile = False
    Select Case LCase(objFSO.GetExtensionName(objFile.Path))
        
		' Include only these extensions
        Case "csv", "xls", "xlsx"
		
    End Select
	
End Function

Open in new window


The above code runs but nothing happens - no errors and no converting of files.

Below is your code, with the amendments made:

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

' Get folder name to process off the command line, make sure it's valid
strFolder = "C:\Users\Desktop\3rd Party\Work Folder"

' 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, False, True)
        objWorkbook.SaveAs Replace(strPath, strCSV, strExcel), xlOpenXMLWorkbook
        objWorkbook.Close False
        Set objWorkbook = Nothing
    End If
Next

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

Open in new window


This code executes perfectly, however, I get an error at Line 36:

Cannot save file as that name. Document was opened as read-only.

With regards to your comment below:

I don't think I understand this.  Based on the code I provided it doesn't need the full paths for each file.  Or this a different effort?  A little confused, sorry...

Apologies for the confusion. The code I provided in my last comment was code I was making use of before I came here to look for help. So in actual fact, once I have a working solution for my original post (converting multiple csv files) I will be able to use it anywhere.

I only posted that code to show the logic. But its of no real importance right now..
0
 
Eitel DagninIT Security AdministratorAuthor Commented:
Hi Bill,

Just an update. I made an amendment in this line:

Set objWorkbook = objExcel.Workbooks.Open(strPath, False, True)

Open in new window


I changed, the "True" to "False" and it runs. However, it only converts the csv files with the lower case csv extension and excludes the upper case csv extension files as well as the xls files.
0
 
Eitel DagninIT Security AdministratorAuthor Commented:
The above code runs but nothing happens - no errors and no converting of files.

I realized now that it wont do anything because it has no code to do anything. Sorry about that,
0
 
Bill PrewCommented:
Okay, a couple of small tweaks to my code, based on the changes you have needed.  It was originally designed to only handle one extension so adding the XLS files also has caused some issues.  I missed one part of that and have corrected below.  Also notice that the FALSE was correct in the OPEN, we want that, so I have that back the way it was originally.

This code worked here on CSV and XLS files when I tested it.  A couple of thoughts though.  First, be aware that XLS files can contain macros, and this code will likely fail for those cases, since in the newer format there are XLSX files (no macros) and XLSM files (support macros).  Second, I don't think you want to include XLSX files as "input" to this (as you did in your CASE statement), there is no need to convert an XLSX file to itself, right?

'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 = "B:\EE\EE29088597\Files"

' 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, False, True)
        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

Open in new window


»bp
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
Eitel DagninIT Security AdministratorAuthor Commented:
Hi Bill,

Thank you very much for your continuous help! This is exactly what I needed. Thank you again :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.