0

My original problem was finding out how to separate a file with 12000 rows into individual files with 1000 rows and keep the header. I found a great Q&A that does just that (with only one adjustment to number of rows), the link is here: How to split spreadsheet into multiple spreadsheets with set number of rows?

The only thing I need is for the script to save the files down as CSVs rather than Worksheets, but I can't see anything obvious in the script that defines the file type for me to change. FYI, I'm very new to this!

Thanks in advance

Community
  • 1
  • 1
user3119184
  • 1
  • Look up [SaveAs() method](http://msdn.microsoft.com/en-us/library/office/ff841185(v=office.15).aspx) of the `Workbook` object. See the `FileFormat` parameter and [xlFileFormat Enumeration](http://msdn.microsoft.com/en-us/library/office/ff198017(v=office.15).aspx) –  Oct 31 '14 at 10:06

1 Answers1

3

As mentioned by @vba4all in the comments above, the expression for Workbook.SaveAs is

expression .SaveAs(FileName, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AccessMode, ConflictResolution, AddToMru, TextCodepage, TextVisualLayout, Local)

You can visit this msdn article to read more about it.

To save the workbook as csv, you have to use

ActiveWorkbook.SaveAs Filename:="C:\Test.csv", FileFormat:=xlCSV

Please note that when you save the workbook as a csv with or without code, Excel will prompt you for confirmation. To avoid that you can set Application.DisplayAlerts to False

Here is an example

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="C:\Test.csv", FileFormat:=xlCSV
Application.DisplayAlerts = True

To read more about file formats, you may want to see this msdn link

Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
smackenzie
  • 2,880
  • 7
  • 46
  • 99
  • bit unfair, he said he was very new to this and so was giving him the direct code sample. – smackenzie Oct 31 '14 at 10:37
  • also, his question states "the only thing he needs is to save the files down as CSV" he stated a solution for the 12k rows in his question!? – smackenzie Oct 31 '14 at 10:38
  • Point taken. Removed that part from the comment. - 1 for an attempt to harvest points on someone else's suggestion :) Your direct code sample is not doing a better job than the msdn example. It is giving the code in a platter without even explaining what the code actually does. – Siddharth Rout Oct 31 '14 at 10:40
  • If you would have posted that line of code before @vba4all, I wouldn't have downvoted you :) – Siddharth Rout Oct 31 '14 at 10:42
  • fair do's...don't think it needs much explaining though ;-) – smackenzie Oct 31 '14 at 10:44
  • I hate downvoting but am really disappointed when I see this. Ok Guess what? Improve your answer and I will reverse the downvote. What say? I will even upvote it :) – Siddharth Rout Oct 31 '14 at 10:46
  • You can revert the changes that I did if you are not happy about it – Siddharth Rout Oct 31 '14 at 11:03