I Love This VBA for Multiple .xlsx Files Converted to .csv Files Except...

[bmst1972]

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

 

[Trebor76]

You are not going to believe what just happened! All of a sudden it finally worked! I can't explain it, but it worked...LOL!!!

Something must have changed but that's great it's finally working

Like MARK858 says you need to change the block of code I originally wrote to check for a trailing backslash i.e. \ not simply that it's not nothing ""

Robert

 

[bmst1972]

I clearly see your point. However, I don't know how I posted "'Add trailing backslash if user hasn't If Right(strMyPath, 1) <> "" Then
strMyPath = strMyPath & "" End If" because I used "'Add trailing backslash if user hasn't If Right(strMyPath, 1) <> "" Then
strMyPath = strMyPath & "" End If" before you sent me your two most recent responses. All I know is what I have is finally working. Therefore, I have no reason to touch any part of the code...LOL

 

[MARK858]

All I know is what I have is finally working. Therefore, I have no reason to touch any part of the code...LOL

The whole purpose of those lines of code Robert posted is to make sure that the address in the cell has the last backslash and if not to add it in.
If you aren't going to use it (i.e. you know you will always have the last backslash in the cell) then remove the lines totally rather that test for a nothing.

 

[bmst1972]

Hello...In preparation of the upcoming Pennsylvania state test(PSSA), my school principal recently sent me an Excel spreadsheet consisting of student names and their corresponding start dates for when they began the school year. The purpose of his spreadsheet is for his teachers to cross-reference his data with the teachers' data. I have already been using an Excel spreadsheet to take daily attendance for years. However, my students' start dates are not in the same column due to different late entries into my classes. I want to create a "Start Date" column that contains a formula that will return the first NON blank row cell from left to right(student row) with the corresponding date for each student. If you look to the far right, you will notice Student 1's start date is September 12(Cell P10 on my worksheet) while the other students were enrolled on the official start date of school which was September 6(Cell P11 on my worksheet). Any assistance you can provide will be greatly appreciated.

Robert

Student Name	Yearly Total	Yearly(P)	Yearly(A)	Yearly(T)	Yearly(H)	1st Q.(Total)	1st Q.(P)	1st Q.(A)	1st Q.(T)	1st Q.(H)	6-Sep	7-Sep	8-Sep	11-Sep	12-Sep
Student 1	128	120	8	0	0	39	36	3	0	0					P
Student 2	132	124	8	0	0	43	41	2	0	0	P	P	P	P	P
Student 3	132	114	18	0	0	43	34	9	0	0	P	P	P	P	P

<colgroup><col><col span="10"><col span="5"></colgroup><tbody>
</tbody>

 

[bmst1972]

I just realized I should have started a new thread with a different topic. I do apologize.