PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
read buildinproperties in xlsm-files-VBForums
Results 1 to 14 of 14

Thread: read buildinproperties in xlsm-files

  1. #1

    Thread Starter
    New Member
    Join Date
    Jan 2018
    Location
    Germany - Westerwald
    Posts
    10

    Question read buildinproperties in xlsm-files

    Hi,
    always a happy new year!

    To read the buildinproperty "category" of xls-files we use the DSOFile API. For example
    Set oDocProp = CreateObject("DSOFile.OleDocumentProperties")
    sKategorie = oDocProp.SummaryProperties.Category

    But the DSOfile api can't read the XLSM-Files (Microsoft Excel Open XML Spreadsheet)
    What is the easiest way read this buildinproperty of xlsm-files?
    I would read this information without open the file with the Excel-application api becose this used to much time.

    rawa

  2. #2
    Frenzied Member wqweto's Avatar
    Join Date
    May 2011
    Posts
    1,801

    Re: read buildinproperties in xlsm-files

    You can try to download cZipArchive.cls and use something like this

    thinBasic Code:
    1. Option Explicit
    2.  
    3. Private Sub Form_Load()
    4.     Caption = "Category: " & pvGetXlsmCategory("D:\TEMP\aaa.xlsm")
    5. End Sub
    6.  
    7. Private Function pvGetXlsmCategory(sXlsmFile As String) As String
    8.     Dim baBuffer()      As Byte
    9.     Dim sXml            As String
    10.     Dim oNode           As Object
    11.    
    12.     baBuffer = vbNullString
    13.     With New cZipArchive
    14.         .OpenArchive sXlsmFile
    15.         .Extract baBuffer, "docProps/core.xml"
    16.         sXml = .FromUtf8Array(baBuffer)
    17.     End With
    18.     With CreateObject("MSXML2.DOMDocument")
    19.         .loadXml sXml
    20.         For Each oNode In .selectNodes("//cp:category")
    21.             pvGetXlsmCategory = oNode.Text
    22.             Exit For
    23.         Next
    24.     End With
    25. End Function
    cheers,
    </wqw>

  3. #3
    Fanatic Member
    Join Date
    Feb 2003
    Posts
    735

    Re: read buildinproperties in xlsm-files

    Quote Originally Posted by rawa View Post
    Hi,
    always a happy new year!

    To read the buildinproperty "category" of xls-files we use the DSOFile API. For example
    Set oDocProp = CreateObject("DSOFile.OleDocumentProperties")
    sKategorie = oDocProp.SummaryProperties.Category

    But the DSOfile api can't read the XLSM-Files (Microsoft Excel Open XML Spreadsheet)
    What is the easiest way read this buildinproperty of xlsm-files?
    I would read this information without open the file with the Excel-application api becose this used to much time.

    rawa
    Can you provide more code and an example *.XLSM file demonstrating the issue? This would make it easier to help you.

    EDIT:
    Never mind, I see someone answered just before I clicked the submit button.

  4. #4
    PowerPoster
    Join Date
    Feb 2006
    Posts
    20,922

    Re: read buildinproperties in xlsm-files

    I'm not sure why this question comes up over and over again. It has been answered many times. People don't seem to read or search the forums.

    In any case we've had pretty robust support for things like these Extended Properties for quite a long time, and even the relatively primitive support in Windows XP was dramatically expanded beginning in Windows Vista.

    Code:
    Option Explicit
    
    Private Sub Form_Load()
        Const ssfDESKTOP = 0
        Dim Row As Long
        Dim Folder As Object
        Dim ShellFolderItem As Object
        Dim Categories As Variant
    
        With MSHFlexGrid1
            .TextMatrix(0, 0) = "File"
            .ColWidth(0) = 2100
            .TextMatrix(0, 1) = "Category"
            .ColWidth(1) = 2400
            With CreateObject("Shell.Application").NameSpace(ssfDESKTOP).ParseName(App.Path)
                Set Folder = .GetFolder
            End With
            For Each ShellFolderItem In Folder.Items
                With ShellFolderItem
                    If Not (.IsFolder Or .IsLink) Then
                        If .Name Like "*.xls*" Then
                            Row = Row + 1
                            If Row > MSHFlexGrid1.Rows - 1 Then
                                MSHFlexGrid1.Rows = Row + 1
                            End If
                            MSHFlexGrid1.TextMatrix(Row, 0) = .Name
                            Categories = .ExtendedProperty("System.Category")
                            If Not IsEmpty(Categories) Then
                                MSHFlexGrid1.TextMatrix(Row, 1) = Join$(Categories, "; ")
                            End If
                        End If
                    End If
                End With
            Next
            If Row > 1 Then .Rows = Row + 1
        End With
    End Sub
    
    Private Sub Form_Resize()
        If WindowState <> vbMinimized Then
            MSHFlexGrid1.Move 0, 0, ScaleWidth, ScaleHeight
        End If
    End Sub
    Name:  sshot.png
