CODEWITHSUNDEEP
vbaexcelcsv
battery514
battery514

Reputation: 249

Excel VBA Convert .csv to Excel File

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

Upvotes: 4

Views: 9430

Answers (1)

user4039065
user4039065

Reputation:

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.

Upvotes: 2

Related Questions