vba - VBA中对已关闭工作簿的动态引用

Question

我部分解决了我最初遇到的问题，并认为我对问题的描述有点过于详细。我决定重写我的问题，以便更容易理解问题，并且寻找相同事物的人可以更快地联系起来。

我有几个主题文件(每个都有不同的名称)，有 21 行和 21 列需要收集到 1 个文件(称为摘要)中。总之，我想要一个查看主题名称列表的代码，然后在单元格中放置对主题文件中相应单元格的引用。正如您在下面的代码中看到的，我已经完成了一个简化版本。它查看具有第一个主题文件名称的单元格，然后为该文件中的所有行和列创建引用。

Sub PullValue()
Dim path, file, sheet
Dim i As Integer, j As Integer

Application.ScreenUpdating = False


path = Worksheets("Settings").Range("B23")
file = Worksheets("Consolidation").Range("A1")
sheet = "Overview"

For i = 2 To 22
    For j = 1 To 21
        Cells(i, j).Formula = "='" & path & "[" & file & ".xlsm]" & _
        sheet & "'!" & Cells(i - 1, j).Address & ""
    Next j
Next i

Application.ScreenUpdating = True

End Sub

这可以正常工作，但在那之后，它必须对该主题名称表中的所有文件执行此操作。我会继续努力，但非常感谢您的帮助，谢谢。

如果需要更多信息，请随时询问。

谢谢!
巴特

Answer 1

经过大量的研究和反复试验，我想出了自己的解决方案。我将在这里分享它，以便处理相同问题的人可以在这里获得一些意见。

我在代码中添加了注释，这样更容易理解。

Sub PullValue()
Dim path, file, sheet
Dim LastRow As Long, TopicCount As Long
Dim i As Integer, j As Integer, a As Integer

Application.ScreenUpdating = False

'1. We count how many topics are written in the Topics table to decide the amount of loops
'I do this by checking the total rows in the table and subtract the empty ones
With Worksheets("Settings").ListObjects("Topics")

    TopicCount = .DataBodyRange.Rows.Count - _
    Application.CountBlank(.DataBodyRange)

End With

'2. We loop the code for the amount of times we just calculated so it does it for all topics
'I'll make a note where we can find that a in the code
For a = 1 To TopicCount

    '3. In the consolidation sheet where all the data will be, we want to check what the
    'LastRow is in column A to get the starting point of where the data is entered
    With Worksheets("Consolidation")

        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

    End With

    '4. This If function puts a spacing between the blocks of data if the LastRow is not the
    'first row. This is only to make it visually look better.
    If LastRow <> 1 Then

        LastRow = LastRow + 2

    End If

    '5. The first thing we want to do is put the name of the topic below the LastRow so it's
    'easy to check afterwards what topic the block of data belongs to. Here you can find the
    'a from the loop we have in the beginning.
    Cells(LastRow, "A").Value = [Topics].Cells(a, [Topics[Topics]].Column)

    '6. Locate where the path, file and sheet names are located in the document. Don't
    'forget to put the / at the end if it's a website or the \ if it's on your computer.
    'If you look to the code at comment number 7, we already have the .xlsm in the formula
    'so we don't need to enter that for the file.
    path = Worksheets("Settings").Range("D2")
    file = Worksheets("Consolidation").Cells(LastRow, 1)
    sheet = "Overview"

    'This is the core of the code and will the right reference in the right cell. This loops
    'for all the 21 rows and columns.
    For i = LastRow + 1 To LastRow + 21

        For j = 1 To 21

            Cells(i, j).Formula = "='" & path & "[" & file & ".xlsm]" & _
            sheet & "'!" & Cells(i - LastRow, j).Address & ""

        Next j

    Next i

Next a

Application.ScreenUpdating = True

End Sub

您可能对代码有任何疑问，请告诉我。我希望这可以帮助一些人。当然也欢迎改进。

巴特