Make a copy of an Excel file as a CSV without having Excel "re-open" the file as a CSV

Question

I have several dozen Microsoft Excel files in xlsx format. These files are the "master" copy for editing and include various formatting to make them easier to edit.

However, the data from these files is imported into a CMS as CSV, so ultimately I need to generate CSV files.

To generate the CSV files, this is what I do currently.

1. Click File in the Ribbon.
2. Click Save as.
3. Select CSV UTF-8.
4. Click Save.

However, when I do this, Excel automatically closes the xlsx file and opens the new CSV UTF-8 file. This is a problem because the formatting is still there, so it looks like the xlsx file, and sometimes I make additional changes to the CSV file, when the changes need to be made to the master file.

For now, I have made it a habit to very carefully close the CSV after export and then re-open the xlsx file to prevent this kind of mistake.

How can I generate a CSV file from an xlsx file without having Excel close the xlsx file and open the CSV file?

EDIT

The xlsx files are stored in Google Drive, and sometimes users update them via Google Sheets. So I can't convert to xlsm because then the files will no longer be editable in Sheets.

Answer 1

There is no way to change the behavior of Excel without VBA or an add-in.

The following add-ins might be able to help:

• XLTools (commercial)
• excel-csv-import
• CSV Import+Export

But perhaps it's better to avoid doing the Save As in Excel and do it rather with a PowerShell script. This way you would also be able to save multiple Excel worksheets with one script.

Here is an example PowerShell function (totally untested) :

Function ExcelToCsv ($File) {
    $myDir = "D:\Excel"
    $excelFile = "$myDir\" + $File + ".xlsx"
    $Excel = New-Object -ComObject Excel.Application
    $wb = $Excel.Workbooks.Open($excelFile)
    foreach ($ws in $wb.Worksheets) {
        $ws.SaveAs("$myDir\" + $File + ".csv", 6)
    }
    $Excel.Quit()
}

An example usage (untested):

$FileName = "\path\to\workbook"
ExcelToCsv -File $FileName