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]

Hi Robert (cool name ),

Welcome to MrExcel!!

See how this goes:

Option Explicit
Sub ConvertToCSV()

    'The following has been adapted from here:
    'https://msdn.microsoft.com/en-us/vba/excel-vba/articles/application-filedialog-property-excel

    Dim lngCount As Long
    Dim objFSO   As Object
    
    Application.ScreenUpdating = False
    
    Set objFSO = CreateObject("Scripting.FileSystemObject")

    With Application.FileDialog(msoFileDialogOpen)
        .InitialFileName = "D:\Robert\USB Backup 18-Jan-2018\ROB'S USB\Excel\Samples\" 'Initial file path. Don't forget trailing backslash.
        'Add a custom filter to list Excel files only
        With .Filters
            .Clear
            .Add "Excel Files Only", "*.xls*"
        End With
        .AllowMultiSelect = True
        .Show
        'Loop through each file selected, converting them to a CSV format.
        '//Note that as a *.csv file can only have a single tab the sheet the *.xls*' workbook opens on will be the *.csv file//
        For lngCount = 1 To .SelectedItems.Count
            'Open Workbook
            Workbooks.Open Filename:=.SelectedItems(lngCount)
           'Turn alters off
            Application.DisplayAlerts = False
            'Save the active tab as a *.csv file
            ActiveWorkbook.SaveAs Filename:=objFSO.GetParentFolderName(.SelectedItems(lngCount)) & "\" & objFSO.GetBaseName(.SelectedItems(lngCount)) & ".csv", FileFormat:=xlCSV, CreateBackup:=False
            ActiveWorkbook.Close False
            Application.ScreenUpdating = True
        Next lngCount
    End With
    
    Set objFSO = Nothing
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[bmst1972]

Hi Robert...I truly appreciate your quick response, and you have validated the reason for your website being called "Mr. Excel". I received 3 replies regarding my problem, and you are the only one to give me the perfect solution! I will probably no longer bother visiting any other website for Excel assistance.

 

[Trebor76]

you have validated the reason for your website being called "Mr. Excel"

No, Bill Jelen is the owner of this great site. I'm just a contributor.

I received 3 replies regarding my problem, and you are the only one to give me the perfect solution! I will probably no longer bother visiting any other website for Excel assistance

I'm glad we were able to provide a solution for you. Note if you have posted the same question on other forums you must provide a link to each. Failure to do so is in breach of MrExcel's cross-posting rules.

Thanks,

Robert

 

[bmst1972]

I didn't mean any disrespect as I was not aware of "cross posting". I should have read Mr. Excel's website rules more thoroughly.

No, Bill Jelen is the owner of this great site. I'm just a contributor.



I'm glad we were able to provide a solution for you. Note if you have posted the same question on other forums you must provide a link to each. Failure to do so is in breach of MrExcel's cross-posting rules.

Thanks,

Robert

 

[Trebor76]

NP. If you can provide the links to the other site(s) as per Rule 13 of our forum rules (refer here) it would be appreciated. I'd say you'd probably have to do the same on their sites too

Thanks,

Robert

 

[bmst1972]

NP. If you can provide the links to the other site(s) as per Rule 13 of our forum rules (refer here) it would be appreciated. I'd say you'd probably have to do the same on their sites too

Thanks,

Robert

Rob...I made a mistake. I thought it was two different people, but it was actually only one guy who responded twice. Here is the link: https://answers.microsoft.com/en-us...essageId=35bd9d08-c169-4325-95c3-379924b524d4

By the way, I am also trying to figure out how to permanently link two workbooks together. I only have an issue when I transfer my files to and from my school computer. Why can't excel automatically update the vales of the target workbook since it displays the source workbook file name on the left side of "Edit Links" window???

Robert(The Schoolteacher from Scranton, PA)

 

[Trebor76]

Hi Robert,

You need to use the Change Source option on the Edit Links dialog as Update Values does just that - updates (refreshes) the values that are already linked (if you have set the calculation method to manual for example). You could use Edit > Replace (Ctrl + H) to also replace the links.

HTH

Robert

 

[bmst1972]

Hi Robert,

You need to use the Change Source option on the Edit Links dialog as Update Values does just that - updates (refreshes) the values that are already linked (if you have set the calculation method to manual for example). You could use Edit > Replace (Ctrl + H) to also replace the links.

HTH

Robert

Hi Again Rob(The MS Excel Expert)...I have another request due to the fact I have linked workbooks that constantly have to be semi-manually updated with the back and forth transfer of files between my home computer and my school computer. Whatever I am doing or not doing is causing the links to become severed, and I have to help Excel find my source files with each data transfer between computers. Therefore, I have decided to take another route. My new goal is to find a macro that will automate the process of converting multiple Excel worksheets within the same workbook to CSV files with the CSV files having the same exact names as their corresponding XLSX worksheets. I have been creating, using and transferring data that contains linked worksheets within the same workbook for years. I simply need your assistance with the conversion process of the Excel worksheets to CSV files. I also 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?". Any assistance you can provide will be greatly appreciated.


Robert(The Schoolteacher)

 

[Trebor76]

Rob(The MS Excel Expert)

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

Whatever I am doing or not doing is causing the links to become severed

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.

My new goal is to find a macro that will automate the process of converting multiple Excel worksheets within the same workbook to CSV files with the CSV files having the same exact names as their corresponding XLSX worksheets.

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