How can a .xlsx of multiple linked worksheets be split into separate worksheet-files, and how can it thereafter be reassembled?

Question

A non-IT related class has been assigned a group project where the work they do will be stored is a single .xlsx file. The members decided the best way to collaboratively edit said file would be to split it into its constituent sheets, upload each *.xlsx sheet to an SVN repository, and use locks and a .txt file to organize sheet/member responsibility.

The group has accomplished the splitting of said files with a VB script (courtesy of this wonderful site) which was as follows:

Sub SaveSheets()
Dim strPath As String
Dim ws As Worksheet

Application.ScreenUpdating = False

strPath = ActiveWorkbook.Path & "\"
For Each ws In ThisWorkbook.Sheets
    ws.Copy
    'Use this line if you want to break any links:
    BreakLinks Workbooks(Workbooks.Count)
    Workbooks(Workbooks.Count).Close True, strPath & ws.Name & ".xlsx"
Next

Application.ScreenUpdating = True
End Sub

Sub BreakLinks(wb As Workbook)
    Dim lnk As Variant
    For Each lnk In wb.LinkSources(xlExcelLinks)
        wb.Breaklink lnk, xlLinkTypeExcelLinks
    Next
End Sub

Therewith the group now has a repository where each member is currently in the process of editing their respective files. The question then is, how can we automate the re-unification of these files into one .xlsx file with the preservation of the original links.

EDIT 4/2: started bounty // I'm aware that the links were "broken" by the above script but am not exactly sure what this means though I suspect it would make re-assembly with the preservation of original links more difficult. It should be noted that the original file which had the links is still available and might could be used to assist with this problem.

EDIT 4/2: Excel version is 2010--original links do not exist in current files.

EDIT 4/3: Original links are not in the current files, but it is desired that with the re-unification the original links (from original unedited file, pre-splitting) be re-created/preserved.

Answer 1

If you have SharePoint, you can all update the same Excel (2003 or 2010) book.

http://office.microsoft.com/en-us/excel-help/about-shared-workbooks-HP005262294.aspx

Answer 2

We probably need more detail in order to help you, but you may be able to accomplish what you need as follows (maybe this can kick-start a solution):

• Loop through the sheets in the workbook
• For each sheet
  • Open the appropriate xlsx file
  • Identify non-formula cells
  • For each of those cells
    • Copy to the identical location in the main workbook
  • Close the xlsx file

Below is an example (based on your SaveSheets code). If you try this, be sure to backup everything first. We obviously don’t know how the spreadsheets are laid out and how they are used. It would really suck if it got all screwed up. Also, there are some assumptions:

• The layout and used range in the xlsx files are the exact same layout and used range as it appears in the original workbook.
• The links you are referring to are formulas (either to another sheet or another workbook).

If these assumptions are wrong, you will need to modify as appropriate (identifying specific ranges to copy and/or adding more robust logic to the routine).

Note that the actual code to do this is very short. I added comments and basic error handling which significantly increased the amount of code.

This code would be added to the original workbook.

Sub RetrieveSheets()
Dim strPath As String
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim wbk As Workbook
Dim rng As Range

Application.ScreenUpdating = False

strPath = ActiveWorkbook.Path & "\"
For Each ws In ThisWorkbook.Sheets

    'Open the xlsx file (read only)
    Set wbk = Nothing
    On Error Resume Next
        Set wbk = Workbooks.Open(strPath & ws.Name & ".xlsx", ReadOnly:=True)
    On Error GoTo 0

    'Continue if xlsx file was successfully opened
    If Not wbk Is Nothing Then
        Set ws2 = Nothing
        On Error Resume Next
            Set ws2 = wbk.Worksheets(ws.Name)
        On Error GoTo 0

        'Continue if appropriate sheet was found
        If Not ws2 Is Nothing Then
            'Identify cells to copy over (cells that are constants)
            For Each rng In ws2.Cells.SpecialCells(xlCellTypeConstants)
                'set the cell value equal to the identical cell location in the xlsx file
                If (Left(ws.Range(rng.Address).Formula, 1)) <> "=" Then
                    ws.Range(rng.Address) = rng
                End If
            Next
            Set ws2 = Nothing
        End If

        'Close the xlsx file
        wbk.Close False
    End If
Next
Set wbk = Nothing
Application.ScreenUpdating = True

End Sub

Answer 3

The links then don't really apply in the solution, as you said the original doesn't have any links and so reassembly with links isn't required.

The script provided even has a comment embedded saying "Use this line if you want to break any links:". So if you comment the line below out (prepend the line with a ') it will preserve the links in the child workbooks.

Using the answer to a previous question on copying sheets to another workbook reassembly can be accomplished with the following VBA:

Sub CombineSheets()
Dim strPath As String
Dim ws As Worksheet
Dim targetWorkbook As Workbook

Set targetWorkbook = ActiveWorkbook


Application.ScreenUpdating = False

'Adjust path location of split files
strPath = "C:\code\xls-split"

Dim str As String

    'This can be adjusted to suit a filename pattern.
    str = Dir(strPath & "\*.xl*")
    Do Until str = ""
        'Open Workbook x and Set a Workbook variable to it
        Set wbResults = Workbooks.Open(strPath & "\" & str, UpdateLinks:=0)
            For Each Sheet In ActiveWorkbook.Sheets
              Sheet.Copy After:=targetWorkbook.Sheets(targetWorkbook.Sheets.Count)
            Next Sheet
        wbResults.Close SaveChanges:=False
        str = Dir()
    Loop
Application.ScreenUpdating = True


End Sub

This will append the other workbooks to the currently opened workbook.

Sourced Replacing FileSearch function for code to find xls files in a directory.

Answer 4

This is a rough outline of how I accomplished this:

1. Use Office2013 and not office 2010
2. create tmp/ directory with original .xlsx assignment file.
3. create source/ directory in tmp/
4. use split sheets module (listed on this page), but comment out the line that break links.
5. place all of the resulting .xlsx files into source/ (you can delete the original .xlsx file)
6. remote the first sheet from the sources/ folder, and place it ../
7. Open this first sheet, and import/use the 'combinesheets' module listed on this page.
8. Save the sheet, reopen it and you'll be prompted to update links. Do so, and select "change source" and select the first sheet Re in step 6.
9. links will automatically update; done.

Notes: you'll have to save a file as macro enabled, ...

Answer 5

Take a look at this MSDN Article on Merging Data from multiple workbook (.xls/.xlsx) files

http://msdn.microsoft.com/en-us/library/office/gg549168%28v=office.14%29.aspx

I don't know much about VBA but I think this is what you are looking for.

Also note it does get rid of the need for a text file to manage the files