☰

如何自动将单元格内容从一个 Excel 文件转移到另一个 Excel 文件？

tag-icon • tag-icon worksheet-function microsoft-excel-2010
如何自动将单元格内容从一个 Excel 文件转移到另一个 Excel 文件？

我有两个 Excel 文件，第一个文件包含大约 5500 个单元格，第二个文件是空的，我想将第一个文件中每个工作表的内容转移到第二个文件的单元格中。

显然，我想将第一个工作表（J2：O2）中的单元格内容转移到第二个文件中的glcm11，将第一个工作表（J11：O11）中的单元格内容转移到glcm12，将第一个工作表（J20：O20）中的单元格内容转移到glcm13，将第一个工作表（J29：O29）中的单元格内容转移到glcm14。

然后，我想将第二张工作表 (J2: O2) 中的单元格内容转移到第二个文件中的 glcm21，并将第二张工作表 (J11:O11) 中的单元格内容转移到 glcm22，依此类推。由于文件中的工作表数量太大，我如何才能自动执行此操作而不使用手动方法。请帮帮我。

第一个文件的第一张表

第一个文件的第二张表 第二个文件

答案1

解决方案 1：公式

这种方法非常规，但我很乐意编写它......如果我为自己做这件事，那么我可能会转向 VBA 或 Power Query。

在我们开始之前，您需要确保两个文件都已打开并且可以引用它们。通过单击第二个工作簿（目标）的空单元格并输入来测试这一点，=然后单击第一个工作簿（源）的任意单元格并按Enter。现在查看您键入的公式栏=，您应该会看到类似的内容='[Book1]Sheet1'!$A$1 如果在之后没有看到任何内容，=请尝试调整两个工作簿的大小，使它们都显示在屏幕上，然后重试。如果仍然只看到，那么=尝试关闭源工作簿并从目标工作簿内部打开它File Menu，然后重试。如果仍然不起作用，那么这两种解决方案都无法帮助您，因为它们需要工作簿相互识别。

您还需要确保源文件中的工作表与您提供的示例数据相匹配，这意味着它们被命名为、、Sheet1等。如果它们不是这样命名的，那么您需要面朝角落站一个小时，这样您才能考虑您提供的示例中还可能存在哪些错误陈述。Sheet2Sheet3

我们几乎已经准备好开始了，所以继续并最大化目标文件窗口，因为我们将在那里完成所有工作；但不要关闭源，它需要保持打开状态。

我们首先在目标单元格中​​输入源工作簿的名称I1。不要使用任何特殊字符。如果您进行的测试看起来像，='[Book1]Sheet1'!$A$1那么您只需Book1在单元格中输入I1。请记住，这一切都在目标工作表中。

现在复制以下三个公式并将它们粘贴到目标表中。

复制="'["&$I$1&"]Sheet0'!"并粘贴到H1

复制=IF(MOD(ROW()-ROW($D$2),4)=0,LEFT($H$1,FIND("0",$H$1)-1)&VALUE(MID(H1,FIND("0",$H$1),FIND("!",H1)-1-FIND("0",$H$1)))+1&"'!J2",LEFT(H1,FIND("J",H1))&VALUE(MID(H1,FIND("J",H1)+1,LEN(H1)))+9)并粘贴到H2

复制=OFFSET(INDIRECT($H2),0,COLUMN()-COLUMN($B$2),1,1)并粘贴到B2

如果您操作正确，单元格B2将具有来自源工作表中单元格的数据J2，您可以使用它B2来填充范围B2:G2。通过拖动或复制粘贴来执行此操作，两种方法都可以。

此时，Row 2应该从中填充B:H，您应该会看到源文件中的数据B:G，并且您可以B2:H2根据需要将工作表复制下来。

当您对结果满意时，您可以复制数据范围并使用 粘贴Paste Special Values。这将保留数据但会断开与源文件的链接，以便您可以安全地删除Column H。

解决方案 2：VBA

我把它弄得比实际需要的更复杂，但它仍然易于遵循和使用。

  1. 首先关闭所有工作簿并完全关闭 Excel。
  2. 现在创建一个新的工作簿，然后用可识别的名称保存它。
  3. 按 从此工作簿内部打开 VBA 编辑器Alt F11。
  4. 按 在 VBA 编辑器内激活项目资源管理器 Ctrl R。它可能位于屏幕左侧。
  5. 在项目资源管理器中找到工作簿的 VBAProject。
  6. 在项目资源管理器中右键单击工作簿的 VBAProject。
  7. 展开Insert菜单并选择Module
  8. 在文件夹中找到Module1工作簿的项目Modules 。
  9. 双击左键Module1
  10. 在 VBA 编辑器中心的大白色区域内单击鼠标左键，并确认窗口标题以[Module1(Code)]
  11. 复制下面的代码并将其粘贴到编辑器中。
  12. 关闭 VBA 编辑器并返回 Excel。
  13. 我建议在继续之前将工作簿保存为启用宏的工作簿。
  14. 左键单击ViewExcel 功能区上的选项卡。
  15. 左键单击Macro图标 - 或 - 左键单击Macro下拉菜单并选择View Macros注意：您的安全设置决定接下来发生的情况。
  16. 如果您的安全设置允许宏，则选择 CopyDataFromWorkbook并单击左键Run

