时间戳

首页 > 解决方案 > CountA 使用工作表中的数据来引用外部文件

excel - CountA 使用工作表中的数据来引用外部文件

问题描述

我试图让这段代码工作：

Sub GetExternalData()

    Dim wbPath As String, WorkbookName As String
    Dim WorksheetName As String, CellRef As String
    Dim Ret As String, i As Long, N As Long

    For i = 1 To Sheets("x").Cells(Rows.Count, 1).End(xlUp).Row

        wbPath = Sheets("x").Cells(i, 1).Value
        WorkbookName = Sheets("x").Cells(i, 2).Value
        WorksheetName = Sheets("x").Cells(i, 3).Value
        CellRef = Sheets("x").Cells(i, 4).Value

        Ret = Application.WorksheetFunction.CountA("'" & wbPath & "[" & WorkbookName & "]" & _
              WorksheetName & "'!" & Range("C:C").Select)

        Sheets("x").Cells(i, 5).Value = ExecuteExcel4Macro(Ret)
        
    Next i
End Sub

基本上，我在第 1 列中有一个文件夹路径，在第 2 列中有文件名，在第 3 列中有工作表名称。我想拿这些并在参考文件的 C 列中找到项目数。

目前，当列中有 30 个值时，方程仅输出值 1。想知道我调用COUNTA函数的方式是否有问题，其结果稍后在ExecuteExcel4Macro. 最初它只是为了调用 ref 文件中的特定值（C2 处的值），它确实有效。

标签： excelvbaexcel-4.0

解决方案

ExecuteExcel4Macro可用于读取单个单元格，但不能将任何函数传递给它：仅当它是有效的 Excel 4.0 宏函数时（请参阅https://exceloffthegrid.com/download/4095/）

通过打开工作簿最容易做到这一点：

Sub GetExternalData()

    Dim wbPath As String, WorkbookName As String
    Dim WorksheetName As String, CellRef As String
    Dim Ret as long, i As Long, N As Long

    With ThisWorkbook.Worksheets("x")
        For i = 1 To .Cells(.Rows.Count, 1).End(xlUp).Row
    
            wbPath = .Cells(i, 1).Value
            WorkbookName = .Cells(i, 2).Value
            WorksheetName = .Cells(i, 3).Value
            'CellRef = .Cells(i, 4).Value
            
            With Workbooks.Open(wbPath & "\" & WorkbookName).Worksheets(WorksheetName)
                Ret = Application.CountA(.Columns("C"))
                .Parent.Close False
            End With
            .Cells(i, 5).Value = Ret
    
        Next i
    End With
End Sub

推荐阅读