CSV to XLSX conversion

[Duckbill32]

Hi everyone.
I am trying to come up with solution to convert .csv file to .xlsx. Although there are many topics regarding that, I am unable to find anything suitable for me and I am not that experienced in VBA to write the script myself, yet ;(

I would like to create an ActiveX Control button with VBA code, which would:
1. Ask in the pop-up window to select the folder
2. Find all the .csv documents in the folder among .xlsx and other documents
3. Convert all those .csv documents into .xlsx and save them with the same name in the same folder without any pop-up windows
4. Delete all the .csv from the same folder

Looking forward to hearing back from VBA gurus!
Thanks!

 

[breynolds0431]

Hello. The below works for me when testing. Proceed with caution since the code will be deleting files. Should go without saying, but make some test folders for testing.

Private Sub CommandButton1_Click()

'declares file picker as a variable
Dim fPicker As FileDialog: Set fPicker = Application.FileDialog(msoFileDialogFolderPicker)

With fPicker
    .AllowMultiSelect = False
    .Title = "Select the Folder with CSV Files"
    .Show
    If .SelectedItems.Count = 0 Then Exit Sub
End With

'Optimize processing
With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
End With

'error handling
On Error GoTo ResetSettings

'set up loop through iPath
Dim iFolder As String: iFolder = fPicker.SelectedItems(1) & "\"
Dim iExt As String: iExt = "*.csv*"
Dim iFile As String: iFile = Dir(iFolder & iExt)
Dim iWB As Workbook
Dim bName As String

'loop through each excel file in iPath
Do While iFile <> ""
    'sets csv file as variable while opening
    Set iWB = Workbooks.Open(FileName:=iFolder & iFile, ReadOnly:=True)
    DoEvents
    'gets base filename for xlsx SaveAs
    bName = CreateObject("Scripting.FileSystemObject").GetBaseName(iFile)
    
    'turns alerts off
    Application.DisplayAlerts = False
    'xlsx SaveAs
    iWB.SaveAs iFolder & bName & ".xlsx", FileFormat:=xlOpenXMLWorkbook
    'closes newly converted xlsx file
    ActiveWorkbook.Close savechanges:=False
    'deletes the csv file
    Kill iFolder & iFile
    'turns alerts back on
    Application.DisplayAlerts = True

    DoEvents
    iFile = Dir
Loop

'Restores settings
With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .Calculation = xlCalculationAutomatic
End With

'Task completion message
MsgBox "The task has completed.", vbInformation, "Task Completion"

Exit Sub

'Error Handling
ResetSettings:
MsgBox "The below error has occurred: " & vbCrLf & vbCrLf & "Error Number:" & Err.Number & vbCrLf & _
    "Error Description: " & Err.Description
With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .Calculation = xlCalculationAutomatic
End With
End Sub

 

[Duckbill32]

OMG, thank you so much for such a fast reply and such a smooth solution! It works wonderfully!

 

[breynolds0431]

Glad I could help