Geek asks and answers

How can .xlsx from several linked sheets be divided into separate sheet files, and how can they be subsequently assembled?

A non-IT class was assigned by a team project in which the work that it does will be performed is a single .xlsx file. The participants decided that the best way to co-edit this file would be to split it into their own sheets, upload each * .xlsx sheet to the SVN repository, and use the locks and the .txt file to organize the responsibility of the sheet / member.

The group split the specified files into a VB script (kindly provided by 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

, , . , .xlsx .

EDIT 4/2: // , "" script, , , , . , , , - .

EDIT 4/2: Excel - 2010 - .

EDIT 4/3: , , / ( , ).

+5
5

, , , , , (, ):

    • xlsx
    • -
    • xlsx

( SaveSheets). , . , , , . , . , :

  • xlsx - , .
  • , , ( , ).

, ( / ).

, . , .

.

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

Sub

+1

, , , .

script : " , :". , ( '), .

, 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

.

Sourced FileSearch , xls .

+1

, :

  • Office2013, 2010
  • tmp/ .xlsx.
  • / tmp/
  • ( ), , .
  • .xlsx /​​( .xlsx)
  • / .. /
  • / "", .
  • , , . " " Re 6.
  • ; .

: ,...

0

More articles:


All Articles