Save selected sheets in a workbook to separate CSV files

Question

This is a very similar question to others asked, but I'm using Excel 2011 on a MAC and don't seem to be getting the desired result.

I have been exporting each sheet in an excel file manually as a windows comma separated CSV file (not MS DOS CSV) and using a bit of php my tables display perfectly on my site.

Using the code below in a Macro I can successfully export individual sheets as individual CSV files with one click. However, they don't have the .CSV extension at the end of the file name, and although the look okay with commas in the right place, when I upload them to my web site, they are displayed in one long row, instead of several rows.

Is this a MAC excel issue, or is there something that can be added to the below to fix?

Sub asdf()
Dim ws As Worksheet, newWb As Workbook

Application.ScreenUpdating = False
For Each ws In Sheets(Array("Sheet1", "Sheet2"))
   ws.Copy
   Set newWb = ActiveWorkbook
   With newWb
      .SaveAs ws.Name, xlCSV
      .Close (False)
   End With
Next ws
Application.ScreenUpdating = True

End Sub

score 0 · Accepted Answer · edited May 23 '17 at 12:27

Try building the extension into your filename:

.SaveAs ws.Name & ".csv", xlCSV

When you check the original file listing in the Finder, it reports "Simple Text Document", when you add the .csv extension it reports "comma-separated values" as the file type (Kind). Maybe this will fix your browser import issue?

If not an option may be to write out your data cell-by-cell. This similar post may be helpful to you.

Save selected sheets in a workbook to separate CSV files

1 Answers1