Solved

Setting a file's attributes without using Dsofile.dll

Posted on 2011-02-18
26
3,387 Views
Last Modified: 2012-05-11
I am creating a system in Excel VBA for multiple users for which I need to set the extended attributes for a a file (e.g. the 'Comments' property of TIF, PDF files)

How can I do this with VBA ? Is there an API command I can use ?

All my searches of the Internet have found nothing except DSOFile.dll - which I do not want to use as this would require an install for each user of my system.

I have searched on Experts Exchange and have likewise found no other suggestions that DSOFile.dll.

There is FileSetAttr but this only sets the flags such as 'ReadOnly' and 'Archive'.

Any suggestions would be most appreciated.
0
Comment
Question by:AL_XResearch
  • 11
  • 5
  • 3
  • +3
26 Comments
 
LVL 6

Expert Comment

by:richardburwood
Comment Utility
With pure VBA this is not possible as the file metadata is stored differently depending on the file type.You can automate other programs to retrieve or possibly set the meta data using vbs.

See code below to get metadata using Cute PDF from a PDF file.


Set objMyForm = CreateObject("CutePDF.Document")
objMyForm.Initialize ("DEMO-SDK-84232865-00514228")
'Initialize object by serial number of the license
If objMyForm.openFile("Pdffilename.pdf") = False Then    	ErrorMessage = objMyForm.GetLastError()
End If
msgbox objMyForm.documentInfo("Title ")
msgbox objMyForm.documentInfo("Keywords")
msgbox objMyForm.documentInfo("author")
msgbox objMyForm.documentInfo("subject")

Open in new window

0
 
LVL 3

Author Comment

by:AL_XResearch
Comment Utility
Thanks richardburwood, however we don't want to install any other software and are using Adobe Distiller.

Really since you can right-click and set the properties of non-Office files in Windows UI there must be an equivilant API function.

You can use the 'Microsoft Shell Controls And Automation' command [.GetDetailsOf] to read attributes from ANY file but there is no corrisponding set command.
0
 
LVL 3

Author Comment

by:AL_XResearch
Comment Utility
My requirements have now changed in that we need to set these attributes on any file type that supports them - not just PDFs.
0
 
LVL 3

Author Comment

by:AL_XResearch
Comment Utility
Does anyone have any other suggestions ?
0
 
LVL 3

Author Comment

by:AL_XResearch
Comment Utility
I am sorry of this has caused an issue, but I am waiting to see if there will be any further contributors to this question. How can I either re-publise this question to alert all users to an outstanding question or close the question as un-resolved ?

Thanks
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
Just found this:

http://www.codeproject.com/KB/files/SummInfoPropSetFile.aspx

 Which may be helpfull to point you in the right direction, but it is in C++

JimD.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
Well here's some code in VBA for setting a revision number on an Excel spreadsheet, so this is along the right lines:

http://us.generation-nt.com/how-do-i-retrieve-entire-revision-number-excel-2007-help-144990531.html

 Might take a little bit of work to figure it out, but it should be doable between the two links I posted.

JimD.
0
 
LVL 38

Expert Comment

by:puppydogbuddy
Comment Utility
see if this link to win api for file management helps:
          http://msdn.microsoft.com/en-us/library/aa364232(v=VS.85).aspx
0
 
LVL 38

Expert Comment

by:puppydogbuddy
Comment Utility
0
 
LVL 3

Author Comment

by:AL_XResearch
Comment Utility
Thank you 'fyed' for your repsonse. Unfortuantly the FileSystemObject is only capable of setting the 'Read Only', 'Hidden' and 'Archive' type attributes of a file. I need to be able to set the summary properties of a file such as 'Comments', 'Author' and 'keywords'.

So far all I can see without amending the target file at the binary level the only methid is the DSOFile.dll solution.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 3

Author Comment

by:AL_XResearch
Comment Utility
Thank you 'puppydogbuddyDate' for you post today @ 3:41 but unfortunatly these API functions are only capable of modifying standard file attributes such as 'Read Only', 'Hidden' and 'Archive'.
0
 
LVL 32

Assisted Solution

by:Robberbaron (robr)
Robberbaron (robr) earned 250 total points
Comment Utility
1/ it can be done using Delphi.  In fact I have found that i needed to use a tiny delphi app to set an summary property before i could use DsoFile.  
// http://www.delphi-central.com/tutorials/File_Summary.aspx -----------
//  Serge Perevoznyk
//---------------------------

2/ and the Delphi app can be stored on a network drive and run from each connected workstation.
3/ dsofile can be installed using a startup script, though 64bit is a problem to install.

4/ the file MUST be on an NTFS disc for this to work.  copy it to a CD and the summaryproperties are lost.
0
 
