Convert from CSV to XLSX and save with same file name

Question

I have a series of CSV files that come to me bundled in a folder simply named for the month. I've got code working to find them, open them, parse them and I'm having trouble saving them the way I want to. What I'm aiming at is saving as the same file name as it was just in the new and parsed format.

Sub OpenCSVs_2() Dim MyFiles As String, ThisMonth As String, Convert As String Dim startPath As String ThisMonth = Format(Date, "mmmm") startPath = "C:\Users\ME\Desktop\CSV find convert tests\" & ThisMonth & "\" MyFiles = Dir(startPath & "*.csv") Convert = Dir(startPath & "*xlsx") Do While MyFiles <> "" Workbooks.Open startPath & MyFiles Call Parse1 ActiveWorkbook.SaveAs Filename:="startPath & Convert", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False MyFiles = Dir '<----------------error happens here Loop End Sub

The above actually does something and creates an xlsm file names "startPath & Convert". I'm sure the solution is right in front of me.

While darnit isn't a major curse word, please try and keep a professional tone when writing posts. — CalvT
– CalvT, Commented Mar 23, 2017 at 14:02

Sorceri · Accepted Answer · 2017-03-23 13:51:58Z

As with my previous post, you are putting your variables in quotes which then turns it into a string. so first, remove the quotes on startPath & MyFiles, then just replace the extension using the function Replace. I also added the Workbook object as you should avoid using Activeworkbook as it can cause issues.

Sub OpenCSVs_2() Dim MyFiles As String, ThisMonth As String Dim startPath As String Dim wb As Workbook ThisMonth = Format(Date, "mmmm") startPath = "C:\Users\ME\Desktop\CSV find convert tests\" & ThisMonth & "\" MyFiles = Dir(startPath & "*.csv") Do While MyFiles <> "" Set wb = Workbooks.Open(startPath & MyFiles) Call Parse1 wb.SaveAs Filename:=startPath & Replace(MyFiles, ".csv", ".xlsx"), FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False MyFiles = Dir Loop End Sub

Ah... Replace is a predefined function. This works... thanks so much.

Collectives™ on Stack Overflow

Convert from CSV to XLSX and save with same file name

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related