Hi...My name is Robert, and I am a schoolteacher who is taking a dip in the Excel VBA pool. I consider myself to be very knowledgeable of using spreadsheet formulas, but I am definitely an amateur when it comes to coding. I am trying to control my frustration of not being able to find the correct code or being capable of modifying code to satisfy my needs. My goal is to find a macro that will automate the process of converting multiple .xlsx files(exactly 20 files) to .csv files with the CSV files having the same exact names as their corresponding XLSX files. Furthermore, I want new CSV files to overwrite existing CSV files without having to deal with the following annoying message: "A file named _____ already exists in this location. Do you want to replace it?". All of the XLSX files and CSV files are contained in the same folder. I provided you with the closest script I found on the Internet below this message. Although I modified it to allow for the selection of multiple files, the macro only returns one CSV file out of the 20 XLSX files that are located in the folder. I have a feeling the problem has something to do with the part of the script I typed in red. I tried changing .SelectedItems(1) to .SelectedItems(20), but the macro selects the 20th position of the file dialogue window rather than selecting all 20 Excel .xlsx files. Any assistance you can provide will be greatly appreciated.
Sub ConvertToCSV()
Dim myPath As String
Dim myString As Variant
'Turn off Any Alerts
Application.DisplayAlerts = False
'Open Workbook
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = True
.Show
myPath = .SelectedItems(1)
End With
'Open Workbook
Workbooks.Open Filename:=myPath
'Remove Excel Extension from String
myString = Split(myPath, ".")
myPath = myString(0)
'Save as CVS
ActiveWorkbook.SaveAs Filename:=myPath & " .csv", FileFormat:=xlCSV, CreateBackup:=False
'Close Window
ActiveWindow.Close
'Turn on Alerts
Application.DisplayAlerts = True
End Sub
Sub ConvertToCSV()
Dim myPath As String
Dim myString As Variant
'Turn off Any Alerts
Application.DisplayAlerts = False
'Open Workbook
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = True
.Show
myPath = .SelectedItems(1)
End With
'Open Workbook
Workbooks.Open Filename:=myPath
'Remove Excel Extension from String
myString = Split(myPath, ".")
myPath = myString(0)
'Save as CVS
ActiveWorkbook.SaveAs Filename:=myPath & " .csv", FileFormat:=xlCSV, CreateBackup:=False
'Close Window
ActiveWindow.Close
'Turn on Alerts
Application.DisplayAlerts = True
End Sub
