ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   File attributes (https://www.excelbanter.com/excel-programming/379318-file-attributes.html)

Alan
File attributes
 
I have managed to use a macro (VBA) to list the file structure of a
particular folder and would now like this list to have a number of file
attributes associated with each file including File Verion and product
Version.

Can anyone point me in the right direction... please ... I am using the
Filesearch method to get the list

Many Thanks

Martin Fishlock
File attributes
 
Alan,

Where are the 'File Verion' and 'Product Version' items?

Are they custom attributes?

Please explain a little more.

--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.


"Alan" wrote:

I have managed to use a macro (VBA) to list the file structure of a
particular folder and would now like this list to have a number of file
attributes associated with each file including File Verion and product
Version.

Can anyone point me in the right direction... please ... I am using the
Filesearch method to get the list

Many Thanks

NickHK
File attributes
 
Alan,
From others' postings, I suspect the FileSearch may be unreliable for this
(although it may not). There's the API route :
http://vbnet.mvps.org/code/system/odbcfileversion.htm

Although it says for ODBC, it will work for any file that stores such info,
which will not be all files.

NickHK

"Alan" wrote in message
...
I have managed to use a macro (VBA) to list the file structure of a
particular folder and would now like this list to have a number of file
attributes associated with each file including File Verion and product
Version.

Can anyone point me in the right direction... please ... I am using the
Filesearch method to get the list

Many Thanks

Bob Phillips
File attributes
 
Try the DSO OLE file method, you have to install DSO. You can get it at
http://support.microsoft.com/?id=224351
and set a reference to "DSO OLE Document Properties Reader 2.0." in the
VBIDE

Option Explicit


Const COL_Application As String = 1
Const COL_Author As String = 2
Const COL_Version As String = 3
Const COL_Subject As String = 4
Const COL_Category As String = 5
Const COL_Company As String = 6
Const COL_Keywords As String = 7
Const COL_Manager As String = 8
Const COL_LastSavedBy As String = 9
Const COL_WordCount As String = 10
Const COL_PageCount As String = 11
Const COL_ParagraphCount As String = 12
Const COL_LineCount As String = 13
Const COL_CharacterCount As String = 14
Const COL_CharacterCountspaces As String = 15
Const COL_ByteCount As String = 16
Const COL_PresFormat As String = 17
Const COL_SlideCount As String = 18
Const COL_NoteCount As String = 19
Const COL_HiddenSlides As String = 20
Const COL_MultimediaClips As String = 21
Const COL_DateCreated As String = 22
Const COL_DateLastPrinted As String = 23
Const COL_DateLastSaved As String = 24
Const COL_TotalEditingTime As String = 25
Const COL_Template As String = 26
Const COL_Revision As String = 27
Const COL_IsShared As String = 28
Const COL_CLSID As String = 29
Const COL_ProgID As String = 30
Const COL_OleFormat As String = 1
Const COL_OleType As String = 32


Sub ListFileAttributes()
Dim FSO As Object
Dim i As Long
Dim sFolder As String
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object
Dim this As Workbook
Dim aryFiles
Dim cnt As Long
Dim sh As Worksheet


Set FSO = CreateObject("Scripting.FileSystemObject")


Set this = ActiveWorkbook
sFolder = "C:\MyTest"
Set Folder = FSO.GetFolder(sFolder)
Set Files = Folder.Files
cnt = 0
ReDim aryFiles(1 To 33, 1 To 1)
For Each file In Files
If file.Type = "Microsoft Excel Worksheet" Then
Call DSO(file.Path, aryFiles)
End If
Next file


On Error Resume Next
Set sh = Worksheets("ListOfFiles")
On Error GoTo 0
If sh Is Nothing Then
Worksheets.Add.Name = "ListOfFiles"
Else
sh.Cells.ClearContents
End If


For i = LBound(aryFiles, 2) To UBound(aryFiles, 2)
Cells(i + 1, "A").Value = aryFiles(COL_Author, i)
Next i
Columns("A:C").AutoFit


End Sub


Sub DSO(ByVal FileName As String, ByRef aryData)
Static notFirstTime As Boolean
Dim fOpenReadOnly As Boolean
Dim DSO As DSOFile.OleDocumentProperties
Dim oSummProps As DSOFile.SummaryProperties
Dim oCustProp As DSOFile.CustomProperty
Dim iNext As Long


If notFirstTime Then
iNext = UBound(aryData, 2) + 1
Else
iNext = UBound(aryData, 2)
notFirstTime = True
End If
ReDim Preserve aryData(1 To 33, 1 To iNext)


Set DSO = New DSOFile.OleDocumentProperties
DSO.Open FileName, fOpenReadOnly, dsoOptionOpenReadOnlyIfNoWriteAccess


'Get the SummaryProperties (these are built-in set)...
Set oSummProps = DSO.SummaryProperties
aryData(1, iNext) = oSummProps.ApplicationName
aryData(2, iNext) = oSummProps.Author
aryData(3, iNext) = oSummProps.Version
aryData(4, iNext) = oSummProps.Subject
aryData(5, iNext) = oSummProps.Category
aryData(6, iNext) = oSummProps.Company
aryData(7, iNext) = oSummProps.Keywords
aryData(8, iNext) = oSummProps.Manager
aryData(9, iNext) = oSummProps.LastSavedBy
aryData(10, iNext) = oSummProps.WordCount
aryData(11, iNext) = oSummProps.PageCount
aryData(12, iNext) = oSummProps.ParagraphCount
aryData(13, iNext) = oSummProps.LineCount
aryData(14, iNext) = oSummProps.CharacterCount
aryData(15, iNext) = oSummProps.CharacterCountWithSpaces
aryData(16, iNext) = oSummProps.ByteCount
aryData(17, iNext) = oSummProps.PresentationFormat
aryData(18, iNext) = oSummProps.SlideCount
aryData(19, iNext) = oSummProps.NoteCount
aryData(20, iNext) = oSummProps.HiddenSlideCount
aryData(21, iNext) = oSummProps.MultimediaClipCount
aryData(22, iNext) = oSummProps.DateCreated
aryData(23, iNext) = oSummProps.DateLastPrinted
aryData(24, iNext) = oSummProps.DateLastSaved
aryData(25, iNext) = oSummProps.TotalEditTime
aryData(26, iNext) = oSummProps.Template
aryData(27, iNext) = oSummProps.RevisionNumber
aryData(28, iNext) = oSummProps.SharedDocument
'Add a few other items that pertain to OLE files only...
If DSO.IsOleFile Then
aryData(29, iNext) = DSO.CLSID
aryData(30, iNext) = DSO.progID
aryData(31, iNext) = DSO.OleDocumentFormat
aryData(32, iNext) = DSO.OleDocumentType
End If


'Now the custom properties
For Each oCustProp In DSO.CustomProperties
aryData(33, iNext) = CStr(oCustProp.Value)
Next oCustProp


Set oCustProp = Nothing
Set oSummProps = Nothing
Set DSO = Nothing


End Sub


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Alan" wrote in message
...
Martin

'File Version' and 'Product Version' are file attributes
selectable/viewable
within windows explorer. I note NickHK comments using API and will maybe
need to try that although would rather write to a worksheet than display
in a
window

Many thanks for your response


"Martin Fishlock" wrote:

Alan,

Where are the 'File Verion' and 'Product Version' items?

Are they custom attributes?

Please explain a little more.

--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.


"Alan" wrote:

I have managed to use a macro (VBA) to list the file structure of a
particular folder and would now like this list to have a number of file
attributes associated with each file including File Verion and product
Version.

Can anyone point me in the right direction... please ... I am using
the
Filesearch method to get the list

Many Thanks

Bob Phillips
File attributes
 
File size is simple

Set FSO = CreateObject("Scripting.FileSystemObject")

Set this = ActiveWorkbook
Set file = FSO.GetFile("C:\Program Files\Tools\system\DSO
File\dsofile.dll")
MsgBox file.Size


but version seems tricky I admit

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Alan" wrote in message
...
Many Thanks Bob

Almost there ... although still cant find the 'Product Version' attribute
... my file has a value also I noted that the DSO OLE doesnt give me the
option for size as a comparison ??

Once again your assistance is very much appreciated


"Bob Phillips" wrote:

Try the DSO OLE file method, you have to install DSO. You can get it at
http://support.microsoft.com/?id=224351
and set a reference to "DSO OLE Document Properties Reader 2.0." in the
VBIDE

Option Explicit


Const COL_Application As String = 1
Const COL_Author As String = 2
Const COL_Version As String = 3
Const COL_Subject As String = 4
Const COL_Category As String = 5
Const COL_Company As String = 6
Const COL_Keywords As String = 7
Const COL_Manager As String = 8
Const COL_LastSavedBy As String = 9
Const COL_WordCount As String = 10
Const COL_PageCount As String = 11
Const COL_ParagraphCount As String = 12
Const COL_LineCount As String = 13
Const COL_CharacterCount As String = 14
Const COL_CharacterCountspaces As String = 15
Const COL_ByteCount As String = 16
Const COL_PresFormat As String = 17
Const COL_SlideCount As String = 18
Const COL_NoteCount As String = 19
Const COL_HiddenSlides As String = 20
Const COL_MultimediaClips As String = 21
Const COL_DateCreated As String = 22
Const COL_DateLastPrinted As String = 23
Const COL_DateLastSaved As String = 24
Const COL_TotalEditingTime As String = 25
Const COL_Template As String = 26
Const COL_Revision As String = 27
Const COL_IsShared As String = 28
Const COL_CLSID As String = 29
Const COL_ProgID As String = 30
Const COL_OleFormat As String = 1
Const COL_OleType As String = 32


Sub ListFileAttributes()
Dim FSO As Object
Dim i As Long
Dim sFolder As String
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object
Dim this As Workbook
Dim aryFiles
Dim cnt As Long
Dim sh As Worksheet


Set FSO = CreateObject("Scripting.FileSystemObject")


Set this = ActiveWorkbook
sFolder = "C:\MyTest"
Set Folder = FSO.GetFolder(sFolder)
Set Files = Folder.Files
cnt = 0
ReDim aryFiles(1 To 33, 1 To 1)
For Each file In Files
If file.Type = "Microsoft Excel Worksheet" Then
Call DSO(file.Path, aryFiles)
End If
Next file


On Error Resume Next
Set sh = Worksheets("ListOfFiles")
On Error GoTo 0
If sh Is Nothing Then
Worksheets.Add.Name = "ListOfFiles"
Else
sh.Cells.ClearContents
End If


For i = LBound(aryFiles, 2) To UBound(aryFiles, 2)
Cells(i + 1, "A").Value = aryFiles(COL_Author, i)
Next i
Columns("A:C").AutoFit


End Sub


Sub DSO(ByVal FileName As String, ByRef aryData)
Static notFirstTime As Boolean
Dim fOpenReadOnly As Boolean
Dim DSO As DSOFile.OleDocumentProperties
Dim oSummProps As DSOFile.SummaryProperties
Dim oCustProp As DSOFile.CustomProperty
Dim iNext As Long


If notFirstTime Then
iNext = UBound(aryData, 2) + 1
Else
iNext = UBound(aryData, 2)
notFirstTime = True
End If
ReDim Preserve aryData(1 To 33, 1 To iNext)


Set DSO = New DSOFile.OleDocumentProperties
DSO.Open FileName, fOpenReadOnly,
dsoOptionOpenReadOnlyIfNoWriteAccess


'Get the SummaryProperties (these are built-in set)...
Set oSummProps = DSO.SummaryProperties
aryData(1, iNext) = oSummProps.ApplicationName
aryData(2, iNext) = oSummProps.Author
aryData(3, iNext) = oSummProps.Version
aryData(4, iNext) = oSummProps.Subject
aryData(5, iNext) = oSummProps.Category
aryData(6, iNext) = oSummProps.Company
aryData(7, iNext) = oSummProps.Keywords
aryData(8, iNext) = oSummProps.Manager
aryData(9, iNext) = oSummProps.LastSavedBy
aryData(10, iNext) = oSummProps.WordCount
aryData(11, iNext) = oSummProps.PageCount
aryData(12, iNext) = oSummProps.ParagraphCount
aryData(13, iNext) = oSummProps.LineCount
aryData(14, iNext) = oSummProps.CharacterCount
aryData(15, iNext) = oSummProps.CharacterCountWithSpaces
aryData(16, iNext) = oSummProps.ByteCount
aryData(17, iNext) = oSummProps.PresentationFormat
aryData(18, iNext) = oSummProps.SlideCount
aryData(19, iNext) = oSummProps.NoteCount
aryData(20, iNext) = oSummProps.HiddenSlideCount
aryData(21, iNext) = oSummProps.MultimediaClipCount
aryData(22, iNext) = oSummProps.DateCreated
aryData(23, iNext) = oSummProps.DateLastPrinted
aryData(24, iNext) = oSummProps.DateLastSaved
aryData(25, iNext) = oSummProps.TotalEditTime
aryData(26, iNext) = oSummProps.Template
aryData(27, iNext) = oSummProps.RevisionNumber
aryData(28, iNext) = oSummProps.SharedDocument
'Add a few other items that pertain to OLE files only...
If DSO.IsOleFile Then
aryData(29, iNext) = DSO.CLSID
aryData(30, iNext) = DSO.progID
aryData(31, iNext) = DSO.OleDocumentFormat
aryData(32, iNext) = DSO.OleDocumentType
End If


'Now the custom properties
For Each oCustProp In DSO.CustomProperties
aryData(33, iNext) = CStr(oCustProp.Value)
Next oCustProp


Set oCustProp = Nothing
Set oSummProps = Nothing
Set DSO = Nothing


End Sub


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Alan" wrote in message
...
Martin

'File Version' and 'Product Version' are file attributes
selectable/viewable
within windows explorer. I note NickHK comments using API and will
maybe
need to try that although would rather write to a worksheet than
display
in a
window

Many thanks for your response


"Martin Fishlock" wrote:

Alan,

Where are the 'File Verion' and 'Product Version' items?

Are they custom attributes?

Please explain a little more.

--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.


"Alan" wrote:

I have managed to use a macro (VBA) to list the file structure of a
particular folder and would now like this list to have a number of
file
attributes associated with each file including File Verion and
product
Version.

Can anyone point me in the right direction... please ... I am using
the
Filesearch method to get the list

Many Thanks

Bob Phillips
File attributes
 
Did you try Randy Birch's solution (as provided by NickHk), that seems to
get the version?

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Alan" wrote in message
...
Many thanks to all
I am still unsure how to retrieve the 'Product Version' attribute from a
file, however will continue to plug away. Im not sure why Version and
Product
Version are different in some of the files I am listing, but will try to
source an answer to this elsewhere

Thanks again

"Bob Phillips" wrote:

File size is simple

Set FSO = CreateObject("Scripting.FileSystemObject")

Set this = ActiveWorkbook
Set file = FSO.GetFile("C:\Program Files\Tools\system\DSO
File\dsofile.dll")
MsgBox file.Size


but version seems tricky I admit

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Alan" wrote in message
...
Many Thanks Bob

Almost there ... although still cant find the 'Product Version'
attribute
... my file has a value also I noted that the DSO OLE doesnt give me
the
option for size as a comparison ??

Once again your assistance is very much appreciated


"Bob Phillips" wrote:

Try the DSO OLE file method, you have to install DSO. You can get it
at
http://support.microsoft.com/?id=224351
and set a reference to "DSO OLE Document Properties Reader 2.0." in
the
VBIDE

Option Explicit


Const COL_Application As String = 1
Const COL_Author As String = 2
Const COL_Version As String = 3
Const COL_Subject As String = 4
Const COL_Category As String = 5
Const COL_Company As String = 6
Const COL_Keywords As String = 7
Const COL_Manager As String = 8
Const COL_LastSavedBy As String = 9
Const COL_WordCount As String = 10
Const COL_PageCount As String = 11
Const COL_ParagraphCount As String = 12
Const COL_LineCount As String = 13
Const COL_CharacterCount As String = 14
Const COL_CharacterCountspaces As String = 15
Const COL_ByteCount As String = 16
Const COL_PresFormat As String = 17
Const COL_SlideCount As String = 18
Const COL_NoteCount As String = 19
Const COL_HiddenSlides As String = 20
Const COL_MultimediaClips As String = 21
Const COL_DateCreated As String = 22
Const COL_DateLastPrinted As String = 23
Const COL_DateLastSaved As String = 24
Const COL_TotalEditingTime As String = 25
Const COL_Template As String = 26
Const COL_Revision As String = 27
Const COL_IsShared As String = 28
Const COL_CLSID As String = 29
Const COL_ProgID As String = 30
Const COL_OleFormat As String = 1
Const COL_OleType As String = 32


Sub ListFileAttributes()
Dim FSO As Object
Dim i As Long
Dim sFolder As String
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object
Dim this As Workbook
Dim aryFiles
Dim cnt As Long
Dim sh As Worksheet


Set FSO = CreateObject("Scripting.FileSystemObject")


Set this = ActiveWorkbook
sFolder = "C:\MyTest"
Set Folder = FSO.GetFolder(sFolder)
Set Files = Folder.Files
cnt = 0
ReDim aryFiles(1 To 33, 1 To 1)
For Each file In Files
If file.Type = "Microsoft Excel Worksheet" Then
Call DSO(file.Path, aryFiles)
End If
Next file


On Error Resume Next
Set sh = Worksheets("ListOfFiles")
On Error GoTo 0
If sh Is Nothing Then
Worksheets.Add.Name = "ListOfFiles"
Else
sh.Cells.ClearContents
End If


For i = LBound(aryFiles, 2) To UBound(aryFiles, 2)
Cells(i + 1, "A").Value = aryFiles(COL_Author, i)
Next i
Columns("A:C").AutoFit


End Sub


Sub DSO(ByVal FileName As String, ByRef aryData)
Static notFirstTime As Boolean
Dim fOpenReadOnly As Boolean
Dim DSO As DSOFile.OleDocumentProperties
Dim oSummProps As DSOFile.SummaryProperties
Dim oCustProp As DSOFile.CustomProperty
Dim iNext As Long


If notFirstTime Then
iNext = UBound(aryData, 2) + 1
Else
iNext = UBound(aryData, 2)
notFirstTime = True
End If
ReDim Preserve aryData(1 To 33, 1 To iNext)


Set DSO = New DSOFile.OleDocumentProperties
DSO.Open FileName, fOpenReadOnly,
dsoOptionOpenReadOnlyIfNoWriteAccess


'Get the SummaryProperties (these are built-in set)...
Set oSummProps = DSO.SummaryProperties
aryData(1, iNext) = oSummProps.ApplicationName
aryData(2, iNext) = oSummProps.Author
aryData(3, iNext) = oSummProps.Version
aryData(4, iNext) = oSummProps.Subject
aryData(5, iNext) = oSummProps.Category
aryData(6, iNext) = oSummProps.Company
aryData(7, iNext) = oSummProps.Keywords
aryData(8, iNext) = oSummProps.Manager
aryData(9, iNext) = oSummProps.LastSavedBy
aryData(10, iNext) = oSummProps.WordCount
aryData(11, iNext) = oSummProps.PageCount
aryData(12, iNext) = oSummProps.ParagraphCount
aryData(13, iNext) = oSummProps.LineCount
aryData(14, iNext) = oSummProps.CharacterCount
aryData(15, iNext) = oSummProps.CharacterCountWithSpaces
aryData(16, iNext) = oSummProps.ByteCount
aryData(17, iNext) = oSummProps.PresentationFormat
aryData(18, iNext) = oSummProps.SlideCount
aryData(19, iNext) = oSummProps.NoteCount
aryData(20, iNext) = oSummProps.HiddenSlideCount
aryData(21, iNext) = oSummProps.MultimediaClipCount
aryData(22, iNext) = oSummProps.DateCreated
aryData(23, iNext) = oSummProps.DateLastPrinted
aryData(24, iNext) = oSummProps.DateLastSaved
aryData(25, iNext) = oSummProps.TotalEditTime
aryData(26, iNext) = oSummProps.Template
aryData(27, iNext) = oSummProps.RevisionNumber
aryData(28, iNext) = oSummProps.SharedDocument
'Add a few other items that pertain to OLE files only...
If DSO.IsOleFile Then
aryData(29, iNext) = DSO.CLSID
aryData(30, iNext) = DSO.progID
aryData(31, iNext) = DSO.OleDocumentFormat
aryData(32, iNext) = DSO.OleDocumentType
End If


'Now the custom properties
For Each oCustProp In DSO.CustomProperties
aryData(33, iNext) = CStr(oCustProp.Value)
Next oCustProp


Set oCustProp = Nothing
Set oSummProps = Nothing
Set DSO = Nothing


End Sub


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Alan" wrote in message
...
Martin

'File Version' and 'Product Version' are file attributes
selectable/viewable
within windows explorer. I note NickHK comments using API and will
maybe
need to try that although would rather write to a worksheet than
display
in a
window

Many thanks for your response


"Martin Fishlock" wrote:

Alan,

Where are the 'File Verion' and 'Product Version' items?

Are they custom attributes?

Please explain a little more.

--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.


"Alan" wrote:

I have managed to use a macro (VBA) to list the file structure of
a
particular folder and would now like this list to have a number
of
file
attributes associated with each file including File Verion and
product
Version.

Can anyone point me in the right direction... please ... I am
using
the
Filesearch method to get the list

Many Thanks

Siva
Execl C API doument and usefull Examples
 
I am unable to find most of the helpfull and detailed document for Excel C
API. only header XCALL.h can see online and msdn. but could find the details
for the each function usage and parameters what to pass? how to pass. Please
let me know if any one know the same.

Thanks,
Siva


All times are GMT +1. The time now is 02:27 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com