如果您的安全设置阻止您运行任何宏，您可以尝试自己更改它们，方法是打开FileExcel 中的菜单，选择Options、，Trust Center单击按钮Trust Center Settings，Macro Settings在左侧面板中选择并设置适当的级别。一般来说，我不建议启用所有宏，我个人的偏好是只运行签名的宏，但为您设置这些宏超出了本文的范围。

我们只是将宏安装在与您的源文件和目标文件无关的工作簿中，以保护您的数据免受任何可能的错误的影响。我让它足够灵活，以便您可以根据需要直接将其安装在源文件或目标文件中。

运行宏时会发生什么

宏询问源表是否打开。

如果选择“是”，它会列出它可以看到的每个工作簿并要求您选择源。Not Listed包含该选项是因为如果源位于不同的 Excel 实例中，则源可能会打开但不可见（与上面的公式存在同样的问题）

如果选择否，则会打开一个窗口并允许您选择源工作簿。

然后，宏会询问目标工作表是否已打开，然后您按照相同的步骤操作。如果您从打开的工作簿列表中选择了源，则不会列出该工作簿供您选择作为目标。

从那里开始，它会循环遍历源中的每个工作表，一次将一行写入目标。如果您发现它运行得太慢，我可以稍微加快速度，但我认为您的数据集足够小，您不会真正注意到。

复制此代码并将其粘贴到Module1您的 VBA 项目中

Option Explicit

Sub CopyDataFromWorkbook()
    Dim srcWorkbook As Workbook
    Dim dstWorkbook As Workbook
    Dim srcWorksheet As Worksheet
    Dim dstWorksheet As Worksheet
    Dim srcActiveRow As Long
    Dim dstActiveRow As Long
    Dim arydata()
    Set srcWorkbook = GetAppropriateWorkbook("source file")
    If Not srcWorkbook Is Nothing Then Set dstWorkbook = GetAppropriateWorkbook("destination file", srcWorkbook)
    If Not dstWorkbook Is Nothing Then
         dstActiveRow = 2
        dstWorkbook.Activate
        Set dstWorksheet = dstWorkbook.Sheets(1)
        For Each srcWorksheet In srcWorkbook.Worksheets
            For srcActiveRow = 2 To 29 Step 9
                arydata = srcWorksheet.Range("J" & srcActiveRow, "O" & srcActiveRow)
                dstWorksheet.Range("B" & dstActiveRow, "G" & dstActiveRow) = arydata
                dstActiveRow = dstActiveRow + 1
            Next srcActiveRow
        Next srcWorksheet
        dstWorkbook.Activate
        ActiveWindow.Visible = True
    End If
End Sub

Private Function GetAppropriateWorkbook(ByVal strRole As String, Optional SelectedWorkbook As Workbook) As Workbook
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim rng As Range
    If MsgBox("Is the " & strRole & " open?", vbQuestion + vbYesNo, "FINDING " & UCase(strRole)) = vbYes Then
        Set ws = ListOpenWorkbooks(SelectedWorkbook)
        On Error Resume Next
        Set rng = Application.InputBox("Select the " & strRole & " and click 'OK'", Title:="ACTIVATE " & UCase(strRole), Type:=8)
        Set wb = Application.Workbooks(rng.Value)
        On Error GoTo 0
        Application.DisplayAlerts = False
        ws.Delete
        Application.DisplayAlerts = True
        If wb Is Nothing Then
            MsgBox "Could not detect the " & strRole & ".  Trying a different way."
        End If
    End If
    If wb Is Nothing Then
        With Application.FileDialog(msoFileDialogOpen)
            .Filters.Clear
            .Title = "Find and open the " & strRole
            .Filters.Add UCase(Space(2) & strRole & Space(40)), "*.xls; *.xlsx; *.xlsm", 1
            .AllowMultiSelect = False
            .Show
            If .SelectedItems.Count = 1 Then
                Application.ScreenUpdating = False
                Set wb = Workbooks.Open(Mid(.SelectedItems(1), InStrRev(.SelectedItems(1), "\") + 1, Len(.SelectedItems(1))))
                ThisWorkbook.Activate
                Application.ScreenUpdating = True
            End If
        End With
    End If
    If wb Is Nothing Then
        MsgBox "Cannot continue without a " & strRole, vbCritical, "No " & strRole & " selected"
    Else
        Set GetAppropriateWorkbook = wb
    End If
End Function

Private Function ListOpenWorkbooks(Optional SelectedWorkbook As Workbook) As Worksheet
    Dim ws As Worksheet
    Dim wb As Workbook
    Dim s As String
    Dim i As Long
    If Not SelectedWorkbook Is Nothing Then s = SelectedWorkbook.Name
    i = 1
    Set ws = ThisWorkbook.Worksheets.Add
    For Each wb In Application.Workbooks
        If s <> wb.Name Then
                ws.Cells(i, 1) = wb.Name
                i = i + 1
        End If
    Next
    ws.Cells(i, 1) = "Not listed"
    Set ListOpenWorkbooks = ws
End Function

相关内容