Thread: Split Excel workbooks in flat source

Can you give us some more information, because I for one am really confused by this. Specifically:

1. What is a 'lac'
2. When you say you need a 'flat source' do you mean you need this outputting to a text file, or are you trying to combine it all into one sheet, or are you trying to split it into 30 sheets?
3. Where do the 3 numbers that top and tail each sheet come from? Do they already exist and you're trying to use them as delimiters for where each flat source starts and ends?
4. Some example data would be really, really useful - without this anybody on here is really just guessing as to your requirement.

Originally Posted by Richard Buttrey

(for Andrew's benefit a lac is an Indian numbering unit. Specifically 10^5 or 100,000)

Well, every day's a school day. Thanks for the info.

yeah lac is equal to 10^5=100,000

Flat source is textfile(notepad)
yes I want sheets as follows.

10 sheets of 4000 nos
07 sheets of 5000 nos
19 sheets of 8000 nos
04 sheets of 2000 nos

so total is 40 sheets. ( By mistake I write 30 in my last post)

I need these separate because I need to upload every sheet in my tool. I am doing this for saving this in a database. This is to be done with the help of tool.
So I need 40 sheets out of these total numbers.

in addition ..

The text file should have start like

MOBILE
99xxxxxxxxx
98xxxxxxxxx
97xxxxxxxxx


and in the end
99xxxxxxxxx
98xxxxxxxxx
97xxxxxxxxx


these number are to be changed on regular basis. so code should be flexible for these three number.
means I can change It whenever I want.

Sorry My English is not that good.

OK, assuming that all of your numbers are in column A then the following macro works for me:

Sub SplitIntoText()

Const sStartCell = "A1"
Const sHeaderText = "MOBILE"
Const sFilePath = "C:\" 'Change to whichever directory you want the files in, ending with a \
Const sFileName = "DATA"
Const sFileExt = ".txt"

Dim rngCurrentExport As Range
Dim rngCellLoop As Range
Dim avMobileNumbers As Variant
Dim sDateText As String
Dim lFileCounter As Long
Dim lExportRows As Long
Dim lLastRow As Long
Dim iFileHandle As Integer
Dim lMobLoop As Long

avMobileNumbers = Array("980000001", "9900000002", "9700000003")
lFileCounter = 0

With ActiveSheet

  lLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
  
  Set rngCurrentExport = Range(sStartCell)
  
  While rngCurrentExport.Row <= lLastRow
  
    lFileCounter = lFileCounter + 1
    Select Case lFileCounter
      Case Is < 10
        lExportRows = 2000
      Case 10 To 18
        lExportRows = 4000
      Case Else
        lExportRows = 7000
    End Select
    
    If rngCurrentExport.Offset(lExportRows - 1, 0).Row > lLastRow Then
      lExportRows = lLastRow - rngCurrentExport.Row
    End If
    
    iFileHandle = FreeFile
    
    Open sFilePath & Format(Now(), "ddmmm") & " " & sFileName & lFileCounter & sFileExt For Output As iFileHandle
      Print #iFileHandle, sHeaderText
      
      For lMobLoop = LBound(avMobileNumbers) To UBound(avMobileNumbers)
        Print #iFileHandle, avMobileNumbers(lMobLoop)
      Next lMobLoop
    
      For Each rngCellLoop In .Range(rngCurrentExport, rngCurrentExport.Offset(lExportRows - 1, 0)).Cells
        Print #iFileHandle, rngCellLoop.Value
      Next rngCellLoop
      
      For lMobLoop = LBound(avMobileNumbers) To UBound(avMobileNumbers)
        Print #iFileHandle, avMobileNumbers(lMobLoop)
      Next lMobLoop
      
    Close #iFileHandle
    
    Set rngCurrentExport = rngCurrentExport.Offset(lExportRows, 0)
    
  Wend
  
End With
      
End Sub

woow

that was awesome .

genius


thanks for the code. it works great,
but by my mistake it created 23k files. after that 40 files :D

there is some problem with code its create lots of files . I have tried it thrice after success files it start creating waste files with that three numbers & count goes 50 to 60k in a minute.

Based on you having 900,000 rows it will create 140 files - 9 with 2,000 rows in, 10 with 4,000 rows in and 121 with 7,000 rows in - isn't that what you wanted?

yeah its creating all the files, but in addition to those files it create waste files . and keep on doing that.

Ah, right. Is there anything in the extra files created? If so that would indicate that your sheet contains something in the cells after the data you want to export.

yeah
every sheet have this
MOBILE
99xxxxxxxxx
98xxxxxxxxx
97xxxxxxxxx


and in the end
99xxxxxxxxx
98xxxxxxxxx
97xxxxxxxxx

 
    lFileCounter = lFileCounter + 1
    Select Case lFileCounter
      Case Is < 11
        lExportRows = 4000
      Case 11 To 18
        lExportRows = 5000
      Case 18 To 37
        lExportRows = 8000
    Case 37 To 41
        lExportRows = 2000
      Case Else
        lExportRows = 830000
    End Select

I tried this it does not create the extra files, but in the end it shows me a error.