0

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.

3
  • This will probably require to do the Save As in a VBA macro, which requires converting the workbooks from .xlsx to .xlsm. Does this work for you?
    – harrymc
    Sep 18, 2021 at 8:03
  • @harrymc Unfortunately no, because it seems Google Sheets can't edit xlsm. I've updated the question. Sep 18, 2021 at 13:01
  • It's not really opening the csv file after the SaveAs; rather you are seeing the file as you last saved it. (The effect is the same, however). That is by design and you can't change it. You will need to close the file after your SaveAs. And then re-open your template file if you want to do more work on it. (or use VBA) Sep 20, 2021 at 11:27

1 Answer 1

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:

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.