Excel VBA Convert .csv to Excel File

Question

I have a folder which has .csv files, .xls files, and xlsx files. The below code is a portion of an overall project (when I remove the below code, the remaining code achieves what I want). A large chunk of the code was compiled from somewhere (here and around the internet). What I want the code to do is open only the .csv files in the folder, convert them to an Excel file, close the files, and then delete the .csv files in the folder. What ends up happening with the code is that one or both of the files created by the code are deleted from the folder, and I am left with nothing. Thanks in advance for any help.

Sub Test()
'
' Test Macro
'
'Set variables for the below loop
Dim MyFolder As String
Dim MyFile As String
Dim GetBook As String
Dim GetBook2 As String
Dim MyCSVFile As String
Dim KillFile As String
MyFolder = "REDACTED"
MyFile = Dir(MyFolder & "\*.xls")
MyCSVFile = Dir(MyFolder & "\*.csv")

'Open all of the .csv files in the folder and convert to .xls
Do While MyCSVFile <> ""
    Workbooks.Open Filename:=MyFolder & "\" & MyCSVFile
    GetBook = ActiveWorkbook.Name
    GetBook2 = Left(GetBook, Len(GetBook) - 4)
    ActiveSheet.Name = "Sheet1"
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:=GetBook2, FileFormat:=56
    ActiveWorkbook.Close False
    Kill MyFolder & "\" & GetBook
Loop

End Sub

Why are you saving as xlExcel8 (e.g. FileFormat:=56)? Is there some restriction against an .XLSX (e.g. xlOpenXMLWorkbook or FileFormat:=51)? — user4039065
– user4039065, Commented Sep 2, 2015 at 18:05
I may be wrong but wouldn't you want {...Filename: = myfolder & "\" & GetBook2 & ".xls" ...} — Scott Craner
– Scott Craner, Commented Sep 2, 2015 at 18:11

score 2 · Accepted Answer · 2015-09-02 19:35:42Z

2

You are not calling the Dir function to get the next file.

Sub Test()
    'Set variables for the below loop
    Dim myFolder As String
    Dim getBook As String
    Dim myCSVFile As String

    Application.DisplayAlerts = False

    myFolder = Environ("TEMP") & Chr(92) & "REDACTED"

    myCSVFile = Dir(myFolder & "\*.csv")

    Do While myCSVFile <> ""
        Workbooks.Open Filename:=myFolder & "\" & myCSVFile
        getBook = ActiveSheet.Name  '<~ Sheet1 of an opened CSV is the name of the CSV
        ActiveSheet.Name = "Sheet1"
        ActiveWorkbook.SaveAs Filename:=myFolder & Chr(92) & getBook, FileFormat:=56
        ActiveWorkbook.Close False
        Kill myFolder & Chr(92) & myCSVFile  '<~~ delete the CSV, not the workbook
        myCSVFile = Dir   '<~~ this is important to get the next file in the folder listing
    Loop

End Sub

The only worksheet in an opened CSV is named for the CSV (without the .CSV extension) so that can be used in the Workbook.SaveAs method. I've used xlOpenXMLWorkbook as the SaveAs FileFormat type.

edited Sep 2, 2015 at 19:35

answered Sep 2, 2015 at 18:16

user4039065

Sign up to request clarification or add additional context in comments.

5 Comments

battery514 Over a year ago

Thanks for the quick reply! However, it appears as though the code is not creating the Excel file, and is also deleting the .CSV file. The code executed, and I ended up with no files. Upon removing the "Kill" line and re-running, the folder just contains the two .csv files. It appears as though it's not even saving an Excel file. Thoughts? Thanks again.

user4039065 Over a year ago

The MyFolder = "REDACTED" is not a complete path to start with. It is relative to the current directory (not necessarily the directory of the active workbook). Is it intended to be a subfolder of the active workbook's path?

battery514 Over a year ago

No reason as to why .xls and not .xlsx. Here is a rough outline of the path, which the names redacted: MyFolder = "L:\Folder\Sub-Folder\Sub-Folder"

user4039065 Over a year ago

I've chopped out all of the unnecessary code and left only what is absolutely needed to perform the task you have described. Edit and change Environ("TEMP") & Chr(92) & "REDACTED" to L:\Folder\Sub-Folder\Sub-Folder. This was tested and performs as expected on a subfolder of my %TEMP% folder.

battery514 Over a year ago

That's awesome. Thanks!

Collectives™ on Stack Overflow

Excel VBA Convert .csv to Excel File

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related