Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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.

like image 669
eichoa3I Avatar asked Oct 22 '22 14:10

eichoa3I


1 Answers

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

like image 189
James Jenkins Avatar answered Oct 24 '22 10:10

James Jenkins