• Home
 · 
• Files/File Controls/Input/Output
 · 
• With this Function you can split a file (it must be formatted so that you find the line feed at the

With this Function you can split a file (it must be formatted so that you find the line feed at the

With this Function you can split a file (it must be formatted so that you find the line feed at the end of each rows) into n-files defining

Rate With this Function you can split a file (it must be formatted so that you find the line feed at the

• 1
• 2
• 3
• 4
• 5

(2(2 Vote))

'================================================================================================
'Author: Michele Bortolotti
'Email: [email protected]
'================================================================================================
'With this Function you can split a file (it must be formatted so that you find the line feed at
'the end of each rows) into n-files defining the number of the rows you want to put in each new
'file. Hope it can be usefull!
'================================================================================================
'sFileName = name (with its path!) of the file you want to split
'lNumRowsForFile = max number of rows you want to put in each new files
'================================================================================================

On Error GoTo ErrManager

Dim lCount As Long
Dim lTotRows As Long        'Total lines/rows found in the file
Dim iTotNumFiles As Double  'Total splitted files will be generated
Dim sRow As String          'Content of each line
Dim sFiles() As String      'Array with all the names of the splitted files
Dim i As Integer

'Check if the given file exists
If Dir(sFileName) = "" Then
  MsgBox "File not found!", vbExclamation, "Attention: file not found!"
  Exit Function
End If

'Count how many lines we have in the given file
Open sFileName For Input As #1
  Do While Not EOF(1)
    Line Input #1, sRow
    DoEvents
    lTotRows = lTotRows + 1
  Loop
Close #1

'Number of the splitted files that will be generated
iTotNumFiles = Mid(lTotRows / lNumRowsForFile, 1, InStrRev((lTotRows / lNumRowsForFile), ",") - 1)

If iTotNumFiles = 0 Or InStrRev(lTotRows / lNumRowsForFile, "E") Then iTotNumFiles = 1

'Save into the array all the names of the splitted files
For i = 1 To iTotNumFiles
  ReDim Preserve sFiles(i)
  'Max 999 splitted files, change it if you like it (0000 means 9999 files, and so on)
  sFiles(i) = Replace(sFileName, ".txt", "") & Format(i, "000")
Next i

'If I found something from the division it means the I have to add one more file that will
'contain the remaining lines
If lTotRows Mod lNumRowsForFile <> 0 Then
  ReDim Preserve sFiles(i)
  sFiles(i) = Replace(sFileName, ".txt", "") & Format(i, "000")
End If

'Create all the necessary files...
For i = 1 To UBound(sFiles) - 1
  Open Replace(sFiles(i), ".txt", "") For Output As 1
  Close #1
Next i

i = 1
'Loop on the given file and populate (with append) all the splitted files
Open sFileName For Input As #1
  Do While Not EOF(1)
    DoEvents
    lCount = lCount + 1
    If lCount < lNumRowsForFile + 1 Then
      Open sFiles(i) For Append As 2
        Line Input #1, sRow
        Print #2, sRow
      Close 2
    Else
      'Reset the rows counter and take the next file
      lCount = 0
      i = i + 1
    End If
  Loop
Close #1

splitFile = True
Exit Function

ErrManager:
  splitFile = False
  'If you want give a message with the specified error...
  MsgBox "The follow error has raised in splitFile(): " & Err.Description & " #" & Err.Number, vbExclamation, "Attention!"

End Function