从另一个工作簿调用宏时VBA代码退出循环

Question

一旦进入，它从新打开的工作簿中调用另一个子例程，子例程运行良好（需要2-3分钟），但随后代码自动退出循环（我的意思是它不打开循环中的下一个文件）。是因为被调用宏的处理时间吗？我可以用一些关于这个问题的见解，这将是非常有帮助的。提前致谢：）

Public strFileName As String
Public strfilename2 As String
Public currentWB As Workbook
Public dataWB As Workbook

Sub GetData()
    Dim strListSheet As String
    Dim ws As Worksheet
    Dim Tabnames() As Variant
    Dim celladdress() As Variant
    Dim values() As Variant
    Dim tabcount As Integer
    Dim filecount As Integer
    Dim j As Integer
    Dim k As Integer
    Dim i As Integer

strListSheet = "Data_Specifics"

Sheets(strListSheet).Select
tabcount = WorksheetFunction.CountA(Rows(6)) - 1
filecount = WorksheetFunction.CountA(Columns(2)) - 4

ReDim Tabnames(0, 0 To tabcount - 1)
ReDim celladdress(0, 0 To tabcount - 1)
ReDim values(0 To filecount - 1, 0 To tabcount - 1)

For k = 0 To tabcount - 1
Tabnames(j, k) = ActiveWorkbook.Sheets(strListSheet).Cells(6, k + 4).Value
celladdress(j, k) = ActiveWorkbook.Sheets(strListSheet).Cells(7, k + 4).Value
Next k

For j = 0 To filecount - 1
    For k = 0 To tabcount - 1
        values(j, k) = ActiveWorkbook.Sheets(strListSheet).Cells(j + 9, k + 4).Value
    Next k
Next j
Range("B8").Select
i = -1

Set currentWB = ActiveWorkbook
Do While ActiveCell.Value <> ""

    strFileName = ActiveCell.Offset(1, 1)
    strfilename2 = ActiveCell.Offset(1, 0)

    Application.Workbooks.Open strFileName, UpdateLinks:=False, ReadOnly:=False
    Set dataWB = ActiveWorkbook

    i = i + 1
        For k = 0 To tabcount - 1
        Sheets(Tabnames(0, k)).Select
        Range(celladdress(0, k)).Select
        If values(i, k) <> "XXXXX" Then
        Selection.Value = values(i, k)
        Else
        End If
        Next k

        strfilename2 = "'" & strfilename2 & "'" & "!ValidateDataNew"
        Application.Run strfilename2
        dataWB.Close SaveChanges:=True

    currentWB.Activate
    Sheets(strListSheet).Select
    ActiveCell.Offset(1, 0).Select
    i = i
Loop
Exit Sub

末端接头

Answer 1

为了确保错误来自validateDataNew宏，让我们首先删除sub getdata代码中可能存在的错误。

我们应该删除使用select、selection和ActiveCell时可能出现的所有错误。相反，应使用完全限定的range对象。

用下面的代码替换do While activeCell.value<>“”循环：

Dim lRow As Long

Set currentWB = ActiveWorkbook
lRow = 8 ' start scanning from row 8

' use a loop with fully qualifed range, not select
Do While currentWB.Worksheets(strListSheet).Range("B" & lRow).Value <> ""

    strFileName = currentWB.Worksheets(strListSheet).Range("B" & lRow).Offset(1, 1)
    strfilename2 = currentWB.Worksheets(strListSheet).Range("B" & lRow).Offset(1, 0)

    Set dataWB = Workbooks.Open(strFileName, UpdateLinks:=False, ReadOnly:=False)

    i = i + 1
    For k = 0 To tabcount - 1
        If values(i, k) <> "XXXXX" Then
            dataWB.Worksheets(Tabnames(0, k)).Range(celladdress(0, k)).Value = values(i, k)
        Else
        End If
    Next k

    strfilename2 = "'" & strfilename2 & "'" & "!ValidateDataNew"
    Application.Run strfilename2

    dataWB.Close SaveChanges:=True
    Set dataWB = Nothing

    lRow = lRow + 1 ' advance the row by 1
Loop