Export Excel data as multiple files
Is a macro ok?
Option Explicit
Sub testme()
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim CurWks As Worksheet
Dim TempWks As Worksheet
Dim myPath As String
Dim myFileName As String
Dim fCtr As Long
Application.ScreenUpdating = False
Set CurWks = Worksheets("Sheet1")
Set TempWks = Workbooks.Add(1).Worksheets(1)
'this folder has to exist!
myPath = "C:\my documents\excel\test"
If Right(myPath, 1) < 1 Then
myPath = myPath & "\"
End If
With CurWks
FirstRow = 2 'headers in row 1???
'I used column A to determin the last row to process
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
fCtr = 0
For iRow = FirstRow To LastRow
Application.StatusBar = "Processing: " & iRow
fCtr = fCtr + 1
myFileName = Format(fCtr, "00000") & ".csv"
.Rows(iRow).Copy
TempWks.Range("a1").PasteSpecial Paste:=xlPasteValues
Application.DisplayAlerts = False
TempWks.SaveAs Filename:=myPath & myFileName, FileFormat:=xlCSV
Application.DisplayAlerts = True
TempWks.Cells.Clear 'probably not necessary, but it won't hurt
Next iRow
End With
TempWks.Parent.Close savechanges:=False
With Application
.StatusBar = False
.ScreenUpdating = True
End With
End Sub
K wrote:
I need to export a table of excel data with 2-3000 rows (records) and
10-15 columns (variable names) so that each 1 becomes an individual text
.csv file.
Any suggestions? many thanks
--
Dave Peterson
|