PDA

View Full Version : File properties using FSO and Dir



Indigenous
01-16-2018, 02:18 AM
Does anyone have an example which uses both FSO and Dir to list files and their properties (such as Title and Subject)?
Is it acceptable to do so?

offthelip
01-16-2018, 03:39 AM
I have written this workbook which allows the user to search for any files which include the entered string and it also allows the user to select which file attributes you want displayed. I use this worikbook instead of the Microsoft, indexing and search facility which I find totally unsatisfactory because it slows the computer down all the time and isn't a proper search facility. ( I disable it on my computers)
What is also much better is you end up with a excel worksheet with all the details which then allows me to, move or delte files using vBA
to use it:
There are two worksheet, when you select the control sheet,. a userfomr pops up which ask you for a string ( leave it blank if you want) it will then ask you to browse for a directory which is the starting point for the search. It searches all the directories under that .
I hope this helps

Indigenous
01-21-2018, 07:12 AM
Hi
Thanks for sending this.
Do you know how to look for "Title" and "Subject"?

offthelip
01-21-2018, 07:23 AM
Title and Subject are "properties" of a Microsoft Office file. They are not "general" file properties, e.g a .jpg file will have different properties or metadata.
FSO will only find the file attributes which are general to all files types which I think are all the ones which my file accesses. I suspect that you are going to have to open the document to get access to those properties, however somebody else might know better!!

Indigenous
01-21-2018, 07:49 AM
OK thanks for your input.

Paul_Hossler
01-21-2018, 08:14 AM
I think you can get the properties using FSO without opening the document

However, the property field number changes from O/S version so you have to search for the field number (faGetFieldNumber) first, and then get the property (faGetFileProperty)

My cheat sheet is also in the attachment




Option Explicit

Sub drv()
MsgBox faGetFileProperty("C:\Users\Daddy\Documents\PropertiesTest.xlsm", "subject")
MsgBox faGetFileProperty("C:\Users\Daddy\Documents\PropertiesTest.xlsm", "Title")
End Sub


Function faGetFileProperty(sFilename As String, sProperty As String) As Variant
Dim iProp As Long
Dim oFolder As Object, oFolderItem As Object
Dim iFieldNumber As Long
Dim sFolder As String, sFile As String

faGetFileProperty = vbNullString
On Error GoTo NiceExit
iProp = faGetFieldNumber(sProperty)

If iProp = -1 Then Exit Function
With CreateObject("scripting.FileSystemObject")
sFolder = .GetParentFolderName(sFilename)
sFile = .GetFileName(sFilename)
End With

