Solved

How to divide a MS Excel Spreadsheet in to multiple files

Posted on 2014-07-22
4
865 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
[X]
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
4 Comments
 
LVL 29

Expert Comment

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

Accepted Solution

by:
Glenn Ray earned 500 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
    
    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
ID: 40301352
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
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
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

733 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