对象'_Global'的VBA方法'Union'失败 [英] VBA Method 'Union' of object '_Global' failed

问题描述

我正在尝试编写一个宏，该宏循环遍历文件夹中的所有工作簿，并且每个宏都发送一封电子邮件，其中包含一系列符合条件的行.当我运行宏时，它将对第一个文件执行此操作，但在第二个文件处停止，并给出错误对象'_Global'的方法'Union'失败"，并指向行"Set rng2 = Union(rng2，row)".下面是相关代码:

I'm trying to write a macro that loops through all the Workbooks in a folder, and for each one sends an email with a range of rows that meet criteria. When I run the macro, it does this for the first file but stops at the second giving the error "Method 'Union' of object '_Global' failed", pointing to the line "Set rng2 = Union(rng2, row)". Below is the relevant code:

Sub LoopThroughFiles()

Dim File As String

File = Dir("FilePath\")

While (File <> "")

    Set WorkBk = Workbooks.Open("FilePath\" & File)

    Dim rng As Range
    Dim row As Range
    Dim rng2 As Range
    Dim strbody As String

    Dim OutApp As Object
    Dim OutMail As Object

    Set rng = Range("B52:I200")

    For Each row In rng.Rows
        If row.Columns(7) >= Date Then
            If Not rng2 Is Nothing Then
                'Below is the line that gets the error
                Set rng2 = Union(rng2, row)
            Else
                Set rng2 = row
            End If
        End If
    Next

    'Email code removed

    WorkBk.Close savechanges:=True

    File = Dir()

Wend

End Sub

任何帮助将不胜感激！

推荐答案

您正尝试使用与以前的工作簿构建的范围相同的范围 Union .您需要为每个要处理的文件清除rng2:

You're attempting to Union with the same range that you built using the previous Workbook. You need to clear the rng2 for each file you process:

WorkBk.Close savechanges:=True
Set rng2 = Nothing  '<---You just closed the workbook this range was built with.
File = Dir()

这篇关于对象'_Global'的VBA方法'Union'失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！