Solved

How to divide a MS Excel Spreadsheet in to multiple files

Posted on 2014-07-22
4
801 Views
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.

Example

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.
0
Comment
Question by:LenCepeda
  • 2
4 Comments
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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 ?
gowflow
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
Comment Utility
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
    
    frmSaveFilepath.Show
    If boolGotFile Then
        strFilePath = frmSaveFilepath.txtPath
    Else
        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
    
    Sheets("Master").Activate
    ActiveWorkbook.Worksheets("Master").Sort.SortFields.Clear
    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
        .Apply
    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
            Sheets("Master").Activate
            Range("A1:J1").Copy
            Sheets(strNewSh).Activate
            ActiveSheet.Paste
            Sheets("Master").Activate
            Range("A" & cl.Row - c & ":J" & cl.Row).Copy
            Sheets(strNewSh).Activate
            Range("A2").Select
            ActiveSheet.Paste
            Application.CutCopyMode = False
            Sheets(strNewSh).Move
            ActiveWorkbook.SaveAs Filename:=strFilePath & strFileName, _
                FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
            ActiveWindow.Close
            Sheets("Master").Activate
            c = 0
        Else
            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.

Regards,
-Glenn
EE-SplitMultiple.xlsm
0
 
LVL 27

Expert Comment

by:Glenn Ray
Comment Utility
Hi,

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.

Thanks,
 -Glenn
0
 

Author Closing Comment

by:LenCepeda
Comment Utility
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
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now