时间戳

首页 > 解决方案 > 在个人工作簿中使用宏引用非活动工作簿

excel - 在个人工作簿中使用宏引用非活动工作簿

问题描述

我有一个可以在任何 Excel 工作簿中使用的宏，但是一旦我将它放在我的 PERSONAL.XLSB 文件中就无法使用。我的目标是从我桌面上一个文件夹中的所有文件中获取选项卡并将它们复制到活动文件中。我知道问题是我使用 This.Workbook 作为复制选项卡的位置参考，但我不知道如何引用我试图将选项卡复制到的工作簿。我不想为复制选项卡的位置引用文件路径，因为这将被多个文件中的多个人使用。任何想法将不胜感激。

Sub CombineWorkbooks()

Dim Path As Variant
Path = GetFolder(1) & "\"

Dim FileName As String
FileName = Dir(Path & "*.xl??")

Dim ws As Worksheet

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Do While FileName <> ""
    Workbooks.Open Path & FileName
    For Each ws In ActiveWorkbook.Sheets
        ws.Copy after:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
    Next ws
    Workbooks(FileName).Close
    FileName = Dir()
Loop

Application.ScreenUpdating = True
Application.DisplayAlerts = True

Exit Sub

End Sub

标签： excelvba

解决方案

如果您将文件定义为变量或活动工作簿，它应该可以工作。

更新，因为我认为我稍微误解了宏观的总体目标，但概念仍然相同。如果这不起作用，请告诉我。

Sub CombineWorkbooks()

Dim Path As Variant
Path = GetFolder(1) & "\"

Dim FileName As String
FileName = Dir(Path & "*.xl??")

Dim ws As Worksheet, wkBkToCopyTo As Workbook

Set wkBkToCopyTo = ActiveWorkbook 'assuming that you run this with the destination open.



Application.ScreenUpdating = False
Application.DisplayAlerts = False

Do While FileName <> ""
    Workbooks.Open Path & FileName
    For Each ws In ActiveWorkbook.Sheets
        ws.Copy after:=wkBkToCopyTo.Worksheets(wkBkToCopyTo.Worksheets.Count)
    Next ws
    Workbooks(FileName).Close
    FileName = Dir()
Loop

Application.ScreenUpdating = True
Application.DisplayAlerts = True

Exit Sub

End Sub

您也可以尝试根据其名称找到它：

'you could also use a loop to find it
For Each wkBkToCopyTo In Application.Workbooks
    If InStr(1, wkBkToCopyTo.Name, "someNameof the workbook", vbTextCompare) > 0 Then
        Exit For
    End If
Next wkBkToCopyTo

推荐阅读