Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Change Multiple csv files into xlsx files

Status
Not open for further replies.
Eitel13

Eitel13

Programmer
Feb 1, 2018
54
ZA
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: 
Dim CSVfolder As String

Error:
Expected end of statement
Click to expand...

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: 
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]
 
Sort by date Sort by votes
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,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Hi Skip & combo,

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

Code: 
'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 :)
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Hi All,

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

Link:
Code: 
'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! :)
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

brunello67
  • Locked
  • Question
how to upload list of multiples files from txt/csv file with ftp
Replies
9
Views
173
strongm
strongm
Palito1916
  • Locked
  • Question
Disable multiple computer accounts using TXT file with computer names
Replies
2
Views
165
Palito1916
Palito1916
jahelka
  • Locked
  • Question
script editing - base64 for files in folder
Replies
1
Views
113
mikrom
mikrom
sugram
  • Locked
  • Question
check multiple files 1
Replies
12
Views
194
sugram
sugram
asel
  • Locked
  • Question
Copy cell data from excel and paste into portable software application
Replies
0
Views
120
asel
asel

Part and Inventory Search

Sponsor

Back
Top