Setting extended property values for external files using VBA (1 Viewer)

[isladogs]

Recently I was contacted by someone who had seen my example database on extended file properties
https://www.access-programmers.co.uk/forums/showthread.php?t=294548

They had a follow up question.
Is it possible to set properties for external files using Access VBA?

After a little bit of digging I found the answer is a partial YES.
1. You need to install the file dsofile.dll as a VBA reference library
2. Properties can be edited provided the extended property exists
3. New properties cannot be created AFAIK

NOTE:
I couldn’t find dsofile.dll on my system but downloaded it from
[url]https://www.microsoft.com/en-us/download/confirmation.aspx?id=8422 [/URL]
and copied it to c:\windows\system32 folder then registered it using regsvr32
I then added this as a VBA reference – its DSO OLE Document Properties Reader 2.1

So for example, using Access VBA, the Comments property can be written to for external docx & xlsx files
However I believe it can't be done for accdb or txt files which do not have this property.
MP3 files have lots of properties which can be written to

This appears to be different to internal database properties where new properties can of course be created

However I may be missing something obvious - hence this post

The attached database is very much work in progress.
I have included dsofile.dll (as an EXE file to install) and some sample files to play with

When a file is selected, the specified folder opens in form view
Right click to change to details view so the property values can be viewed



I would be grateful for any ideas to take this further .....
INCLUDING being able to open explorer directly in details view in a web browser window using VBA

 

[isladogs]

Just giving this a 'bounce' in case anyone can assist.

 

[D_Walla]

Hi
I know that you posted this some time ago, and that you've likely moved on from it, but I just wanted to thank you for posting the project. I have had a horrible time trying to find any information about this (specifically for VBA) until I came across your post. I was familiar with the dsofile.dll through Chip Pearson's blog but hadn't realised it could be used for non-MS office files.

I've had a look at the code, and have found it extremely helpful. One thing I noted is that you said that we can't set the property values for Publisher files, for example, and found that really odd. So I tried doing it through your code in one of the test subs, and tried the form again and sure enough - no changes the values were made. When you check in the details view of explorer too or even the properties dialog box, not only have there been no changes, there are no extended properties listed at all or even the option of adding any custom properties... which is strange for an MS file (I thought). However.... it turns out that the properties are being set. I opened up Publisher and there they were - the properties I set with your code. I just find it all really bizarre. Just thought I'd report back on that point.

Second, you may already have arrived at an answer on this, but for the benefit of anyone else asking the same question, I've managed to find out that you can set the webbrowser to Details view with: Me.WebBrowser1.Document.CurrentViewMode = 4
The other options appear to be:

1 = Icons
2 = Small icons
3 = List
4 = Details
5 = Thumbnails
6 = Tile
7 = Thumbnail strip

Thank you again.

 

[isladogs]

@D_Walla
Many thanks.
I haven't looked at this in a long time and can't remember whether I took it any further. Will have another look at the code and post back if I have anything new to add. Will also add it to my website

 

[Uncle Gizmo]

However I may be missing something obvious - hence this post

I'm sure you are not missing anything! I suspect you already know that you can create a custom property for a database:-

I thought I should mention it, just in case ....

I have some sample code if you need it.

 

[Pat Hartman]

I don't have time to try out the database now. Did you ever try using FSO to change the extended properties?

 

[isladogs]

@D_Walla

One thing I noted is that you said that we can't set the property values for Publisher files, for example, and found that really odd. So I tried doing it through your code in one of the test subs, and tried the form again and sure enough - no changes the values were made. When you check in the details view of explorer too or even the properties dialog box, not only have there been no changes, there are no extended properties listed at all or even the option of adding any custom properties... which is strange for an MS file (I thought). However.... it turns out that the properties are being set. I opened up Publisher and there they were - the properties I set with your code. I just find it all really bizarre. Just thought I'd report back on that point.

Please can you clarify this for me. Which properties did you manage to set for Publisher and how did you see them in Publisher?
Also, did you have any success using this code to set properties for external ACCDB files?

