Divide file into multiple files?

  • Thread starter Thread starter Robert Blackwell
  • Start date Start date
R

Robert Blackwell

Say, I have csv with 200 addresses in it. Would it be possible to break down
the file or export the records into multiple csv's with like only 25 records
per file?

I know it sounds a little crazy, but the reasoning is that I need this for
postage printing software. I need small batches because the printer can only
hold X envelopes, and also, if the printer jams in between it totally
screwes everything up, and you could end up losing postage on the remaining
unprinted records.
 
You could use a little macro:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim newWks As Worksheet
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim myStep As Long
Dim iCtr As Long

myStep = 25

Set wks = Worksheets("sheet1")
Set newWks = Workbooks.Add(1).Worksheets(1)
iCtr = 0
With wks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For iRow = FirstRow To LastRow Step myStep
newWks.Cells.Clear
.Rows(iRow).Resize(myStep).Copy _
Destination:=newWks.Range("a1")
With newWks
Application.DisplayAlerts = False
iCtr = iCtr + 1
.Parent.SaveAs Filename:=Environ("temp") & "\Extracted_" _
& Format(iCtr, "000"), _
FileFormat:=xlCSV
Application.DisplayAlerts = True
End With
Next iRow
End With

newWks.Parent.Close savechanges:=False
End Sub

I stuck the CSV files into the windows temp folder as: Extracted_###.csv.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Oh okay, got it. Now after it ran though, I couldn't find the files.

..Parent.SaveAs Filename:=Environ("temp")

I checked c:\windows\temp and there was nothing new inside.

I tried changing ("temp") to ("c:\temp") but nothing was inside that folder
either. After doing a search on my HD it turns out it was saving to d:\ not
c:\temp

strange
 
You need more than that...

..Parent.SaveAs Filename:=Environ("temp") & "\Extracted_" _
& Format(iCtr, "000"), _
FileFormat:=xlCSV

Was the original line. It saves them as Extracted###.csv in your temp folder.

A quick way to get to windows temp folder:

Windows start button|run
%temp%
and hit ok

If you want a different (*already existing* folder):

..Parent.SaveAs Filename:="C:\myexistingfoldername\Extracted_" _
& Format(iCtr, "000"), _
FileFormat:=xlCSV
 
Hello,

I saw the macro you posted on 12/30/04 in reply to a
request on how to divide one large file into several
smaller files. The macro was "Option Explicit - Sub testme
()"

It looks like it will solve my problem, except there is a
varying # of rows in my separate files (not 25 in ea per
your macro). I need to separate the large file into
separate files according to store #. I'm very new to
macros. Can you tell me what to change in your macro to
do this? Also, is there a way to have the files named
according to which store the data is for when it's
extracted? I can see how to change "extracted" to "store"
and I think I can figure out how to change "CVS" file
format to "XLS".

I appreciate any help you can give. I have to create and
send out 117 separate files tomorrow (Friday).
(I wanted to find your original response to reply to, but
I kept getting a system error. Thanks again!
-----Original Message-----
You need more than that...

..Parent.SaveAs Filename:=Environ("temp") & "\Extracted_" _
& Format(iCtr, "000"), _
FileFormat:=xlCSV

Was the original line. It saves them as Extracted###.csv in your temp folder.

A quick way to get to windows temp folder:

Windows start button|run
%temp%
and hit ok

If you want a different (*already existing* folder):

..Parent.SaveAs
Click to expand...
Filename:="C:\myexistingfoldername\Extracted_" _
 
Back
Top