GetDetailsOf Not working with Windows 7

[bcooler]

Hello all-

I've been using XP until I was recently converted over to Windows 7. I have been very successful with Allen Browne's solution for obtaining file properties (until now):

[link]http://allenbrowne.com/ser-59alt.html[/url]

I've added a little code to this and will give it below.

Now I'm running into some problems because of 2 issues:

1.) The new computer had a Windows setting that said to "hide known file extensions". Until I turned that off, the following code would not work:

For Each strFilename In objFolder.Items
            If objFolder.GetDetailsOf(strFilename, 0) = strTemp Then 'strTemp is a filename (with extension)
                arrHeaders(0) = strPath & strTemp 'file path
                arrHeaders(1) = objFolder.GetDetailsOf(strFilename, 0) 'file name
                arrHeaders(2) = objFolder.GetDetailsOf(strFilename, 10) 'title
                arrHeaders(3) = objFolder.GetDetailsOf(strFilename, 12) 'Category (using as 'AS9100 Keywords')
                arrHeaders(4) = objFolder.GetDetailsOf(strFilename, 14) 'Comments (using as 'Expiration Date')
                arrHeaders(5) = objFolder.GetDetailsOf(strFilename, 9) 'Author

                s = " INSERT INTO tblFileProp " & "(FilePath, FileName, Title, AS9100, Comments, Author) VALUES " _
                    & "(""" & arrHeaders(0) & """,""" & arrHeaders(1) & """,""" & arrHeaders(2) & """,""" & arrHeaders(3) & """,""" & arrHeaders(4) & """,""" & arrHeaders(5) & """)"
                
                db.Execute s, dbFailOnError
            Exit For
            End If
        Next

because "objFolder.GetDetailsOf(strFilename, 0)" is just a screen name, not a file name (no extension). That made me realize I have code that depends on a user's preferences.....ehhh. How do I make a solution that does not care what a user's settings are?

2.) I have another issue finding the correct file properties because different Windows OS have different locations for these properties (please see bottom of a related article www.kixtart.org/forums/ubbthreads.php?ubb=showflat&Number=160880&page=1).

One idea I had was to map to the correct locations depending on the Windows OS. Any ideas on how to get OS info with VBA? Please note, I'm about a 5 out of 10 on VBA, but know nothing of most other languages).

Thanks!
Brian

 

[bcooler]

ok, I found out the second part (via Google) by adding an Excel built in function to check the version...

Function GetOSfromExcel() As String
    Dim objXLApp As Object

    Set objXLApp = CreateObject("Excel.Application")
    GetOSfromExcel = objXLApp.operatingsystem
    objXLApp.Quit
    Set objXLApp = Nothing
End Function

I then compare this to known versions and then go from there. I guess I didn't realize I could call Excel functions from Access.

 

[SkipVought]

Please provide the ALL the code that your question is referring to. Neither your code nor the link lists anything regarding GetDetailsOf!!!

Try using the FileSystemsObject.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[bcooler]

My applogies for any confusion. The first link is just some background info. The 1st pile of code I posted does have "GetDetailsOf".

Also, thank you for the idea of FSO. I looked up how to use it and I am told that I need a reference to the scripting type library (Scrrun.dll). I am using this code on many computers and prefer not to add the scripting type library (Scrrun.dll) to everyone's computer (if I can help it).

 

[bcooler]

Sorry to keep piling it on, but I am now noticing with the above Excel function solution that MS Word documents with the ".docx" extension do not store many of these attributes. AGGHH! It looks like I'm going to have to abandon "GetDetailsOf". Any other suggestions?

 

[SkipVought]

I see NO CODE for the GetDetailsOf() function in ANY of this thread. I do see where you CALL GetDetailsOf, but that's no help!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[bcooler]

Um, GetDetailsOf is a method, not a function that I created (or at least copied from others)...??

[

http://msdn.microsoft.com/en-us/library/windows/desktop/bb787870(v=vs.85).aspx

 

[SkipVought]

Well in that case you have a 'black box' for which there are only guesses at what is happening.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[bcooler]

Anyone else have some ideas?

 

[strongm]

GetDetailsOf queries specific columns that can appear in Windows Explorer. Sadly, the numbering of these columns can differ slightly under different Windows OSs.

So firstly, yes for certain columns, user preferences will indeed make a difference. And secondly, yes using magic numbers will for some columns return the wrong info.

I gave some code in the VB6 forum in the past which allows you to query for a column's current magic number by it's name/label. Sorry can't provide links currently. Am browsing on a train from a smartphone


But, more importantly, it appears that you are actually querying a document's built-in properties. This is probably better done using the relevant Office library method

Finally, you would not have to install scrrun.dll. It has shipped as part of the OS for many years.

 

[bcooler]

Thanks for the info! I guess my comment about the scrrun.dll was that turning on this library may cause a lot of issues unless I can programmatically cut it on. There will always be that one person that will not cut it on no matter how many times I email, and then complain that it doesn't work. I also really don't want to go to hundreds of computers and turn on that reference. I will investigate add this programmatically.....

For the other comment, I'm not sure how to do the object library thing. I have Excel and word files mostly. Maybe a powerpoint here and there.

Thanks for the insight!

 

[strongm]

>turn on that reference

I guess I'm not understanding why they need to 'turn it on'. Unless you are simply providing some code that they are copying and pasting into existing code, and don't already have the reference made in the VBA project. In which case late bind.

 

[bcooler]

I'm sorry...my ignorance is showing through...

I'm really only familiar with cutting on a library manually from within the VBA editor (and try not to even do that). I assumed every computer that needs to run this library enhanced code would also need to do the same thing, but maybe the file carries this library on/off option with it and I don't have to cut it on for each computer?

 

[strongm]

>maybe the file carries this library on/off

There is no library on/off option. A (COM/ActiveX) library is either registered with the system or it is not. In general, only a COM/ActiveX DLL you download from a 3rd party source will need to be registered. Libraries shipped with the OS are already registered. New libraries added by applications are registered during installation.

Your app can happily use those libaraies without having to switch them on. You either early bind, which means the compiler knows about all about the library before compilation (and yes, your VBA program does compile, sort of) and this is normally achieved simply by adding a reference to the library in your project (Tools/References), or by late binding which means the compiler knows nothing about the library ahead of time and means you ahve to do absolutely nothing apart from explicitly creating objects directly from the library

Now, if you don't really know anything about this sort of stuff, how is it that you are using GetDetailsOf (which does rather take us back to Skip's question), which is a method from an ActiveX library?

In the meantime, here is my code for listing all the columns along with their magic number that the shell knows about for GetDetailsOf:

[blue]Public Sub ShellColumns()
    Dim i As Long
    Dim objShell As Object
    Dim objFolder As Object
    
    Set objShell = CreateObject("Shell.Application")
    Set objFolder = objShell.Namespace("C:\")
    
    Do Until objFolder.GetDetailsOf(objFolder.Items, i) = ""
        debug.? i, objFolder.GetDetailsOf(objFolder.Items, i)
        i = i + 1
    Loop
End Sub[/blue]

And here is an Excel example for listing all the builin document properties we can query:

[blue]Public Sub Example()
    Dim rw As Long
    rw = 1
    Dim prop As Office.DocumentProperty
    Worksheets(1).Activate
    For Each prop In ActiveWorkbook.BuiltinDocumentProperties ' Each office app has own BuiltinDocumentProperties
        Debug.Print rw, prop.Type
        Cells(rw, 1).Value = prop.Name
        rw = rw + 1
    Next
End Sub[/blue]

 

[bcooler]

Thank you for your quick response and examples! I believe my code is very similar to your first example.

To answer your question, "GetDetailsOf" is just something I found on Google after a LOT of searching. I don't really know much about it except it's no longer working for me.

So to summarize above, I believe it fails to meet my needs for 2 reasons:

1.) objFolder.GetDetailsOf(strFilename, 0) gets the "screen name" for the file. I need this to match the actual file name (with extension). Since the "screen name" may or may not have a file extension, I find it difficult to depend on.

2.) It seems that .docx (and maybe .xlsx) no longer have more than the 1st 7 attributes as part of objFolder.GetDetailsOf (only objFolder.GetDetailsOf(strFilename, 0) THRU objFolder.GetDetailsOf(strFilename, 7) exists in my testing). My online research tells me that .docx is basically a zip file, containing about 10 small files. One of these contains the metadata I want, but I don't know how to get to it and extract the needed info.

I will attempt to figure out "BuiltinDocumentProperties" or maybe FSO for my situation. Maybe this will be all I need.

Thanks again!

 

[strongm]

>I believe my code is very similar to your first example

Er, my first example delivers the NAMES of each of all the columns available for the shell, not the contents of a specific column for a specific file. Somewhat different, although it looks superficially the same.

 

[bcooler]

Ok, I figured out a little more of what is going on. It turns out (at least for me) that XP handles "GetDetailsOf" just fine for all files. However, Windows 7 does not handle the newer Office file extensions (.xlsx, .docx, .pptx, etc.) and only displays a few of the index numbers (0 though 6).

To work around it, I used StrongM's idea of "BuiltinDocumentProperties" for all the above file extensions, but continued with the GetDetailsOf method for all other files (including old Office .doc, .xls, etc). I did it this way because "BuiltinDocumentProperties" requires the file to be open, so I only use it when I have to.

I also got a lit of help from this link:

http://www.snb-vba.eu/VBA_Bestanden_en.html.

Finally, StrongM helped me realize that library references that are turned on as I develop the code remain on when I distribute it. For some reason, I thought I would have to turn on the references at each computer that used the file.

Here's a portion of the code if anyone's interested. Note you have to turn on the oject library for Excel, Word, and Powerpoint for it to work. I'm reading that late binding might be a good way to go also, but I didn't investigate enough to use it.

Also note that there is a lot of duplication in the Case structure. I felt like this was best because there are different ways to refer to the opened item (Documents, Workbooks, Presentations), and different ways to open as read only/close without prompting.

Finally, note that I used the above Excel function to determine if the system running the code is Win 7. If so, the structure of GetDetailsOf changes, so I change the index used to match.

    Dim arrHeaders(6)
    Dim db As Database
    Dim strTemp As String
    Dim colFolders As New Collection
    Dim vFolderName As Variant
    Dim strSQL As String
    Dim objShell As Object, objFolder As Object, objFolderItem As Object
    Dim wrdApp As Word.Application
    Dim wrdDoc As Word.Document
    Dim exlApp As Excel.Application
    Dim exlbook As Excel.Workbook
    Dim pptApp As PowerPoint.Application
    Dim ppPres As PowerPoint.Presentation
    'Dim filepath As String

    'Add the files to the folder.
    strPath = strFolder
    strFolder = TrailingSlash(strFolder)
    strTemp = Dir(strFolder & strFileSpec)
    Do While strTemp <> vbNullString
        gCount = gCount + 1
        SysCmd acSysCmdSetStatus, gCount
        Set objShell = CreateObject("Shell.Application")
        Set objFolder = objShell.Namespace(strPath)
        Set db = CurrentDb
        
        For Each strFilename In objFolder.Items
            If objFolder.GetDetailsOf(strFilename, 0) = strTemp Then
                Skip = False
                Select Case Right(strTemp, 4)
                    Case "DOCX", "DOCM", "DOTX"
                        Set wrdApp = CreateObject("Word.Application")
                        wrdApp.Visible = True
                        Set wrdDoc = wrdApp.Documents.Open(strFolder & strTemp, , True) 'Open the file
                        arrHeaders(0) = strPath & strTemp 'file path
                        arrHeaders(1) = objFolder.GetDetailsOf(strFilename, 0)
                        arrHeaders(2) = FixQuote(wrdDoc.BuiltinDocumentProperties("Title").Value) 'file name
                        arrHeaders(3) = FixQuote(wrdDoc.BuiltinDocumentProperties("Category").Value)
                        arrHeaders(4) = FixQuote(wrdDoc.BuiltinDocumentProperties("Comments").Value)
                        arrHeaders(5) = FixQuote(wrdDoc.BuiltinDocumentProperties("Author").Value)
                        wrdApp.Quit 0 'for MS Word, "0" prevents save prompting
                        Set wdDoc = Nothing
                        Set wdApp = Nothing
                    Case "XLSX", "XLSM", "XLTX"
                        Set exlApp = CreateObject("Excel.Application")
                        exlApp.Visible = True
                        Set exlbook = exlApp.Workbooks.Open(strFolder & strTemp, 0, True) 'Open the file
                        arrHeaders(0) = strPath & strTemp 'file path
                        arrHeaders(1) = objFolder.GetDetailsOf(strFilename, 0)
                        arrHeaders(2) = FixQuote(exlbook.BuiltinDocumentProperties("Title").Value) 'file name
                        arrHeaders(3) = FixQuote(exlbook.BuiltinDocumentProperties("Category").Value)
                        arrHeaders(4) = FixQuote(exlbook.BuiltinDocumentProperties("Comments").Value)
                        arrHeaders(5) = FixQuote(exlbook.BuiltinDocumentProperties("Author").Value)
                        exlApp.DisplayAlerts = False 'prevent save prompting
                        exlApp.Quit
                        exlApp.DisplayAlerts = True
                        Set exlbook = Nothing
                        Set exlApp = Nothing
                    Case "PPTX", "PPTM", "POTX"
                        Set pptApp = CreateObject("Powerpoint.Application")
                        pptApp.Visible = True
                        Set ppPres = pptApp.Presentations.Open(strFolder & strTemp, msoTrue) 'Open the file
                        arrHeaders(0) = strPath & strTemp 'file path
                        arrHeaders(1) = objFolder.GetDetailsOf(strFilename, 0)
                        arrHeaders(2) = FixQuote(ppPres.BuiltinDocumentProperties("Title").Value) 'file name
                        arrHeaders(3) = FixQuote(ppPres.BuiltinDocumentProperties("Category").Value)
                        arrHeaders(4) = FixQuote(ppPres.BuiltinDocumentProperties("Comments").Value)
                        arrHeaders(5) = FixQuote(ppPres.BuiltinDocumentProperties("Author").Value)
                        pptApp.Quit
                        Set ppPres = Nothing
                        Set pptApp = Nothing
                    Case Else 'using GetDetailsOf for all other file types
                        If GetOSfromExcel = "Windows (32-bit) NT 6.01" Then 'if running Win 7, the GetDetailsOf location is different....note this code only handles Win 7 and XP
                            Ttl = 21
                            Cat = 23
                            Comnt = 24
                            Auth = 20
                        Else
                            Ttl = 10
                            Cat = 12
                            Comnt = 14
                            Auth = 9
                        End If
                        arrHeaders(0) = strPath & strTemp 'file path
                        arrHeaders(1) = objFolder.GetDetailsOf(strFilename, 0)
                        arrHeaders(2) = objFolder.GetDetailsOf(strFilename, Ttl)
                        arrHeaders(3) = objFolder.GetDetailsOf(strFilename, Cat)
                        arrHeaders(4) = objFolder.GetDetailsOf(strFilename, Comnt)
                        arrHeaders(5) = objFolder.GetDetailsOf(strFilename, Auth)
                End Select

                s = " INSERT INTO tblFileProp " & "(FilePath, FileName, Title, AS9100, Comments, Author) VALUES " _
                    & "(""" & arrHeaders(0) & """,""" & arrHeaders(1) & """,""" & arrHeaders(2) & """,""" & arrHeaders(3) & """,""" & arrHeaders(4) & """,""" & arrHeaders(5) & """)"
                db.Execute s, dbFailOnError
            Exit For
            End If
        Next
        db.Close

Thanks to StrongM for giving me some hints on how to solve this one!