I've now added the line Me.WebBrowser0.Document.CurrentViewMode = 4 to my code (though intellisense doesn't show that). The first file selected opens the Explorer window in detail view but if a different file is then selected, it reverts to the defaULT icon view

 

[isladogs]

@Uncle Gizmo ; @Pat Hartman
Yes, I'm well aware that you can create and edit database properties using VBA and do so regularly
The important point in the original post was this line:

Is it possible to set properties for external files using Access VBA?

The purpose of the thread was to test the dsofile.dll code which was based on Chip Pearson's article.
Whilst a huge amount can be done with FSO including obtaining various properties of files & folders, as far as I'm aware you cannot use it to set the extended properties of external files.
But if I'm wrong, do let me know

 

[D_Walla]

I checked the properties point just now. I had originally set the Title and Keywords properties, but to double check, I also wrote to the Manager, Company and Comments properties just now. I was able to access these properties by loading the PublisherTest file you provided, and selecting the Info tab in the Backstage area > Publication Properties, I was presented with this dialog box:

I cannot, however, see this data in Explorer in the additional corresponding columns (though I can't find the columns for Keywords or Manager in the settings), nor can I see it when I select the Properties button in Explorer either. Only when I look at it within Publisher.

As for ACCDB, they don't appear to be getting set at all. I tried running the same code over a test ACCDB file, and nothing.

As for the Details view, I will look into that tomorrow morning, and will try and find the website where I found that information.

 

[isladogs]

Hi
Just tested and I have also managed to edit properties for the Publisher file though these aren't listed in the app or in Explorer.
I still can't manage to set the properties for an ACCDB file

 

[D_Walla]

I just find it really odd that MS would make the setting of these extended properties:
(a) such a convoluted process (requiring an external DLL that is extremely difficult to find - for 64-bit, at least);
(b) seemingly impossible (for ACCDB, at least).

As for the Webbrowser settings, the best I've been able to come up with is something of a hacky workaround, but it requires setting the CurrentViewMode in the WebBrowser control's DownloadComplete event:

Private Sub WebBrowser1_DownloadComplete()
    Me.WebBrowser1.Document.CurrentViewMode = 4
End Sub

Although not ideal, from what I can see, it's pretty seamless. I've only checked this on a userform in Excel because I don't have Access on my work computer, but I would expect it (perhaps foolishly) to work the same.

 

[isladogs]

Hi
I agree with points a) & b)

I wasn't aware of the DownloadComplete event & although it does work. it doesn't seem relevant to changing the selected file'
However, before you posted I'd added exactly the same code to the Updated event & that also works

Private Sub WebBrowser0_Updated(Code As Integer)
    Me.WebBrowser0.Document.CurrentViewMode = 4
End Sub

However, there is still the problem that each file change removes any additional File Explorer columns such as Authors, Comments etc from the browser control. If this is going to have any real value, the browser settings need to be done in code and also 'stick'. I haven't looked into that as yet.

 

[D_Walla]

Ah I see. Unfortunately, I don't have my personal laptop to hand (therefore, no Access) so wasn't able to check whether or not my DownloadComplete approach worked when changing the properties with your program. I can check when I get home, but I'm afraid I don't know off the top of my head. The last time I looked at this was a few years ago, and even then it was a bit of a superficial 'investigation' - I had inadvertently managed to change the default size of the icons and was never quite sure how to change it back. With that in mind, given that I had that level of customisation available, I would think that it would be possible to add property columns to the display, but who knows really...

 

[D_Walla]

I'm very sorry - I thought I had posted an update earlier in the week.
What I thought I had sent was items 2 and 3 below, but they were since overtaken with the following, far more relevant point. It's not a short note, but I thought I'd give you more detail on the off-chance it might help get to the solution quicker:

1. It seems that the answer lies in the IColumnManager interface. According to MSDN:

[The IColumnManager Interface] exposes methods that enable inspection and manipulation of columns in the Windows Explorer Details view. Each column is referenced by a PROPERTYKEY structure, which names a property.
The IColumnManager interface inherits from the IUnknown interface.

Specifically, it provides GetColumns / SetColumns methods (including Get/SetColumnInfo methods) which gets/sets the collection of columns for the view to display.

