excel - Issue with VBA converting .XLSX file to bulk .CSV

Question

I have an excel file received on a monthly basis that includes multiple sheets. Each sheet needs to be split into .CSV files before it can be uploaded into our system for reading, and given that a single workbook might include upwards of 10 to 15 pages, it's a chore to do it by hand.

Presently, I'm using this VBA script to achieve the job:

Sub Splitbook()
     'Updateby20140612
     Dim xPath As String
          xPath = Application.ActiveWorkbook.Path
          Application.ScreenUpdating = False
          Application.DisplayAlerts = False
     For Each xWs In ThisWorkbook.Sheets
     xWs.Copy
          Application.ActiveWorkbook.SaveAs Filename:=xPath & "" & xWs.Name & ".csv"
          Application.ActiveWorkbook.Close False
     Next
          Application.DisplayAlerts = True
          Application.ScreenUpdating = True
End Sub

It "works". The problem? When you try to open the generated csv windows complains about an extension mismatch and warns about corruption. If you click through the dialogue it opens anyway, but the target system can't do that. So I'm left with figuring out what's missing or going back to parsing by hand. Any help?

question from:https://stackoverflow.com/questions/65922303/issue-with-vba-converting-xlsx-file-to-bulk-csv

Answer 1

I've created a version which combines your code with the relevant parts of the other answer. The open workbook will take the names of each of the worksheets in turn. It will therefore be important to save your workbook BEFORE running this code. The version which is open at the end will have all the tabs, but they aren't in the saved CSV file of the same name.

If you want to save again at the end back to the original name and format, I think some of the other answers can help with that too.

I've removed the Worksheet Copy command and commented out the Application.ActiveWorkbook.Close False line as I wasn't sure what they were doing.

Sub Splitbook()

     Dim xPath As String
     xPath = Application.ActiveWorkbook.Path
     Application.ScreenUpdating = False
     Application.DisplayAlerts = False
     
     For Each xWs In ThisWorkbook.Sheets
         xWs.SaveAs Filename:=xPath & "" & xWs.Name & ".csv", FileFormat:=xlCSV
          'Application.ActiveWorkbook.Close False
     Next
     
     Application.DisplayAlerts = True
     Application.ScreenUpdating = True

End Sub