excel - 如何为工作簿声明灵活变量？

Question

我想以某种方式声明动态工作簿变量，以便我可以更改工作簿文件名而不会导致错误。我最初的想法是使用 Set Variable = ActiveWorkbook ，但这似乎给出了一些特殊的错误:

    Set WkBk1 = ActiveWorkbook

    MsgBox (WkBk1.Name)
    WkBk1.Worksheets(1).Select

上面的代码本身就可以正常工作。之后，在相同的过程中，我打开一个新工作簿并运行以下命令:

    Set WkBk2 = ActiveWorkbook

    MsgBox (WkBk2.Name)
    WkBk2.Worksheets(1).Select
    WkBk1.Worksheets(1).Select

我在最后一行收到错误消息“运行时错误 1004；选择工作表类的方法失败”。任何人都可以解释这个问题的根源吗？我猜这与使用 ActiveWorkbook 声明变量或工作簿之间的错误方法有关。

我应该提到，打开的文件是在 excel 中打开的 .dat 文件。

最终，我希望有代码将两个工作表(无论文件名)分配给它们各自的变量。然后我想在两个工作簿之间复制和粘贴数据。

实际代码如下。问题在于 for 循环中的复制语句。
“运行时错误 '438' 对象不支持此属性或方法”。我以前使用过类似的语法，但无法弄清楚为什么它在这里不起作用。

Sub Import3()
    ' Imports TSS samples from backlog

    Dim TSS As Workbook, Backlog As Workbook
    Dim Sample As Range, SearchRange As Range
    Dim Count As Long

    Set TSS = ActiveWorkbook

    ChDir ("C:\lwuser6")
    Workbooks.OpenText Filename:="C:\lwuser6\BACKLOG.DAT", Origin:=437, _
           StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(7, _
           1), Array(68, 1), Array(78, 1), Array(86, 1), Array(126, 1), Array(150, 1)), _
           TrailingMinusNumbers:=True
        ' for some reason _ has to be in between "Array(7, _1)"
            ' Can't be between list elements of encompassing list "Array"
    Set Backlog = ActiveWorkbook

    Set SearchRange = Backlog.Worksheets("BACKLOG").Range(Cells(1, 1), _
        Cells(Rows.End(xlDown).Row, 1))
    Count = 14

    For Each Sample In SearchRange
        Backlog.Range(Sample(1, 1), Sample(1, 3)).Copy
        TSS.Range("G" & Count).PasteSpecial (xlValues)
        Count = Count + 2
    Next

End Sub

Answer 1

我强烈建议您通过 Rubberduck inspections 运行您的代码(它是免费和开源的；我管理该项目)，因为它不仅解释了您所询问的具体错误，还可以防止您陷入许多常见的初学者陷阱。

问题是 Backlog是 Workbook对象和 Workbook没有 Range成员:

Member not found

A member access call is made against an extended interface that Rubberduck couldn't resolve, or the member couldn't be found. If VBA cannot resolve the type at run-time, error 438 will be raised. [...]

您需要一个 Worksheet访问 Range , 一个 Workbook不会工作。

注意后面的 TSS.Range通话同样非法；-)


拿着这个:

Set Backlog = ActiveWorkbook

通过捕获 Workbook，摆脱对打开工作簿副作用的依赖。 Workbooks.Open 返回的对象:

Set Backlog = Workbooks.OpenText(Filename:="C:\lwuser6\BACKLOG.DAT", Origin:=437, _
       StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(7, _
       1), Array(68, 1), Array(78, 1), Array(86, 1), Array(126, 1), Array(150, 1)), _
       TrailingMinusNumbers:=True)

没关系， Workbooks.OpenText不返回 Workbook对象引用。

然后拿这个:

 Backlog.Worksheets("BACKLOG")

并将其拉入局部变量:

Dim backlogSheet As Worksheet
Set backlogSheet = Backlog.Worksheets("BACKLOG")

现在您可以在需要该特定工作表的任何地方使用它:

With backlogSheet
    Set SearchRange = .Range(.Cells(1, 1), .Cells(.Rows.End(xlDown).Row, 1))
End With

..这应该可行，尽管 IMO 提供 Range反对 Worksheet.Range属性感觉有些不对(如果 Sample 范围不在 backlogSheet 上，则会引发错误 1004):

backlogSheet.Range(Sample(1, 1), Sample(1, 3)).Copy

不确定 TSS 中的哪个特定工作表|你的意思是粘贴东西的工作簿，但同样的原则也适用。也就是说，您甚至可能不需要涉及剪贴板:

tssSheet.Range("G" & count & ":I" & count).Value = _
    backlogSheet.Range(Sample(1, 1), Sample(1, 3)).Value