Solved

Setting a file's attributes without using Dsofile.dll

Posted on 2011-02-18
26
3,631 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 5
  • 3
  • +3
26 Comments
 
LVL 6

Expert Comment

by:richardburwood
ID: 34925458
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
ID: 34925695
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
ID: 34959043
My requirements have now changed in that we need to set these attributes on any file type that supports them - not just PDFs.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Author Comment

by:AL_XResearch
ID: 35005020
Does anyone have any other suggestions ?
0
 
LVL 3

Author Comment

by:AL_XResearch
ID: 35213579
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)
ID: 35214582
0
 
LVL 57
ID: 35214737
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
ID: 35214820
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
ID: 35216304
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
ID: 35216367
0
 
LVL 3

Author Comment

by:AL_XResearch
ID: 35216381
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
 
LVL 3

Author Comment

by:AL_XResearch
ID: 35216441
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
ID: 35220810
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
ID: 35226602
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)
ID: 35231073
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
ID: 35231090
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
ID: 35231661
<<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
ID: 35231829
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
ID: 35232006
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)
ID: 35232203
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
ID: 35232543
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
ID: 35232810
<<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
ID: 35304211
Although the solution from 'robberbaron' is interesting it does not solve my issue.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

735 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