I'm using VBA in Excel to loop through a series of files and decide which ones to import. I'd like to decide which files to import using something like the file's tags, so that I don't need to open every single file. I'm trying to use the GetDetailsOf method to get them, but it's failing whenever I try to use a variable for the file name.

This code, using a Constant for the file name, works correctly:

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

However, when I try to replace the constant with a variable passed by the calling subroutine, I get an error. Here is the failing code:

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?

  • Try passing strPath & strFile to GetTags instead of just strFile. I mean you can pass it as a literal concatenation, you don't necessarily have to pass it as two parameters. – Bill Hileman Jun 14 at 14:33
up vote 1 down vote accepted

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
  • Okay, that's just...crazy. Why would that work?! It's closer, but something's still off...instead of returning "Imported 188", as it does when I use a constant, it's returning "Tags", the name of the category rather than its content. – Werrf Jun 14 at 15:18
  • @Werrf Added a third work-around. I'm still puzzled by why it doesn't work and even more so why the work-arounds even manage to by-pass the error. – robinCTS Jun 14 at 15:23
  • @Werrf Not sure what you're doing differently, but all three work-arounds are working on my end. I'm running on Excel 2007, though. – robinCTS Jun 14 at 15:27
  • Got it - I had tried passing the full path to strFile to see if that made a difference. When I passed the full path, it gave me Tags as the result, but when I passed it just the file name it gave me the content of the tag, Imported 188. Working, thank you! – Werrf Jun 14 at 16:00
  • @Werrf I think I know what the problem is. You should be using strFile instead of .Items.Item(strFile) when calling .GetDetailsOf(). Still trying to nail down the precise details, but it seems that .Items doesn't have a "proper" .Item method and fails sometimes. You can use either a FolderItem object or a string for the argument to .GetDetailsOf so using a string should solve the problem. I might try a bit more research and then update my answer. – robinCTS Jun 14 at 16:12

The Dir() function does NOT return a full path, it only returns the filename and extension. Therefore, when you try to access the tags, you're only passing the filename and extension if you get the result of Dir(). Instead, pre-pend the path as shown below. I changed the name of the passed variable in GetTags to help avoid confusion.

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(strPath & strFile)
        strFile = Dir()
    Loop
End Sub

Function GetTags(ByVal strFullPath As String)
    With CreateObject("Shell.Application").Namespace("C:\Users\XXXX\Documents\Safe Space\MacroTest\")
        GetTags = .GetDetailsOf(.Items.Item(strFullPath), 18)
    End With
End Function
  • No change. Note that in the code that works, the constant also only contains the file name. – Werrf Jun 14 at 14:55
  • In your code that works, you define csFile but you never use it that I can see - it still references strFile which declared only in TestFlags(). You also declare an integer i that never gets used. – Bill Hileman Jun 14 at 15:01
  • That was my oversight, sorry - I must have missed that change when I was copying it. Corrected to show that the working code uses csFile in the first argument for .GetDetailsOf. The integer i is an artefact from an earlier test I was doing, and can be ignored. – Werrf Jun 14 at 15:04

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.