0

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?

Damiano
  • 105
  • 1
  • Hi there Damiano. Welcome to Stack Overflow. Are you sure that this code "works"? The resulting file should be openable and readable in a text editor (if it's too big for notepad, try notepad++ or similar). It looks to me that you are saving a bunch of Excel files but giving them the file extension of ".csv". You haven't specified a "file format" only it's name. – Steve Lovell Jan 27 '21 at 15:45
  • Does this answer your question? [Saving excel worksheet to CSV files with filename+worksheet name using VB](https://stackoverflow.com/questions/10551353/saving-excel-worksheet-to-csv-files-with-filenameworksheet-name-using-vb) – Steve Lovell Jan 27 '21 at 15:49
  • And that could well be my problem. I'm not particularly savvy with vba and got most of this script a while ago from a thread that focused on splitting the workbooks. What would I need to add to fix it? – Damiano Jan 27 '21 at 16:12
  • Check the other SO question I linked to, and upvote the answer if you found it helpful. It has a "save as" line in the code which you might find useful. – Steve Lovell Jan 27 '21 at 16:15
  • I'm not sure if I'm missing a detail or not ... the code looks like it should work, but when I run it all that happens is the workbook updates and each sheet takes the name of the workbook, without ever making CSV files. I would note this other code requires me to specify a target directory, and I rather like how mine just puts the files in the same directory the workbook is already in. – Damiano Jan 27 '21 at 16:32

1 Answers1

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
Steve Lovell
  • 2,564
  • 2
  • 13
  • 16
  • This worked precisely as needed! I had almost puzzled it out myself and was just missing the save as steps. Thank you! – Damiano Jan 27 '21 at 17:07