Views: 81
Size:  2.0 KB

    No need for any code injection, no need to deploy additional dependencies.

  5. #5
    Frenzied Member wqweto's Avatar
    Join Date
    May 2011
    Posts
    1,801

    Re: read buildinproperties in xlsm-files

    Quote Originally Posted by dilettante View Post
    I'm not sure why this question comes up over and over again.
    Probably the sample code is hard to decipher and needlessly depends on grid controls.

    Here is a simplified version

    thinBasic Code:
    1. Private Function pvGetXlsmCategory2(sXlsmFile As String) As String
    2.     Dim vProp           As Variant
    3.    
    4.     With CreateObject("Shell.Application").NameSpace(0).ParseName(sXlsmFile)
    5.         vProp = .ExtendedProperty("System.Category")
    6.     End With
    7.     If IsArray(vProp) Then
    8.         pvGetXlsmCategory2 = Join(vProp, ";")
    9.     End If
    10. End Function
    Not tested on XP but should work on Win7 and above.

    Edit:
    Quote Originally Posted by dilettante View Post
    No need for any code injection, no need to deploy additional dependencies.
    Oh, I'm seeing this funny remark after submitting. . . There is no code injection in this thread and the only external dependency is the MSHFlexGrid1 your sample is referencing.

    FYI, I'm using posts like this to polish ZipArchive project with small touches here and there -- e.g. an optional param to specify the level of compression per file or a utf8 conversion method made public in latest commit. It's coming pretty rounded swiss-army-knife kind of class already.

    cheers,
    </wqw>

  6. #6
    PowerPoster
    Join Date
    Feb 2006
    Posts
    20,922

    Re: read buildinproperties in xlsm-files

    The IsArray() test is a little bizarre since the property value is always either Empty or a Variant String Array.

  7. #7
    PowerPoster
    Join Date
    Feb 2006
    Posts
    20,922

    Re: read buildinproperties in xlsm-files

    BTW:

    Category is listed as deprecated with Keywords being preferred. MS Office calls Keywords "Tags" in many (all?) versions that support it. Both Category and Keywords are "Core" system properties, but there are many more that apply to various types of files.

    You can find the list in the Windows SDK header file propkey.h or at Windows Properties. The header file contains a little more specific information about returned data types however.

  8. #8
    Frenzied Member wqweto's Avatar
    Join Date
    May 2011
    Posts
    1,801

    Re: read buildinproperties in xlsm-files

    Quote Originally Posted by dilettante View Post
    The IsArray() test is a little bizarre since the property value is always either Empty or a Variant String Array.
    I was considering Not IsEmpty but then Join would fail when someone "upgrade" the logic to return a bare string here (when the array has a single element for instance). Old habits of in-depth defensive programming to always "trust" fellow developers providing APIs for us to use. . .

    Anyway, probably this is the full version I skipped for brevity:

    thinBasic Code:
    1. '--- try hard not to fail on NULLs too
    2.     If IsArray(vProp) Then
    3.         pvGetXlsmCategory2 = Join(vProp, ";")
    4.     Else
    5.         pvGetXlsmCategory2 = vProp & vbNullString
    6.     End If
    cheers,
    </wqw>

  9. #9
    PowerPoster
    Join Date
    Feb 2006
    Posts
    20,922

    Re: read buildinproperties in xlsm-files

    The only reason you might join the String array is to display the information such as I did to in my sample.

    Normally you would not do that because this property is far more useful in trying to find files that match on one or more "category" values. These are distinct values and nothing guarantees their order, so you pretty much have to iterate the array to find matches. Concatenating them basically destroys their value except for anything but trivially displaying them.

    In the end your wrapper function adds overhead and destroys value. It is more misleading than useful.


    Actually you are probably better off using Windows Search for this sort of thing rather then a lot of procedural logic that traverses the directory testing every file. For array properties like Category there is a special SQL syntax in Windows Search:

    Multi-Valued (ARRAY) Comparisons

  10. #10
    Frenzied Member wqweto's Avatar
    Join Date
    May 2011
    Posts
    1,801

    Re: read buildinproperties in xlsm-files

    Quote Originally Posted by dilettante View Post
    In the end your wrapper function adds overhead and destroys value. It is more misleading than useful.
    It is useful for assigning the value to the string variable sKategorie in OP.

    The overhead is with the grid control you bolted on your sample code.

    And btw, what is Join$? This would be an example of misleading function name as there is no "string version" of Join.

    cheers,
    </wqw>

  11. #11
    PowerPoster
    Join Date
    Feb 2006
    Posts
    20,922

    Re: read buildinproperties in xlsm-files

    Wow, so... what? You think a better example would have been Debug.Print instead? How does the approach used to display the information have anything to do with how to obtain it?

  12. #12
    Frenzied Member wqweto's Avatar
    Join Date
    May 2011
    Posts
    1,801

    Re: read buildinproperties in xlsm-files

    Quote Originally Posted by dilettante View Post
    Wow, so... what? You think a better example would have been Debug.Print instead?
    There are already 2 lines of code in OP what they currently do. There is no mentioning of grid controls. . . and they might really just Debug.Print sKategorie.

    Read the code, Luke! :-))

    cheers,
    </wqw>

  13. #13

    Thread Starter
    New Member
    Join Date
    Jan 2018
    Location
    Germany - Westerwald
    Posts
    10

    Re: read buildinproperties in xlsm-files

    Hi dilettante, thx for your patient with a little programmer. My code-knowledge is just enough to digging on the surface.
    Hi wqweto, thx also to you. You are absolutely right that with just Debug.Print sKategorie. :-)
    I choose the shell-way about it is a little bit faster then the zip-way.
    Therefore, thx you all and by
    rawa

  14. #14

    Thread Starter
    New Member
    Join Date
    Jan 2018
    Location
    Germany - Westerwald
    Posts
    10

    Resolved Re: read buildinproperties in xlsm-files

    Hi dilettante, thx for your patient with a little programmer. My code-knowledge is just enough to digging on the surface.
    Hi wqweto, thx also to you. You are absolutely right that with just Debug.Print sKategorie. :-)
    I choose the shell-way about it is a little bit faster then the zip-way.
    Therefore, thx you all and by
    rawa

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width