I am working with a large XLSX file 100K+ rows and I need to split it into smaller XLS files of 10K rows. Searching this forum, I found the code below for a macro that almost does what I need it to do, but I need it tweeked so that it will save it as an XLS file and I also want to change the naming structure to File1, File2, ect. Can anyone help with this?
Please Login or Register to view this content.
Last edited by EnormousRA; 10-05-2013 at 03:39 PM.
Welcome to the Forum!
First a couple of notes. We have a forum specifically for VBA issues, so I have moved the thread there. Second, your PHP tag gets the job done but you'll see that it also provides color coding that you may or may not find useful. We also have a CODE tag that preserves spacing without color coding.
Now then, I have made the change below as highlighted and have tested it. Close allows you to save the file with a different name but offers no option for selecting a different format. To do that you must use SaveAs, and then Close afterwards. xlExcel8 means Excel 97-2003, and is a built-in constant with a value of 56.
Note that the code you provided copies Row 1 as a header into all the split files. I'm assuming that's what you want.
Please Login or Register to view this content.
Thank you, this is exactly what I was looking to do. One other quick question, how does it determine where the file is being saved? Right now it seems to be saving it in a different folder than the excel file is in.
Ah, the value of giving a complete description in the first post....
Change this line to:
However, I do not have a Mac to test this on.Please Login or Register to view this content.
I guess it doesn't like this code on my Mac. It gives a compile error. I attached a screenshot below.
Screen Shot 2013-10-05 at 11.20.54 AM.jpg
I'm very sorry. Change PathName to Path.
Thank you for the help. Your code was close, and I got it figured out from there. I posted the finished working code below.
Please Login or Register to view this content.
Thanks for posting the completed code. I also did a little research and found that the directory delimiter on a Mac is ":". Good catch.
Hi guys, I get a run-time error '1004' Application-defined or object-defined error and just one output file of in my case 65k lines from a 639k .xslx. Any ideas appreciated..
Last edited by jochap; 08-23-2016 at 09:58 AM.
jochap, even though your question is a follow-up, the original question has been solved, yours is a new problem and you should start a new thread. You should also attach your file so we can see what code you are using.
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.
If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.
Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks