vba - 使用 GetObject 编辑其他工作簿工作 1 次 x

Question

首先，我是一个初学者。欢迎任何建议!只是尝试在工作中编写代码以节省以后管理 100 多个文件的时间。

我正在构建一个 Excel 宏，并且与许多其他用户一样，我正在尝试执行以下操作:

将宏“驻留在”工作簿“A”中。

使用工作簿“B”中的键盘快捷键触发宏

让宏在工作簿“B”中发挥作用(包括从工作簿“C”复制工作表)

这包括在某些时候提示用户输入工作簿“C”，定义一个变量并将工作簿 C 放入其中。

以下代码是宏的一部分，仅在 x 中运行一次，并且显然“崩溃”而没有警告或错误代码，甚至让我知道它已经崩溃。

'All the following code is in workbook "A"

Dim fd As FileDialog
Dim ffs As FileDialogFilters
Dim DestWkb As Workbook
Dim SourceWkb As Workbook
Dim SourceWkbPath As String

'Set active workbook as destination workbook (this is workbook "B")
Set DestWkb = ActiveWorkbook

'Prompt user for source workbook (this will be workbook "C")
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
    Set ffs = .Filters
    With ffs
        .Clear
        .Add "Excel", "*.xlsx"
    End With
    .AllowMultiSelect = False
    If .Show = False Then Exit Sub
    SourceWkbPath = fd.SelectedItems(1)
End With

'Here is where it starts to go wrong..
'This message box will show up with correct data in it :

MsgBox SourceWkbPath

'This line seems to work, as I can see in VB the workbook specified by the user
Set SourceWkb = GetObject(SourceWkbPath)

'From here... it's like there was an Exit Sub. I will rarely see the following msgbox : 

MsgBox "Success"

有人遇到过这样的麻烦吗？非常感谢!

Answer 1

GetObject 用于设置对已打开的外部应用程序的引用。以同样的方式使用 CreateObject 设置一个创建一个新的外部应用程序。

添加了一个检查以查看是否打开了同名的工作簿。

删除集 DestWkb = ActiveWorkbook

改用 ThisworkBook

添加了有趣的消息，所以你知道它有效。

    Dim fd As FileDialog
    Dim ffs As FileDialogFilters
    Dim SourceWkb As Workbook
    Dim SourceWkbPath As String
    Dim SourceWkbShortName As String
    'Prompt user for source workbook (this will be workbook "C")
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
        Set ffs = .Filters
        With ffs
            .Clear
            .Add "Excel", "*.xlsx"
        End With
        .AllowMultiSelect = False
        .Show
        SourceWkbPath = fd.SelectedItems(1)
        SourceWkbShortName = Mid(SourceWkbPath, InStrRev(SourceWkbPath, "\") + 1)
    End With

    On Error Resume Next
    
    Set SourceWkb = Workbooks(SourceWkbShortName)
    If Err.Number <> 0 Then
        Set SourceWkb = Workbooks.Open(SourceWkbPath)
    End If
    On Error GoTo 0
    
    MsgBox """Hello World!""", vbInformation, SourceWkb.Name & " Says:"

MSDN 说:ThisWorkbook 总是返回运行代码的工作簿。

问题是:“如果工作簿 A 在工作簿 B 中运行宏，那么 ThisWorkBook 指的是什么？

工作簿 A

Sub RunExternalCode()
   WorkBookAName= ThisWorkBook.Name

   WorkBookBName = Application.Run ""

End Sub

WorkBook B

Function GetThisWorkBooksName()
    GetThisWorkBooksName = ThisworkBook.Name
End Function

结果:

WorkBookAName = "WorkBookA"

WorkBookBName = "WorkBookB"