当我指定目录时,我有一个工作代码来检索关闭的工作簿中的特定单元格。我试图让这段代码遍历所述目录中的所有现有工作簿,从每个文件中检索相同的单元格。
这是我到目前为止的循环代码(我也包括使用的函数):
Private Function GetValueFromClosedWorkbook(path, file, sheet, ref)
Dim arg As String, xFolder As String
If Right(path, 1) <> "\" Then path = path & "\"
If Dir(path & file) = "" Then
GetValueFromClosedWorkbook = "File not found."
Exit Function
End If
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Address(, , xlR1C1) 'create the argument
GetValueFromClosedWorkbook = ExecuteExcel4Macro(arg)
End Function
Sub TestGetValueFromClosedWorkbook()
Dim p As String, xFolder As String
Dim s As String, a As String, f(1 To 2) As String, z As Long
xFolder = "\\generic path"
For z = 1 To 2
s = "Sheet1"
a = "A1"
p = xFolder '& "\*.xlsx"
f(z) = Dir(p & "\*.xlsx")
Do While f(z) <> ""
ActiveSheet.Range("A" & (z + 7)) = GetValueFromClosedWorkbook(p, f(z), s, a)
f(z) = Dir()
Loop
Next z
End Sub
这将获得正确的单元格,然后粘贴到单元格 A8 中的活动工作表中。
唯一的问题是当它遍历第二个文件时,它仍然从第一个文件中获取单元格。为什么会这样?或者如何确保第二个循环从第二个工作簿中检索单元格?
循环有点混乱,你正在覆盖这些值。请参阅以下更正代码:
Sub TestGetValueFromClosedWorkbook()
Dim p As String, xFolder As String
Dim s As String, a As String, f As String, z As Long
xFolder = "\\generic path"
s = "Sheet1"
a = "A1"
p = xFolder '& "\*.xlsx"
f = Dir(p & "\*.xlsx")
Do While f <> ""
ActiveSheet.Range("A" & (z + 7)) = GetValueFromClosedWorkbook(p, f, s, a)
f = Dir()
z = z + 1
Loop
End Sub
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句