在不打开的情况下检索多个已关闭工作簿的特定列的行中的最后一个值

Question

我在工作表中有一个文件列表，这些文件是当前目录子文件夹中的文件。

我需要在特定工作表(常量(中检索特定单元格(可以更改(的值。

在子文件夹中的 10 个文件中，这些文件都有一个名为"resumen"的工作表，我想获取 G 列中最后一行的值。

到目前为止，我有这个

Sub read_data_from_file_WO_openning()
Dim outputs_address As String
Dim FolderName As String, wbName As String,  cValue As Variant
outputs_address = Sheets("lista_macro").Range("G2").Value
ruta_csv_output = ActiveWorkbook.Path & outputs_address
FolderName = ruta_csv_output
'select files to review
For Each file_analysis In Sheets("archivos_en_outputs").Range("I2", Range("I2").End(xlDown))
wbName = file_analysis.Value
cValue = GetInfoFromClosedFile2(FolderName, wbName, "resumen", "G1")
MsgBox (file_analysis & cValue) 'to see the values
Next file_analysis
End Sub

Private Function GetInfoFromClosedFile2(ByVal wbPath As String, _
wbName As String, wsName As String, cellRef As String) As Variant
Dim arg As String
If Right(wbPath, 1) <> "" Then wbPath = wbPath & ""
arg = "'" & wbPath & "[" & wbName & "]" & _
wsName & "'!" & Range(cellRef).Address(True, True, xlR1C1)
GetInfoFromClosedFile2 = ExecuteExcel4Macro(arg)
End Function

在范围 I2 到向下，我有我的文件列表。
问题是我的"G1"只检索所有文件的单元格G1的数据，并且每个文件都需要G列的最后一行。
有时这些文件有 7 行，其他文件有 15 行。行数可以更改，但始终至少为 2。

我知道问题出在单元格引用中，但我不知道如何更改它来完成我所说的。

Answer 1

假设 G 列中没有空格，则可以将 ExecuteExcel4Macro 与 WorksheetFunction CountA 一起使用来查找最后一行。

Function getLastValueInColumnG(ByVal wbPath As String, wbName As String, wsName As String) As Variant
Dim count As Long
Dim Address As String
Address = getExternalR1C1Address(wbPath, wbName, wsName, "G:G")
count = ExecuteExcel4Macro("CountA(" & Address & ")")
Address = getExternalR1C1Address(wbPath, wbName, wsName, "G" & count)
getLastValueInColumnG = ExecuteExcel4Macro(Address)
End Function
Function getExternalR1C1Address(ByVal wbPath As String, wbName As String, wsName As String, cellRef As String) As String
If Right(wbPath, 1) <> "" Then wbPath = wbPath & ""
getExternalR1C1Address = "'" & wbPath & "[" & wbName & "]" & wsName & "'!" & Range(cellRef).Address(True, True, xlR1C1)
End Function

Answer 2

另一种方法，假设输出文件中的行数有限(<1000 ？

Option Explicit
Sub find_in_closed_files()
Application.ScreenUpdating = False
Dim Fch As Range
Dim Wb1 As Workbook: Set Wb1 = ActiveWorkbook
Dim Fld As String: Fld = Wb1.Path & Sheets("lista_macro").Range("G2").Value
If Not Right(Fld, 1) = "" Then Fld = Fld & ""
Dim Ws1 As Worksheet: Set Ws1 = Wb1.Sheets(1)
Dim Ws2 As Worksheet: Set Ws2 = Wb1.Sheets("tmp pull") 'This is a temp draft sheet to pull the data that you'll need to create
For Each Fch In Ws1.Range("I2", Ws1.Range("I2").End(xlDown))
Ws2.Cells.Clear
Ws2.Range("G1:G999").FormulaR1C1 = "=IF('" & Fld & "[" & Fch.Value & "]resumen'!RC<>"""",'" & Fld & "[" & Fch.Value & "]resumen'!RC,"""")"
Ws2.Range("G1:G999").Value2 = Ws2.Range("G1:G999").Value2
MsgBox Ws2.Range("G9999").End(xlUp).Value
Next Fch
Application.ScreenUpdating = True
End Sub

Answer 3

这会在 G 列中留下一个公式，该公式将跟踪已关闭的外部工作簿中简历工作表 G 列中的最后一个文本、数字或日期。

Sub xlsxLastG()
Dim i As Long, f As String
With Worksheets("archivos_en_outputs")
For i = 2 To .Cells(.Rows.Count, "I").End(xlUp).Row
'conform C:UserspublicAppDataDocumentstest.xlsb
'     to 'C:UserspublicAppDataDocuments[test.xlsb]resumen'!G:G
f = .Cells(i, "I").Value
f = Left(f, InStrRev(f, Chr(92))) & Chr(91) & Right(f, Len(f) - InStrRev(f, Chr(92)))
f = Chr(39) & f & Chr(93) & "resumen'!G:G"
.Cells(i, "G").Formula = _
"=index(" & f & ", max(iferror(match(1e99, " & f & "), 0), iferror(match(""zzz"", " & f & "), 0)))"
Next i
End With
End Sub

#N/A错误通常意味着 G 列为空;#REF!错误将指示引用的工作簿中不存在工作簿或没有简历工作表。