Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How to divide a MS Excel Spreadsheet in to multiple files

Posted on 2014-07-22
4
Medium Priority
?
957 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 31

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 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
    
    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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

572 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