How to divide a MS Excel Spreadsheet in to multiple files

Posted on 2014-07-22
Medium Priority
Last Modified: 2014-09-05
Hello - I was supposed to set up a business / workflow process and need some advice on what system to use to accomplish this project.

Basically, we are supposed to receive a monthly report from a third party in the form of a MS Excel file, either .xls or xlsx.  The workbook should include a single tab with about 10,000 records (about 10 columns), the majority of the columns will include dollar amounts.


Column 1 = Name
Column 2 = Policy Number
Column 3 = Claim Number
Column 4 =  Date
Column 5 = $Premium Amount$
Column 6 = $Claim Amount
Column 7 = $Loss Amount$
Column 8 = $Loss Reserve$
Column 9 = $Adjustment Expense$
Column 10 = $Combined Loss and Expense$

I need to take the spreadsheet and divide it into several individual Excel or PDF files with data grouped by one of the columns (Ex, Column 1, Policy Number)

For instance, say there are 10,000 records but 9,000 unique Policy Numbers, I need to generate 9,000 individual Excel or PDF files including grouped data from the spreadsheet and dump each file into a shared directory.  So each Policy Number would have their own individual file.  We would need to be able to control the way the file name is created/formatted.

We already have a polling type application that will check that shared folder and import the Excel or PDF's into our Document Management program for end user access.

I really don't have any software development experience so I was going try to rely on my experience with either MS Excel or (limited) SQL.  I think I might be able to create a macro (vb Script) within excel or ms access but I wasn't sure either program could handle the creation of 9,000 files

Any thoughts or ideas would be greatly appreciated.
Question by:LenCepeda
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 31

Expert Comment

ID: 40212544
looking at your question can I assume that the file you intend to create from the unique Policy has only 1 row ?

if yes then why to create separate files ?
LVL 27

Accepted Solution

Glenn Ray earned 2000 total points
ID: 40212976
The following code (also included in the attached example file) should be able to split your policy data into individual workbooks for each policy.  
Option Explicit
Global boolGotFile As Boolean
Sub Split_To_Multiple_XL()
    Dim intLR As Long
    Dim c As Integer
    Dim rng As Range
    Dim cl As Object
    Dim strNewSh, strFilePath, strFileName As String
    If boolGotFile Then
        strFilePath = frmSaveFilepath.txtPath
        Exit Sub
    End If
    MsgBox "This process will take several minutes. Click OK to continue.", _
        vbExclamation + vbOKOnly, "Create Policy Files"
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    intLR = Cells.SpecialCells(xlLastCell).Row
    ActiveWorkbook.Worksheets("Master").Sort.SortFields.Add Key:=Range("B2:B" & intLR) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Master").Sort
        .SetRange Range("A1:J" & intLR)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
    End With
    Set rng = Range("B2:B" & intLR)
    For Each cl In rng
        If cl.Value <> cl.Offset(1, 0).Value Then 'create sheet
            strFileName = cl.Value & ".xlsx"
            strNewSh = cl.Value
            Sheets.Add After:=Sheets("Master")
            ActiveSheet.Name = strNewSh
            Range("A" & cl.Row - c & ":J" & cl.Row).Copy
            Application.CutCopyMode = False
            ActiveWorkbook.SaveAs Filename:=strFilePath & strFileName, _
                FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
            c = 0
            c = c + 1
        End If
    Next cl
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    MsgBox "Policy files saved."
End Sub

Open in new window

It assumes that you have a master worksheet (I've named it "Master" here, but you could change the code to suit your actual source sheet name) with data from columns A through J and no blank values in column B (policy numbers).  It also assumes that the policy "numbers" are alphanumeric and don't include characters that might not be allowed for file or sheet names (ex., dashes and periods are okay; asterisks, question marks, slashes are not).

You'll be prompted for a location to store your files; you can create a new directory in this process if you wish.

The macro will sort the existing data by policy number and then proceed.  If only one occurrence (row) of a policy number exists, the new sheet will only have that one row (plus header row).  If more than one occurrence exists, all rows will be shown on the new sheet.

I tested this with 53 files being created from 58 source rows and it took about 12 seconds (Windows 7 Enterprise 64-bit, Excel 2010 32-bit, 2.5GHz quad processor).  You can expect this to take several minutes to run with 10000 rows/9000 possible files.

I HIGHLY RECOMMEND that you save a copy of your original data and that you run this on a sample of it (say, 1000 rows) and store in a blank directory - local (your C: drive) to speed up processing.

LVL 27

Expert Comment

by:Glenn Ray
ID: 40301352

Did you have a chance to review and or test my solution?  If so, and it will work for you, can you please properly close this question by clicking the "Accept this solution" link above my submission above that answers your question?.  This will help ensure that future searches are meaningful to other EE members.

Otherwise, let us know if you have any other issues.


Author Closing Comment

ID: 40306569
Thank you so much.  Yes, the script works very well.  Ultimately we ran into trouble converting such a large volume of excel files to individual pdf files.  Acrobat and PDF Creator are a bit slow when is comes to volume conversion,

We are still investigating this process and others.  Thank syou

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question