How to create many txt files from a Excel file ?

[demianill]

Hi all !

I'm not a programmer, just a simple Excel user.
I have the following problem:


- I have a Excel file with more than 11,000 lines.
- I need to read the first 12 lines (line 1 to line 12) and create a
txt file (the filename could be "1.txt", for example)
- Then, read the next 12 lines (line 13 to line 24) e create the
filename "2.txt"
- Again, again, and again, for each next 12 lines, until the end of
11,000 lines, creating the filename "916.txt".


Probably, it's very easy for expert users, using a VBA macro.
But, for non-programmers, it's a big problem !

Please, help me ! Thanks for all !!!


Demian Nill

 

[Tom Ogilvy]

Sub MakeFiles()
Dim sPath as String, i as LOng
Dim j as Long, lastrow as long
Dim sh as Worksheet
sPath = "C:\MyNewFiles\"
i = 0
set sh = Activesheet
lastrow = cells(rows.count,1).End(xlup).row
for j = 1 to lastrow Step 12
set bk = workbooks.Add(template:=xlWBATWorksheet)
sh.rows(j).Resize(12).copy Destination:=bk.Worksheets(1)
i = i + 1
bk.SaveAs sPath & i & ".txt", FileFormat:=xlTextPrinter
bk.close SaveChanges:=False
next
End Sub

copy about 36 rows of your source workbook to another file, then make the
sheet with the 36 rows the active sheet. change the path in the code to
where you want the files to be stored. Then test the code on that (it runs
against the activesheet).

See if that produces what you want. If not, you might need to use a
different fileformat argument. You can copy some data to a workbook, then
turn on the macro recorded and do a files=>SaveAs and choose the format your
want. then turn off the macro recorder and look at the recorded code. See
what fileformat argument it recorded. Use that.

 

[Ron de Bruin]

Here is a example that Dave Peterson posted a few months back
It save the files in your temp folder (Start>Run %temp% )

With your data in a sheet named "Sheet1" and data in the A column


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 = 12

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