我在Excel中使用VBA遍历一系列文件并确定要导入的文件。我想使用文件标签之类的内容来决定要导入的文件，这样就不必打开每个文件了。我正在尝试使用该GetDetailsOf方法来获取它们，但是当我尝试使用变量作为文件名时却失败了。
此代码使用常量作为文件名，可以正常工作：
Sub TestTags()
Dim strPath As String
Dim strFile As String
strPath = "C:\Users\XXXX\Documents\Safe Space\MacroTest\"
strFile = Dir(strPath & "*.xls*")
Do While strFile <> ""
Debug.Print GetTags()
strFile = Dir()
Loop
End Sub
Function GetTags()
Const csFile As String = "MyTestFile.xlsx"
With CreateObject("Shell.Application").Namespace("C:\Users\XXXX\Documents\Safe Space\MacroTest\")
GetTags = .GetDetailsOf(.Items.Item(csFile), 18)
End With
End Function
但是，当我尝试用调用子例程传递的变量替换常量时，出现错误。这是失败的代码：
Sub TestTags()
Dim strPath As String
Dim strFile As String
strPath = "C:\Users\XXXX\Documents\Safe Space\MacroTest\"
strFile = Dir(strPath & "*.xls*")
Do While strFile <> ""
Debug.Print GetTags(strFile)
strFile = Dir()
Loop
End Sub
Function GetTags(ByVal strFile As String)
Const csFile As String = "MyTestFile.xlsx"
Dim i As Integer
With CreateObject("Shell.Application").Namespace("C:\Users\XXXX\Documents\Safe Space\MacroTest\")
GetTags = .GetDetailsOf(.Items.Item(strFile), 18)
End With
End Function
The only thing I'm changing is the argument in the .GetDetailsOf method, switching from a constant to a variable. Whenever it runs, it stops on that line with 'Error 445: Object doesn't support this action'
What am I doing wrong?
EDIT:
OK. Still can't work out precisely why case 2 doesn't work, but I have found that the "proper" way to get the FolderItem object corresponding to strFile (as required by .GetDetailsOf()) is to use the .ParseName() method:
Function GetTags(ByVal strFile As String)
Const csFile As String = "MyTestFile.xlsx"
Dim i As Integer
With CreateObject("Shell.Application").Namespace("C:\Users\XXXX\Documents\Safe Space\MacroTest\")
GetTags = .GetDetailsOf(.ParseName(strFile)), 18)
End With
End Function
I can't explain why it doesn't work, but I do have three work-arounds.
1) Use CStr(strFile) instead of strFile when calling .GetDetailsOf():
Function GetTags(ByVal strFile As String)
Const csFile As String = "MyTestFile.xlsx"
Dim i As Integer
With CreateObject("Shell.Application").Namespace("C:\Users\XXXX\Documents\Safe Space\MacroTest\")
GetTags = .GetDetailsOf(.Items.Item(CStr(strFile)), 18)
End With
End Function
or
2) Change the parameter type of strFile to Variant:
Function GetTags(ByVal strFile As Variant)
Const csFile As String = "MyTestFile.xlsx"
Dim i As Integer
With CreateObject("Shell.Application").Namespace("C:\Users\XXXX\Documents\Safe Space\MacroTest\")
GetTags = .GetDetailsOf(.Items.Item("" & strFile), 18)
End With
End Function
or
3) Concatenate a null string to strFile when calling .GetDetailsOf():
Function GetTags(ByVal strFile As Variant)
Const csFile As String = "MyTestFile.xlsx"
Dim i As Integer
With CreateObject("Shell.Application").Namespace("C:\Users\XXXX\Documents\Safe Space\MacroTest\")
GetTags = .GetDetailsOf(.Items.Item("" & strFile), 18)
End With
End Function
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句