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?
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
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
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
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:
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.
My FSO macro does retrieve Title and Subject of DOCX files:
Capture.JPGMsgBox 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 informationReturns 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
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
@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
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
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:
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: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: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.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.