excel - VBA 错误 : 'Range' of object '_Global' Failed

Question

我正在使用 MS Access 和 MS Excel 来自动执行重复过程，其中我删除旧的 .csv 文件，在其位置创建一个新的 .csv 文件，用数据填充一组单元格，保存并关闭文件。以下代码在我每次执行它的 Odd 时间都完全按照预期工作。每次偶数，我都会在第 29 行(标有 ***)得到“对象 '_Global' 的'Range' Failed”。当我收到错误时，我结束进程并关闭它创建的 Excel 窗口而不保存。然后当我再次执行代码时，它就可以工作了。
我知道该错误是由于单元格引用错误造成的，但我尝试过的任何方法都无法解决此问题，以便代码每次执行时都能正常工作。我感谢任何人可以提供的任何见解。谢谢你。

    'Creating Excel Application and workbook instance
    Dim x1 As New Excel.Application
    Dim xWB As Excel.Workbook
    
    'Delete Previous version of file to avoid overwrite errors
    If Dir("Z:\ETI\01 ETI Engine\automated\ETI Hootsuite FaceBook Feed.csv") <> "" Then
        Kill ("Z:\ETI\01 ETI Engine\automated\ETI Hootsuite FaceBook Feed.csv")
    End If
    
    'Open new Workbook and save as CSV
    Excel.Application.DisplayAlerts = False
    Set x1 = New Excel.Application
    Set xWB = x1.Workbooks.Add
    x1.Visible = True
    xWB.SaveAs "Z:\ETI\01 ETI Engine\automated\ETI Hootsuite FaceBook Feed.csv", FileFormat:=xlCSV
       
    'Assign values to each post, after checking that they are not scheduled in the past.
    Dim url As String
    url = "https://web-ded.uta.edu/wconnect/CourseStatus.awp1?&course=" & Me.txtCourseCode
    
    Dim i As Integer
    For i = 1 To 4
        'Create and assign a generated date for the post
        Dim rndDate As Date
        rndDate = SocialMedia.randDateFB(Me.txtBegDate, i)
        'if the random date is after now, then create the post.
        If rndDate > Now() Then
            With xWB.Worksheets("ETI Hootsuite FaceBook Feed")
                ***.Range("A" & i).Value = rndDate
                .Range("B" & i).Value = SocialMedia.fbPost(Me.txtCatCode, SocialMedia.courseLocation(Me.txtCity, Me.txtState, Me.chkSimulcast), SocialMedia.courseDates(Me.txtBegDate, Me.txtEndDate))
                .Range("C" & i).Value = url
            End With
        Else
        End If
    Next i
    
    'Removes empty rows and removes duplicate posts to meet Hootsuite standards
    Range("A1", "C100").RemoveDuplicates Columns:=Array(2), Header:=xlNo
    Range("A1", "C100").RemoveDuplicates Columns:=Array(1), Header:=xlNo
    
    
    'The following code helped close a program loop, so that it doesn't need to be manually reset every time the code is run.
    xWB.Save
    xWB.Close
    x1.Quit
    
    Set x1 = Nothing
    Set xWB = Nothing
    
    Me.chkFacebookLinkedInPostsSent.Value = True

Answer 1

每个 Excel 方法/属性/对象都必须使用您自己的 Excel 对象进行限定。否则，它会创建保持事件状态的全局引用并阻止您的代码第二次工作。
这些留在您的代码中:

Excel.Application.DisplayAlerts = False

改成

x1.DisplayAlerts = False

和

Range("A1", "C100").RemoveDuplicates Columns:=Array(2), Header:=xlNo
Range("A1", "C100").RemoveDuplicates Columns:=Array(1), Header:=xlNo

必须符合您的工作表。