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?
Printable View
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?
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
Hi
Thanks for sending this.
Do you know how to look for "Title" and "Subject"?
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!!
OK thanks for your input.
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
Code: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
For more on this, see:
http://www.snb-vba.eu/VBA_Bestanden_en.html
@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/show...Of-not-working). It works for PDF but not for DOCX and XLSX.
I don't understand what you want to communicate here:
Quote:
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). It works for PDF but not for DOCX and XLSX.
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.
Quote:
Originally Posted by Indigenous
My FSO macro does retrieve Title and Subject of DOCX files:
Attachment 21423Code: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")
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?
FSO would be the way I'd go if I wanted that informationQuote:
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:
@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.
1. I think FSO will do what you wantQuote:
Originally Posted by Indigenous
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
Thanks for providing the info.
For info, it seems that Windows 7 has issues with reading XLSX and DOCS.
Link.
I had a go but as mentioned above could only get them by opening the file with:
Code: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
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.
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?
For example if you placed this in a module in each document you wish to export these values for (Word or Excel):
Then you could place this in the ThisDocument part for word:Code: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
or this in the ThisWorkbook module of a workbook:Code: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
and it will build an ini file as these books are closed.Code: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
Just something i was thinking about, may not be viable for your needs.
Hope this helps
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.