LVL 3

Author Comment

by:AL_XResearch
Comment Utility
Thank you 'robberbaron' - unfortunately there is no possibility of developing an application in Deplhi, I don't know the language and one of the pre-requisites of this system development is a zero install requirement (which makes system development in Excel ideal). Although DSOFile.dl will need to be registered on all user's PC this is the limit of per-user customisation I am afraid.
0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
Comment Utility
1/ zero -install.... I have used a small app stored and executed from a shared drive so no user install needed.  
2/ yes, i had to get a friend to compile my delphi app for me. but it is very simple.

3/ if you can get DSOfile installed, that will work.  There is no way within Excel VBA to write direct to the SummaryProperties stream. needs C++ (ie dsofile) or Delphi.

4/ there are 64bit versions of DSOFile available but you probably dont need them from VBA (i think it is always 32bit)
0
 
LVL 3

Accepted Solution

by:
AL_XResearch earned 0 total points
Comment Utility
I was hoping that there was an API way in which to modify the summary details - but it does not appear so. I will have to stick to using DSOFile.dll.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<I was hoping that there was an API way in which to modify the summary details - but it does not appear so. I will have to stick to using DSOFile.dll. >>

?? The links I posted has code only using oleaut32...

JimD.
0
 
LVL 3

Author Comment

by:AL_XResearch
Comment Utility
Hi 'JDettman' - thank you for your contributions. Yes I did look at the link you posted but a) it appears too complicated for this task b) nearly all the reference material for 'StgOpenStorageEx' is written for C or it's variants (which it would take too long to translate) and c) as this uses OLE I would say that it is basically the same as Micorosoft's DSOFile.dll except that it is low level, would require more work / testing and does not provide such a clean interface / set of commands for accessing a file's properties.

Indeed the following artical on this site also reaches the same conclusion as I have; that DSOFile.dll is really the only viable VB alterantive.
http://www.experts-exchange.com/Programming/Languages/Visual_Basic/Q_20555228.html

I will certainly keep your link in mind though for the future.

Thanks again.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
With respect,

<<a) it appears too complicated for this task >>

  It's not my fault that what you asked for is complicated.

<<b) nearly all the reference material for 'StgOpenStorageEx' is written for C or it's variants (which it would take too long to translate)>>

  The second link is in VBA and shows setting the revsion property on an Excel file.

<< c) as this uses OLE I would say that it is basically the same as Micorosoft's DSOFile.dll except that it is low level, would require more work / testing and does not provide such a clean interface / set of commands for accessing a file's properties. >>

   Same maybe, but you asked for a method that uses something other then DSOFile.dll, which is what I found.  Again, it's not my fault that what your attempting to do needs a low level solution and is more complicated as a result.

  I'm not asking that this question be closed differently, but please keep in mind that when you ask a question in the future, it's not the fault of the expert if you don't like the solution.

JimD.

0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
Comment Utility
that Excel workbook method posted by JDettman works well.   uses Ole32 & other win apis.

note that the other PropertyId values are found at http://207.46.16.248/en-us/library/dd942089%28PROT.13%29.aspx
PIDSI_COMMENTS = &H6

i have changed the code to successfully write the subject data. could easyly be moded to write a listed parameter.

 I will be looking at it further for my own use, but i currently use UserProperties via dso so dont know if this method works for those.

0
 
LVL 3

Author Comment

by:AL_XResearch
Comment Utility
JDettman : I apologise if you took my previous post the wrong way. I was not implying that it was your fault it was complicated or that the links you supplied were not useful. It is just that for this solution I don't have the time for developing & testing complex code and since I am not familiar with those functions I would not be confident releasing such a solution until I have thoroughly understood, tested and researched that method.

I have to admit that I didn't initially see that the VBA revision number updating code used the same function as the C++ code. My apologies for that.

As I said in my earlier post today, although I was hoping for a relatively straight forward API call with no install, I will use the DSOFile approach. Additionally my comment about the OLE code and DSOFile appearing functionally the same was not meant as a criticism - just a observation that if Microsoft have taken the time to develop and test a solution to simplify this task, that presumably uses the same techniques as your link, then it would seem counterproductive for me to invest the time to reproduce their work and make the code more complicated.

I would never imply that an expert was at fault for suggesting a complex solution, all solutions are valuable and there are very few solutions that are not complex.  

Once again I am sorry for any misunderstanding.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<Once again I am sorry for any misunderstanding.>>

  No problem here.  I was just pointing some things that might be helpful down the road with other questions.

JimD.
0
 
LVL 3

Author Closing Comment

by:AL_XResearch
Comment Utility
Although the solution from 'robberbaron' is interesting it does not solve my issue.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now