For the example subroutine below, I had the original csv file named
"multiYr.csv" and located it in the folder "C:\Test". The subroutine
creates muliple csv files from the sections separated by a blank line and
saves them with names "myTemp0.csv", "myTemp1.csv", myTemp2.csv, etc and
locates these new csv files in C:\Temp along with the larger original. For
each of these csv files, a new workbook is created. You would locate the
subroutine in a module, run it and then manually move through the windows
and do a 'File > SaveAs' for each, giving it an appropriate name and
location.
__________________________________
Sub BreakApartCSV()
Const ForReading = 1
Const ForWriting = 2
Dim strLine As String
Dim strTemp As String
Dim txtArray()
Set FSO = CreateObject("Scripting.FileSystemObject")
Set objFile = FSO.OpenTextFile("C:\Test\multiYr.csv", ForReading)
strTemp = ""
i = 1
Do Until objFile.AtEndOfStream
strLine = objFile.ReadLine
If strLine = "" Then
ReDim Preserve txtArray(i)
txtArray(i - 1) = strTemp
i = i + 1
strTemp = ""
Else
strTemp = strTemp & strLine
End If
Loop
txtArray(i - 1) = strTemp
For X = 0 To UBound(txtArray)
strPathTmp = "C:\Test\myTemp" & CStr(X) & ".csv"
Set tmpTextFile = FSO.CreateTextFile(strPathTmp, ForWriting)
tmpTextFile.Write txtArray(X)
tmpTextFile.Close
Workbooks.OpenText Filename:=strPathTmp, DataType:=xlDelimited
Next X
objFile.Close
Set FSO = Nothing
End Sub
___________________________________
Steve