Set oFolder = CreateObject("shell.application").Namespace(sFolder & "\")
Set oFolderItem = oFolder.parsename(sFile)

faGetFileProperty = oFolder.GetDetailsOf(oFolderItem, iProp)
NiceExit:
End Function


Private Function faGetFieldNumber(s) As Long
Dim oFolder As Object
Dim n As Long
Dim sDesktop As Variant



sDesktop = CreateObject("wscript.shell").specialfolders(10) & Application.PathSeparator
Set oFolder = CreateObject("shell.application").Namespace(sDesktop)

On Error GoTo Oops

For n = 0 To 999
If LCase(s) = LCase(oFolder.GetDetailsOf(oFolder.Items, n)) Then
faGetFieldNumber = n
Exit Function
End If
Next n
Oops:
Set oFolder = Nothing
faGetFieldNumber = -1

End Function

snb
01-21-2018, 10:20 AM
For more on this, see:

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

Indigenous
01-22-2018, 03:00 AM
@Paul_Hossler, thanks for the sheet, I was aware of this issue though.
@snb, I have seen this link before.

No-one has yet to advise if Dir is able to extract the Title. Should I assume FSO is the only method I can use?

For info, my issue is trying to read the Title for extensions such as DOCX and XLSX. For some reason it won't work for me. I queried in a post from 2016 (http://www.vbaexpress.com/forum/showthread.php?57202-objFolder-GetDetailsOf-not-working). It works for PDF but not for DOCX and XLSX.

snb
01-22-2018, 04:12 AM
I don't understand what you want to communicate here:


For info, my issue is trying to read the Title for extensions such as DOCX and XLSX. For some reason it won't work for me. I queried in a post from 2016 (http://www.vbaexpress.com/forum/show...Of-not-working (http://www.vbaexpress.com/forum/showthread.php?57202-objFolder-GetDetailsOf-not-working)). It works for PDF but not for DOCX and XLSX.

Indigenous
01-22-2018, 04:34 AM
With the code in the link in post #8, which uses FSO, I was unable to read the Title property for files with extension DOCX and XLSX.
That's why in my OP I wanted to clarify if Dir and FSO can be used together to achieve this. Why together? I don't really know. I'm just trying to avoid Dir by itself because I don't fully understand it and just to keep as much of the code that I have.

Paul_Hossler
01-22-2018, 07:44 AM
@Paul_Hossler, thanks for the sheet, I was aware of this issue though.
@snb, I have seen this link before.

No-one has yet to advise if Dir is able to extract the Title. Should I assume FSO is the only method I can use?

For info, my issue is trying to read the Title for extensions such as DOCX and XLSX. For some reason it won't work for me. I queried in a post from 2016 (http://www.vbaexpress.com/forum/showthread.php?57202-objFolder-GetDetailsOf-not-working). It works for PDF but not for DOCX and XLSX.


My FSO macro does retrieve Title and Subject of DOCX files:



MsgBox faGetFileProperty("C:\Users\Daddy\Documents\Lorem ipsum dolor sit amet.docx", "subject") & vbCrLf & vbCrLf & _
faGetFileProperty("C:\Users\Daddy\Documents\Lorem ipsum dolor sit amet.docx", "Title")


21423


1. I'm assuming that you actually have a Subject and Title on the DOCX or XLSX documents? Word and Excel do not add them automatically, so you must have entered them yourself

2. Why would you expect the Dir() function to be able to extract the Title or anything else?


Returns a String representing the name of a file, directory, or folder that matches a specified pattern or file attribute, or the volume label of a drive.

SyntaxDir [ (pathname [ ,attributes ] ) ] The Dir function syntax has these parts:


FSO would be the way I'd go if I wanted that information

Indigenous
01-22-2018, 08:21 PM
@Paul_Hossler
Thanks for clarifying that Dir is not suitable.
I will persist with FSO.
One thing which I thought may work was to install the reference "DSO OLE Document Properties Reader 2.1" as recommended by Chip Pearson (http://www.cpearson.com/excel/docprop.aspx). However this only worked with DOC and XLS for me, not for DOCX and XLSX.

Paul_Hossler
01-23-2018, 07:37 AM
@Paul_Hossler
I will persist with FSO.
One thing which I thought may work was to install the reference "DSO OLE Document Properties Reader 2.1" as recommended by Chip Pearson (http://www.cpearson.com/excel/docprop.aspx). However this only worked with DOC and XLS for me, not for DOCX and XLSX.

1. I think FSO will do what you want

2. I don't have any experience with DSOfile.dll, but if all you want is to retrieve some properties, the FSO approach would be the way I'd do it

Indigenous
01-23-2018, 07:29 PM
Thanks for providing the info.

Indigenous
01-29-2018, 11:30 PM
For info, it seems that Windows 7 has issues with reading XLSX and DOCS.
Link (http://www.tek-tips.com/viewthread.cfm?qid=1707647).

georgiboy
01-30-2018, 07:01 AM
I had a go but as mentioned above could only get them by opening the file with:


Sub LoopFiles()
Dim fWB As Workbook, newRow As Long
Dim fPath As String, ext As String
Dim fl As String

fPath = "C:\Users\A\Desktop\From\"
ext = "*.xlsm"
fl = Dir(fPath & ext)
newRow = 2

Do While fl <> ""
Set fWB = Workbooks.Open(fPath & fl)
Sheet2.Range("A" & newRow).Value = ActiveWorkbook.BuiltinDocumentProperties("title")
Sheet2.Range("B" & newRow).Value = ActiveWorkbook.BuiltinDocumentProperties("subject")
fWB.Close
fl = Dir
newRow = newRow + 1
Loop

End Sub

Indigenous
01-30-2018, 08:33 AM
I couldn't quite get it to work.
Tried replacing ActiveWorkbook.BuiltinDocumentProperties with fWB.BuiltinDocumentProperties but still not quite.
The other thing is that it does not account for Word files.
Cheers.

georgiboy
01-30-2018, 09:07 AM
If i'm honest it was more of a sample than a solution.

I was thinking of a different approach,

How about using an workbook_close event to store your info to an external source then using excel to pull the data for you as needed?

georgiboy
01-30-2018, 09:46 AM
For example if you placed this in a module in each document you wish to export these values for (Word or Excel):


Private Declare Function GetPrivateProfileString Lib "kernel32" Alias "GetPrivateProfileStringA" (ByVal lpApplicationName As String, ByVal lpKeyName As String, ByVal lpDefault As String, ByVal lpReturnedString As String, ByVal nSize As Long, ByVal lpFileName As String) As LongPrivate Declare Function GetPrivateProfileInt Lib "kernel32" Alias "GetPrivateProfileIntA" (ByVal lpApplicationName As String, ByVal lpKeyName As String, ByVal nDefault As Long, ByVal lpFileName As String) As Long
Private Declare Function WritePrivateProfileString Lib "kernel32" Alias "WritePrivateProfileStringA" (ByVal lpApplicationName As String, ByVal lpKeyName As String, ByVal lpString As String, ByVal lpFileName As String) As Long


Private Const conFig = "C:\Users\A\Desktop\Test.ini" ' location of ini file


Public Function GetINIString(ByVal sApp As String, ByVal sKey As String) As String
Dim sBuf As String * 256
Dim lBuf As Long
lBuf = GetPrivateProfileString(sApp, sKey, "", sBuf, Len(sBuf), conFig)
GetINIString = Left$(sBuf, lBuf)
End Function


Public Function WriteINI(ByVal sApp As String, ByVal sKey As String, ByVal sValue As String) As String
WritePrivateProfileString sApp, sKey, sValue, conFig
End Function

Then you could place this in the ThisDocument part for word:


Private Sub Document_Close()
x = WriteINI(ThisDocument.Path & "\" & ThisDocument.Name, "Title", ThisDocument.BuiltInDocumentProperties("title"))
y = WriteINI(ThisDocument.Path & "\" & ThisDocument.Name, "Subject", ThisDocument.BuiltInDocumentProperties("subject"))
End Sub

or this in the ThisWorkbook module of a workbook:


Private Sub Workbook_BeforeClose(Cancel As Boolean)
x = WriteINI(ThisWorkbook.Path & "\" & ThisWorkbook.Name, "Title", ThisWorkbook.BuiltinDocumentProperties("title"))
y = WriteINI(ThisWorkbook.Path & "\" & ThisWorkbook.Name, "Subject", ThisWorkbook.BuiltinDocumentProperties("subject"))
End Sub

and it will build an ini file as these books are closed.

Just something i was thinking about, may not be viable for your needs.

Hope this helps

Indigenous
01-30-2018, 07:51 PM
Thanks very much for the assistance, however I have a large number of files and can't write the code in post #19 in each one of them.
Cheers.