×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Change Multiple csv files into xlsx files

Change Multiple csv files into xlsx files

Change Multiple csv files into xlsx files

(OP)
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

CODE --> vbs

Dim CSVfolder As String 


Error:

Quote:

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

CODE --> vbs

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 

Thank you in advance bigsmile

RE: Change Multiple csv files into xlsx files

Hi,

If this is VB Script, then you don’t have a Workbook variable type, until you 1) CreateObject() to create an Excel Application Object and the 2) use the Excel Application Object to open or add an [Excel Application Object].Workbook object.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Change Multiple csv files into xlsx files

Additionally, in VBS:
- all variables are variant,
- there is no named arguments handling, you need to pass arguments in the order from method definition.

combo

RE: Change Multiple csv files into xlsx files

(OP)
Hi Skip & combo,

Thank you both for your replies. I managed to get the following piece of code put together:

CODE --> vbs

'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 

Unfortunately I have 4 issues:

1) I was instructed to run this in the following manner:
Copy the code above and saved it as csv.vbs
Go to CMD and type in cscript csv.vbs "C:\Users\Eitel\Desktop\3rd Party\Work Folder" - This is the path where the csv files are

I would prefer to have a way of executing the code by clicking on/opening a vbscript - If someone can help achieve this, that would be great. bigsmile

2) I received this error: Input Error: Can not find script file "C:\Users\Eitel\csv.vbs"
I went to "C:\Users\Eitel\csv.vbs" and pasted the csv.vbs file in this location
I ran the command again and this is what was displayed:

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

I have no clue what this means or why it happens?

3) 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?

4) 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?

Thank you again for the help :)

RE: Change Multiple csv files into xlsx files

You may have problem with capitalised extension. "csv" is not found in file full path, so not replaced by "xlsx". To find all cases, you may use Split function with "." delimiter for file name, test the last item of the array, if LCase of it = strCSV, replace by strExcel and combine again with Join function and the same "." delimiter.
You may also consider that if you don't specify extension of output file name, excel adds it according to specified file format.

combo

RE: Change Multiple csv files into xlsx files

(OP)
Hi All,

I have the solution I was looking for. Code below:

Link: https://www.experts-exchange.com/questions/2908859...

CODE --> vbs

'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 

Thank you all for the help! :)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close