The VB6 Solution
I checked to see whether the VB6 community have a method of engaging the IColumnManager interface - it turns out that they do: here. The solution is in the form of a TypeLibrary. Of the dozens of sample projects listed, two are on-point: ucShellBrowse (updated on 22/2/22) and a Project display search results.

I have nearly zero experience with installing and dealing with non-standard TypeLibraries, but I note that the author (Fafalone) says that the TypeLibrary contains API Declarations, etc. A forum user who develops in VBA asked back in 2015 if fafalone could recompile the TLB in 64bit for use in Office. Fafalone responded:

Edit: So I installed 64-bit office, and definitely need more info about what part of the typlibs won't work. Note that olelib includes API calls, but I've had problems with these everywhere and strongly suggest always using your own declares in a module.
This is what I tried, and it worked fine with the current versions. Note that IShellItem.GetDisplayname returns a pointer, but VBA doesn't know that thus the LongPtr type isn't needed. If you needed it down the road, there apparently is a CLngPtr() function built in.

There is quite a lot of back-and-forth, and I haven't had time to bottom it out yet, but I thought I should send you this information to let you know where I'm up to - and perhaps you know and answer to some of these points? We've long since passed over the limits of my knowledge and skill! That said, the discussion between Fafalone and the VBA developer looks promising (and if nothing else, I'm finding it educational!).

-----------------------
For completeness, I thought I'd include my other two pieces of information.

2. Although the ClassID for the WebBrowser control/shell explorer is "Shell.Explorer.2", it seems that if you generate the control programmatically at runtime (or even at design time) using the .Controls.Add("Shell.Explorer.2","MyControlName") method, the Details view mode only needs to be set once.

3. The WebBrowser.Document object also provides a .SortColumns method, which helpfully revealed the property system names. For example, title = "prop:System.Title". While it is possible to the set the sorting column with this method/property, I haven't yet worked out how to get over the threshold issue of being able to programmatically display the columns! I set out below a basic script that demonstrates this point using an Excel Userform. It stops just before sorting to let you manually add a title column (if you wanted to see it work):

Sub OpenShellExplorer()
Dim WB As Object
Dim ShExp As Object
Set Wb = Me.Controls.Add("Shell.Explorer.2","MyName")
Set ShExp = WB.Document
ShExp.CurrentViewMode = 4
Debug.Print ShExp.SortColumn
Stop
' Manually add the Title column
ShExp.SortColumn = "prop:System.Title;"
End Sub

If you look at the properties of the ShExp object, you'll also see ViewOptions. This appeared potentially useful, but is a read-only property that returns certain of the view settings as set out in the ShellFolderViewOptions enumeration.

 

[D_Walla]

Did you manage to solve this?

 

[isladogs]

Hi
I'd completely forgotten about this thread & indeed don't remember ever seeing your post #14.
So, the simple answer is I haven't looked at this in at least a year ... nor did I ever put it on my website

Will try to review in the near future ...but won't make any promises

 

[D_Walla]

Happy New Year!
Well I have made some progress, and narrowing in on a solution - it'll manner of API chicanery, but my skills in this area have improved. The author of the ucShellBrowser (and of the TaskDialog class) has been active in converting his code over to be TwinBasic compatible. He has recently converted Shell-related project (here - Work-In-Progress), so was going to turn my attention to this and update you if you hadn't already arrived at the solution.

 

[isladogs]

Hi
Thanks. Do keep me informed
I haven't done much with twinBasic yet but I know fafalone is very actively involved.
Is that an alias used by Ben Clothier ?

 

[D_Walla]

Hi. I'm very sorry - I didn't see that you had responded - I have been busy moving, and it isn't nearly as much fun as people would have you believe! From what I can see, it isn't Ben's alias, no. Ben uses the clever secret alias bclothier... Fafalone has his name and contact email set out in the header comment section of his cTaskDialog class module. For your reference, Fafalone has published his own version of the TaskDialog class (link), but last I checked there were some issues with the VBA 64bit version. I will need to check it again when I get a chance.