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

 

[bmst1972]

Ha - far from it. I'm just another Excel user user



At a guess I'd say that the files are being saved on a school server which obviously doesn't exist on your home computer.



Try this:

Option Explicit
Sub SaveEachTabAsCSV()

    Dim strMyPath    As String
    Dim wsMySheet    As Worksheet
    Dim intFileCount As Integer
    
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With
    
    strMyPath = "C:\CSV File Test\" 'Path to save the individual tabs as CSV files.  Change to suit but don't forget trailing backslash!!
    'Add trailing backslash if user hasn't
    If Right(strMyPath, 1) <> "\" Then
        strMyPath = strMyPath & "\"
    End If
    
    For Each wsMySheet In ThisWorkbook.Sheets
        intFileCount = intFileCount + 1
        wsMySheet.Copy
        ActiveWorkbook.SaveAs Filename:=strMyPath & wsMySheet.Name & ".csv", FileFormat:=xlCSV
        ActiveWorkbook.Close
    Next wsMySheet
    
    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
    
    MsgBox intFileCount & " CSV file(s) have now been saved in the """ & strMyPath & """ directory.", vbInformation

End Sub

Regards,

Robert

Robert,

I don't save my files on a school server. I simply save them on my school computer's desktop until I have permission from my school district's IT people to begin using Google Drive as a backup source. In the meantime, I received the following error message from running your most recent macro script: Run-time error '1004': Method 'SaveAs' of object'_Workbook' failed. Furthermore, the debugger highlighted the following section of the script but I have no idea how to fix it: ActiveWorkbook.SaveAs Filename:=strMyPath & wsMySheet.Name & ".csv", FileFormat:=xlCSV​.

 

[Trebor76]

I received the following error message from running your most recent macro script: Run-time error '1004': Method 'SaveAs' of object'_Workbook' failed.

There are two reasons I think of why this error has occurred...

• The directory path held the in the strMyPath doesn't exist or
• One or more of the tabs in the workbook are hidden so they can't be copied out of it to be made a CSV file from

...the following addresses both of these (note hidden tabs are ignored):

Option Explicit
Sub SaveEachTabAsCSV()

    Dim strMyPath    As String
    Dim wsMySheet    As Worksheet
    Dim intFileCount As Integer
    
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With
    
    strMyPath = "U:\CSV File Test\" 'Path to save the individual tabs as CSV files.  Change to suit but don't forget trailing backslash!!
    'Add trailing backslash if user hasn't
    If Right(strMyPath, 1) <> "\" Then
        strMyPath = strMyPath & "\"
    End If
    'Ensure the 'strMyPath' directory exists
    If Dir(strMyPath, vbDirectory) = "" Then
        MsgBox "The path """ & strMyPath & """ doesn't exist!!" & vbNewLine & "Please check it and try again.", vbCritical
        Exit Sub
    End If
    
    For Each wsMySheet In ThisWorkbook.Sheets
        If wsMySheet.Visible = xlSheetVisible Then
            intFileCount = intFileCount + 1
            wsMySheet.Copy
            ActiveWorkbook.SaveAs Filename:=strMyPath & wsMySheet.Name & ".csv", FileFormat:=xlCSV
            ActiveWorkbook.Close
        End If
    Next wsMySheet
    
    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
    
    MsgBox intFileCount & " CSV file(s) have now been saved in the """ & strMyPath & """ directory.", vbInformation

End Sub

Regards,

Robert

 

[bmst1972]

Rob...Your most recent script worked perfectly. However, I had to modify the target path section of the code. I believe it is the following: "U:\CSV File Test" I don't want to modify the target path of the VBA everytime I transfer data from my school computer to my home computer and vice versa. I am almost positive I wouldn't have this problem provided the folder paths for my school and home computers are identical. I want the target CSV files to be placed in a subfolder of the source files folder called "eschool grades". Can you insert a line of code that will tell Excel to make a multi-selection file dialogue box in place of what I highlighted in the seond line of this message. After I run the new script, I will assign "eschool grades" as the Excel default folder on both of my computers.

 

[Trebor76]

Hi Robert,

You need to change the strMyPath variable to a path where you want the CSV files to be saved. The "U:\CSV File Test" was a directory I made to test the code and why I put a comment next to that line for it to be changed.

You could have the directory in a cell that the code references if you like to make it easy like so...

strMyPath = Sheets("Sheet1").Range("A2")

...where the directory path was in cell A2 of Sheet1 (change to suit) of your workbook.

Code could also be written that if one directory is not found (school computer) a second is used (home computer) and vice versa or you choose the path via a dialog box (similar to the Save As dialog box) each time.

Regards,

Robert

 

[bmst1972]

Hi Robert,

You need to change the strMyPath variable to a path where you want the CSV files to be saved. The "U:\CSV File Test" was a directory I made to test the code and why I put a comment next to that line for it to be changed.

You could have the directory in a cell that the code references if you like to make it easy like so...

strMyPath = Sheets("Sheet1").Range("A2")

...where the directory path was in cell A2 of Sheet1 (change to suit) of your workbook.

Code could also be written that if one directory is not found (school computer) a second is used (home computer) and vice versa or you choose the path via a dialog box (similar to the Save As dialog box) each time.

Regards,

Robert

Rob,

I believe I am extremely close to achieving glory. Don't forget I am a novice when it comes to reading and writing macros. I placed the following directory path in Cell A2 of Sheet1: C:\Users\Owner\Desktop\nsis\nsis 2017-2018\grades(online)\eschool grades data\eschool grades\. As a result I received the following message Compile error: Variable not defined.

(1) Please take a look at the following most recent code you sent me to determine whether or not I made an error with the Bold part of the script. (2) Please let me know specifically how to enter the directory path into Cell A2 of Sheet1

Option Explicit
Sub SaveEachTabAsCSV()


Dim strMyPath As String
Dim wsMySheet As Worksheet
Dim intFileCount As Integer

With Application
.ScreenUpdating = False
.DisplayAlerts = False
End With

strMyPath = Sheets("Sheet1").Range("A2") 'Path to save the individual tabs as CSV files. Change to suit


but don't forget trailing backslash!!
'Add trailing backslash if user hasn't
If Right(strMyPath, 1) <> "" Then
strMyPath = strMyPath & ""
End If
'Ensure the 'strMyPath' directory exists
If Dir(strMyPath, vbDirectory) = "" Then
MsgBox "The path """ & strMyPath & """ doesn't exist!!" & vbNewLine & "Please check it and try


again.", vbCritical
Exit Sub
End If

For Each wsMySheet In ThisWorkbook.Sheets
If wsMySheet.Visible = xlSheetVisible Then
intFileCount = intFileCount + 1
wsMySheet.Copy
ActiveWorkbook.SaveAs Filename:=strMyPath & wsMySheet.Name & ".csv", FileFormat:=xlCSV
ActiveWorkbook.Close
End If
Next wsMySheet

With Application
.DisplayAlerts = True
.ScreenUpdating = True
End With

MsgBox intFileCount & " CSV file(s) have now been saved in the """ & strMyPath & """ directory.",


vbInformation


End Sub

 

[MARK858]

Oops sorry you already have it. Are you sure it is the line you have bolded giving that error?

Can you also please repost your code but this time using code tags as there are compile errors when I copy and paste it.

To put code tags just paste your code in the thread, select the code and then click the # icon.

 

[bmst1972]

I don't think the bold part is causing me the error based on the simple fact you gave it to me. However, the debugger highlighted the following: Sub SaveEachTabAsCSV()

 

[MARK858]

As I have stated please repost the code you are using in code tags then I will see if I can see the issue.

Btw, why have you changed the lines below in the code Trevor posted

    'Add trailing backslash if user hasn't
    If Right(strMyPath, 1) <> "\" Then
        strMyPath = strMyPath & "\"
    End If

to the below?

'Add trailing backslash if user hasn't
 If Right(strMyPath, 1) <> "" Then
 strMyPath = strMyPath & ""
 End If

 

[bmst1972]

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!!!

 

[MARK858]

Happy you have it sorted but you still need to put the lines I posted back to how Trevor had them