| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding document title
If I hover the cursor over an Excel spreadsheet in Windows Explorer, a box
appears showing some of the file data. Included in this is the Title as entered in the Properties page within Excel. Can I find that file title using VBA without opening the file? -- Ian -- |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Finding document title
Hi,
Try Title = ThisWorkbook.BuiltinDocumentProperties("Title") If nothing has been entered then this will return Empty Mike "IanC" wrote: If I hover the cursor over an Excel spreadsheet in Windows Explorer, a box appears showing some of the file data. Included in this is the Title as entered in the Properties page within Excel. Can I find that file title using VBA without opening the file? -- Ian -- |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Finding document title
The code below get the file description of the following DLL
Folder = "c:\Program Files\Microsoft Office\Office11\" FName = "ACCWIZ.DLL" Public Filename As String Public Directory As String Public FullFileName As String Public StrucVer As String Public FileVer As String Public ProdVer As String Public FileFlags As String Public FileOS As String Public FileType As String Public FileSubType As String Type VS_FIXEDFILEINFO dwSignature As Long dwStrucVersionl As Integer ' e.g. = &h0000 = 0 dwStrucVersionh As Integer ' e.g. = &h0042 = .42 dwFileVersionMSl As Integer ' e.g. = &h0003 = 3 dwFileVersionMSh As Integer ' e.g. = &h0075 = .75 dwFileVersionLSl As Integer ' e.g. = &h0000 = 0 dwFileVersionLSh As Integer ' e.g. = &h0031 = .31 dwProductVersionMSl As Integer ' e.g. = &h0003 = 3 dwProductVersionMSh As Integer ' e.g. = &h0010 = .1 dwProductVersionLSl As Integer ' e.g. = &h0000 = 0 dwProductVersionLSh As Integer ' e.g. = &h0031 = .31 dwFileFlagsMask As Long ' = &h3F for version "0.42" dwFileFlags As Long ' e.g. VFF_DEBUG Or VFF_PRERELEASE dwFileOS As Long ' e.g. VOS_DOS_WINDOWS16 dwFileType As Long ' e.g. VFT_DRIVER dwFileSubtype As Long ' e.g. VFT2_DRV_KEYBOARD dwFileDateMS As Long ' e.g. 0 dwFileDateLS As Long ' e.g. 0 End Type Declare Function GetFileVersionInfo Lib "Version.dll" Alias _ "GetFileVersionInfoA" (ByVal lptstrFilename As String, ByVal _ dwhandle As Long, ByVal dwlen As Long, lpData As Any) As Long Declare Function GetFileVersionInfoSize Lib "Version.dll" Alias _ "GetFileVersionInfoSizeA" (ByVal lptstrFilename As String, _ lpdwHandle As Long) As Long Declare Function VerQueryValue Lib "Version.dll" Alias _ "VerQueryValueA" (pBlock As Any, ByVal lpSubBlock As String, _ lplpBuffer As Any, puLen As Long) As Long Declare Sub MoveMemory Lib "kernel32" Alias "RtlMoveMemory" _ (dest As Any, ByVal Source As Long, ByVal length As Long) Declare Function GetSystemDirectory Lib "kernel32" Alias _ "GetSystemDirectoryA" (ByVal Path As String, ByVal cbBytes As Long) As Long ' ===== From Win32 Ver.h ================= ' ----- VS_VERSION.dwFileFlags ----- Public Const VS_FFI_SIGNATURE = &HFEEF04BD Public Const VS_FFI_STRUCVERSION = &H10000 Public Const VS_FFI_FILEFLAGSMASK = &H3F& ' ----- VS_VERSION.dwFileFlags ----- Public Const VS_FF_DEBUG = &H1 Public Const VS_FF_PRERELEASE = &H2 Public Const VS_FF_PATCHED = &H4 Public Const VS_FF_PRIVATEBUILD = &H8 Public Const VS_FF_INFOINFERRED = &H10 Public Const VS_FF_SPECIALBUILD = &H20 ' ----- VS_VERSION.dwFileOS ----- Public Const VOS_UNKNOWN = &H0 Public Const VOS_DOS = &H10000 Public Const VOS_OS216 = &H20000 Public Const VOS_OS232 = &H30000 Public Const VOS_NT = &H40000 Public Const VOS__BASE = &H0 Public Const VOS__WINDOWS16 = &H1 Public Const VOS__PM16 = &H2 Public Const VOS__PM32 = &H3 Public Const VOS__WINDOWS32 = &H4 Public Const VOS_DOS_WINDOWS16 = &H10001 Public Const VOS_DOS_WINDOWS32 = &H10004 Public Const VOS_OS216_PM16 = &H20002 Public Const VOS_OS232_PM32 = &H30003 Public Const VOS_NT_WINDOWS32 = &H40004 ' ----- VS_VERSION.dwFileType ----- Public Const VFT_UNKNOWN = &H0 Public Const VFT_APP = &H1 Public Const VFT_DLL = &H2 Public Const VFT_DRV = &H3 Public Const VFT_FONT = &H4 Public Const VFT_VXD = &H5 Public Const VFT_STATIC_LIB = &H7 ' ----- VS_VERSION.dwFileSubtype for VFT_WINDOWS_DRV ----- Public Const VFT2_UNKNOWN = &H0 Public Const VFT2_DRV_PRINTER = &H1 Public Const VFT2_DRV_KEYBOARD = &H2 Public Const VFT2_DRV_LANGUAGE = &H3 Public Const VFT2_DRV_DISPLAY = &H4 Public Const VFT2_DRV_MOUSE = &H5 Public Const VFT2_DRV_NETWORK = &H6 Public Const VFT2_DRV_SYSTEM = &H7 Public Const VFT2_DRV_INSTALLABLE = &H8 Public Const VFT2_DRV_SOUND = &H9 Public Const VFT2_DRV_COMM = &HA Private Sub GetVerInfo() '*** Get Version Info **** Folder = "c:\Program Files\Microsoft Office\Office11\" FName = "ACCWIZ.DLL" FullFileName = Folder & FName Dim rc As Long Dim lDummy As Long Dim sBuffer() As Byte Dim lBufferLen As Long Dim lVerPointer As Long Dim udtVerBuffer As VS_FIXEDFILEINFO Dim lVerbufferLen As Long '*** Get size **** lBufferLen = GetFileVersionInfoSize(FullFileName, lDummy) If lBufferLen < 1 Then MsgBox "No Version Info available!" Exit Sub End If '**** Store info to udtVerBuffer struct **** ReDim sBuffer(lBufferLen) rc = GetFileVersionInfo(FullFileName, 0&, lBufferLen, sBuffer(0)) rc = VerQueryValue(sBuffer(0), "\", lVerPointer, lVerbufferLen) MoveMemory udtVerBuffer, lVerPointer, Len(udtVerBuffer) '**** Determine Structure Version number - NOT USED **** StrucVer = Format$(udtVerBuffer.dwStrucVersionh) & "." & _ Format$(udtVerBuffer.dwStrucVersionl) '**** Determine File Version number **** FileVer = Format$(udtVerBuffer.dwFileVersionMSh) & "." & _ Format$(udtVerBuffer.dwFileVersionMSl) & "." & _ Format$(udtVerBuffer.dwFileVersionLSh) & "." & _ Format$(udtVerBuffer.dwFileVersionLSl) '**** Determine Product Version number **** ProdVer = Format$(udtVerBuffer.dwProductVersionMSh) & "." & _ Format$(udtVerBuffer.dwProductVersionMSl) & "." & _ Format$(udtVerBuffer.dwProductVersionLSh) & "." & _ Format$(udtVerBuffer.dwProductVersionLSl) '**** Determine Boolean attributes of File **** FileFlags = "" If udtVerBuffer.dwFileFlags And VS_FF_DEBUG _ Then FileFlags = "Debug " If udtVerBuffer.dwFileFlags And VS_FF_PRERELEASE _ Then FileFlags = FileFlags & "PreRel " If udtVerBuffer.dwFileFlags And VS_FF_PATCHED _ Then FileFlags = FileFlags & "Patched " If udtVerBuffer.dwFileFlags And VS_FF_PRIVATEBUILD _ Then FileFlags = FileFlags & "Private " If udtVerBuffer.dwFileFlags And VS_FF_INFOINFERRED _ Then FileFlags = FileFlags & "Info " If udtVerBuffer.dwFileFlags And VS_FF_SPECIALBUILD _ Then FileFlags = FileFlags & "Special " If udtVerBuffer.dwFileFlags And VFT2_UNKNOWN _ Then FileFlags = FileFlags + "Unknown " '**** Determine OS for which file was designed **** Select Case udtVerBuffer.dwFileOS Case VOS_DOS_WINDOWS16 FileOS = "DOS-Win16" Case VOS_DOS_WINDOWS32 FileOS = "DOS-Win32" Case VOS_OS216_PM16 FileOS = "OS/2-16 PM-16" Case VOS_OS232_PM32 FileOS = "OS/2-16 PM-32" Case VOS_NT_WINDOWS32 FileOS = "NT-Win32" Case other FileOS = "Unknown" End Select Select Case udtVerBuffer.dwFileType Case VFT_APP FileType = "App" Case VFT_DLL FileType = "DLL" Case VFT_DRV FileType = "Driver" Select Case udtVerBuffer.dwFileSubtype Case VFT2_DRV_PRINTER FileSubType = "Printer drv" Case VFT2_DRV_KEYBOARD FileSubType = "Keyboard drv" Case VFT2_DRV_LANGUAGE FileSubType = "Language drv" Case VFT2_DRV_DISPLAY FileSubType = "Display drv" Case VFT2_DRV_MOUSE FileSubType = "Mouse drv" Case VFT2_DRV_NETWORK FileSubType = "Network drv" Case VFT2_DRV_SYSTEM FileSubType = "System drv" Case VFT2_DRV_INSTALLABLE FileSubType = "Installable" Case VFT2_DRV_SOUND FileSubType = "Sound drv" Case VFT2_DRV_COMM FileSubType = "Comm drv" Case VFT2_UNKNOWN FileSubType = "Unknown" End Select Case VFT_FONT FileType = "Font" Select Case udtVerBuffer.dwFileSubtype Case VFT_FONT_RASTER FileSubType = "Raster Font" Case VFT_FONT_VECTOR FileSubType = "Vector Font" Case VFT_FONT_TRUETYPE FileSubType = "TrueType Font" End Select Case VFT_VXD FileType = "VxD" Case VFT_STATIC_LIB FileType = "Lib" Case Else FileType = "Unknown" End Select '------------------------------------------------------------- mybuf = "" For i = 0 To (lBufferLen - 1) Step 2 mybuf = mybuf & Chr(sBuffer(i)) Next i mybuf = UCase(mybuf) SearchString = "STRINGFILEINFO" Position = InStr(mybuf, SearchString) Position = Position + Len(SearchString) mybuf = Mid(mybuf, Position) 'Remvoe returns mybuf = Trim(Replace(mybuf, Chr(13), "")) mybuf = Trim(Replace(mybuf, Chr(0), "")) MsgBox (mybuf) End Sub "IanC" wrote: If I hover the cursor over an Excel spreadsheet in Windows Explorer, a box appears showing some of the file data. Included in this is the Title as entered in the Properties page within Excel. Can I find that file title using VBA without opening the file? -- Ian -- |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Finding document title
Hi Mike
This returns the title of the open workbook holding the code, not the title of a closed workbook. May be useful in future, though. Thanks. -- Ian -- "Mike H" wrote in message ... Hi, Try Title = ThisWorkbook.BuiltinDocumentProperties("Title") If nothing has been entered then this will return Empty Mike "IanC" wrote: If I hover the cursor over an Excel spreadsheet in Windows Explorer, a box appears showing some of the file data. Included in this is the Title as entered in the Properties page within Excel. Can I find that file title using VBA without opening the file? -- Ian -- |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Finding document title
Thanks Joel.
That's an impressive amount of code, and I'm pretty certain I can hone it down to something more manageable, if only I can get it to work! I tried pasting your code directly into VBA editor. All the Public Const statements are highlighted as syntax errors. How do I implement this code? -- Ian -- "Joel" wrote in message ... The code below get the file description of the following DLL Folder = "c:\Program Files\Microsoft Office\Office11\" FName = "ACCWIZ.DLL" Public Filename As String Public Directory As String Public FullFileName As String Public StrucVer As String Public FileVer As String Public ProdVer As String Public FileFlags As String Public FileOS As String Public FileType As String Public FileSubType As String Type VS_FIXEDFILEINFO dwSignature As Long dwStrucVersionl As Integer ' e.g. = &h0000 = 0 dwStrucVersionh As Integer ' e.g. = &h0042 = .42 dwFileVersionMSl As Integer ' e.g. = &h0003 = 3 dwFileVersionMSh As Integer ' e.g. = &h0075 = .75 dwFileVersionLSl As Integer ' e.g. = &h0000 = 0 dwFileVersionLSh As Integer ' e.g. = &h0031 = .31 dwProductVersionMSl As Integer ' e.g. = &h0003 = 3 dwProductVersionMSh As Integer ' e.g. = &h0010 = .1 dwProductVersionLSl As Integer ' e.g. = &h0000 = 0 dwProductVersionLSh As Integer ' e.g. = &h0031 = .31 dwFileFlagsMask As Long ' = &h3F for version "0.42" dwFileFlags As Long ' e.g. VFF_DEBUG Or VFF_PRERELEASE dwFileOS As Long ' e.g. VOS_DOS_WINDOWS16 dwFileType As Long ' e.g. VFT_DRIVER dwFileSubtype As Long ' e.g. VFT2_DRV_KEYBOARD dwFileDateMS As Long ' e.g. 0 dwFileDateLS As Long ' e.g. 0 End Type Declare Function GetFileVersionInfo Lib "Version.dll" Alias _ "GetFileVersionInfoA" (ByVal lptstrFilename As String, ByVal _ dwhandle As Long, ByVal dwlen As Long, lpData As Any) As Long Declare Function GetFileVersionInfoSize Lib "Version.dll" Alias _ "GetFileVersionInfoSizeA" (ByVal lptstrFilename As String, _ lpdwHandle As Long) As Long Declare Function VerQueryValue Lib "Version.dll" Alias _ "VerQueryValueA" (pBlock As Any, ByVal lpSubBlock As String, _ lplpBuffer As Any, puLen As Long) As Long Declare Sub MoveMemory Lib "kernel32" Alias "RtlMoveMemory" _ (dest As Any, ByVal Source As Long, ByVal length As Long) Declare Function GetSystemDirectory Lib "kernel32" Alias _ "GetSystemDirectoryA" (ByVal Path As String, ByVal cbBytes As Long) As Long ' ===== From Win32 Ver.h ================= ' ----- VS_VERSION.dwFileFlags ----- Public Const VS_FFI_SIGNATURE = &HFEEF04BD Public Const VS_FFI_STRUCVERSION = &H10000 Public Const VS_FFI_FILEFLAGSMASK = &H3F& ' ----- VS_VERSION.dwFileFlags ----- Public Const VS_FF_DEBUG = &H1 Public Const VS_FF_PRERELEASE = &H2 Public Const VS_FF_PATCHED = &H4 Public Const VS_FF_PRIVATEBUILD = &H8 Public Const VS_FF_INFOINFERRED = &H10 Public Const VS_FF_SPECIALBUILD = &H20 ' ----- VS_VERSION.dwFileOS ----- Public Const VOS_UNKNOWN = &H0 Public Const VOS_DOS = &H10000 Public Const VOS_OS216 = &H20000 Public Const VOS_OS232 = &H30000 Public Const VOS_NT = &H40000 Public Const VOS__BASE = &H0 Public Const VOS__WINDOWS16 = &H1 Public Const VOS__PM16 = &H2 Public Const VOS__PM32 = &H3 Public Const VOS__WINDOWS32 = &H4 Public Const VOS_DOS_WINDOWS16 = &H10001 Public Const VOS_DOS_WINDOWS32 = &H10004 Public Const VOS_OS216_PM16 = &H20002 Public Const VOS_OS232_PM32 = &H30003 Public Const VOS_NT_WINDOWS32 = &H40004 ' ----- VS_VERSION.dwFileType ----- Public Const VFT_UNKNOWN = &H0 Public Const VFT_APP = &H1 Public Const VFT_DLL = &H2 Public Const VFT_DRV = &H3 Public Const VFT_FONT = &H4 Public Const VFT_VXD = &H5 Public Const VFT_STATIC_LIB = &H7 ' ----- VS_VERSION.dwFileSubtype for VFT_WINDOWS_DRV ----- Public Const VFT2_UNKNOWN = &H0 Public Const VFT2_DRV_PRINTER = &H1 Public Const VFT2_DRV_KEYBOARD = &H2 Public Const VFT2_DRV_LANGUAGE = &H3 Public Const VFT2_DRV_DISPLAY = &H4 Public Const VFT2_DRV_MOUSE = &H5 Public Const VFT2_DRV_NETWORK = &H6 Public Const VFT2_DRV_SYSTEM = &H7 Public Const VFT2_DRV_INSTALLABLE = &H8 Public Const VFT2_DRV_SOUND = &H9 Public Const VFT2_DRV_COMM = &HA Private Sub GetVerInfo() '*** Get Version Info **** Folder = "c:\Program Files\Microsoft Office\Office11\" FName = "ACCWIZ.DLL" FullFileName = Folder & FName Dim rc As Long Dim lDummy As Long Dim sBuffer() As Byte Dim lBufferLen As Long Dim lVerPointer As Long Dim udtVerBuffer As VS_FIXEDFILEINFO Dim lVerbufferLen As Long '*** Get size **** lBufferLen = GetFileVersionInfoSize(FullFileName, lDummy) If lBufferLen < 1 Then MsgBox "No Version Info available!" Exit Sub End If '**** Store info to udtVerBuffer struct **** ReDim sBuffer(lBufferLen) rc = GetFileVersionInfo(FullFileName, 0&, lBufferLen, sBuffer(0)) rc = VerQueryValue(sBuffer(0), "\", lVerPointer, lVerbufferLen) MoveMemory udtVerBuffer, lVerPointer, Len(udtVerBuffer) '**** Determine Structure Version number - NOT USED **** StrucVer = Format$(udtVerBuffer.dwStrucVersionh) & "." & _ Format$(udtVerBuffer.dwStrucVersionl) '**** Determine File Version number **** FileVer = Format$(udtVerBuffer.dwFileVersionMSh) & "." & _ Format$(udtVerBuffer.dwFileVersionMSl) & "." & _ Format$(udtVerBuffer.dwFileVersionLSh) & "." & _ Format$(udtVerBuffer.dwFileVersionLSl) '**** Determine Product Version number **** ProdVer = Format$(udtVerBuffer.dwProductVersionMSh) & "." & _ Format$(udtVerBuffer.dwProductVersionMSl) & "." & _ Format$(udtVerBuffer.dwProductVersionLSh) & "." & _ Format$(udtVerBuffer.dwProductVersionLSl) '**** Determine Boolean attributes of File **** FileFlags = "" If udtVerBuffer.dwFileFlags And VS_FF_DEBUG _ Then FileFlags = "Debug " If udtVerBuffer.dwFileFlags And VS_FF_PRERELEASE _ Then FileFlags = FileFlags & "PreRel " If udtVerBuffer.dwFileFlags And VS_FF_PATCHED _ Then FileFlags = FileFlags & "Patched " If udtVerBuffer.dwFileFlags And VS_FF_PRIVATEBUILD _ Then FileFlags = FileFlags & "Private " If udtVerBuffer.dwFileFlags And VS_FF_INFOINFERRED _ Then FileFlags = FileFlags & "Info " If udtVerBuffer.dwFileFlags And VS_FF_SPECIALBUILD _ Then FileFlags = FileFlags & "Special " If udtVerBuffer.dwFileFlags And VFT2_UNKNOWN _ Then FileFlags = FileFlags + "Unknown " '**** Determine OS for which file was designed **** Select Case udtVerBuffer.dwFileOS Case VOS_DOS_WINDOWS16 FileOS = "DOS-Win16" Case VOS_DOS_WINDOWS32 FileOS = "DOS-Win32" Case VOS_OS216_PM16 FileOS = "OS/2-16 PM-16" Case VOS_OS232_PM32 FileOS = "OS/2-16 PM-32" Case VOS_NT_WINDOWS32 FileOS = "NT-Win32" Case other FileOS = "Unknown" End Select Select Case udtVerBuffer.dwFileType Case VFT_APP FileType = "App" Case VFT_DLL FileType = "DLL" Case VFT_DRV FileType = "Driver" Select Case udtVerBuffer.dwFileSubtype Case VFT2_DRV_PRINTER FileSubType = "Printer drv" Case VFT2_DRV_KEYBOARD FileSubType = "Keyboard drv" Case VFT2_DRV_LANGUAGE FileSubType = "Language drv" Case VFT2_DRV_DISPLAY FileSubType = "Display drv" Case VFT2_DRV_MOUSE FileSubType = "Mouse drv" Case VFT2_DRV_NETWORK FileSubType = "Network drv" Case VFT2_DRV_SYSTEM FileSubType = "System drv" Case VFT2_DRV_INSTALLABLE FileSubType = "Installable" Case VFT2_DRV_SOUND FileSubType = "Sound drv" Case VFT2_DRV_COMM FileSubType = "Comm drv" Case VFT2_UNKNOWN FileSubType = "Unknown" End Select Case VFT_FONT FileType = "Font" Select Case udtVerBuffer.dwFileSubtype Case VFT_FONT_RASTER FileSubType = "Raster Font" Case VFT_FONT_VECTOR FileSubType = "Vector Font" Case VFT_FONT_TRUETYPE FileSubType = "TrueType Font" End Select Case VFT_VXD FileType = "VxD" Case VFT_STATIC_LIB FileType = "Lib" Case Else FileType = "Unknown" End Select '------------------------------------------------------------- mybuf = "" For i = 0 To (lBufferLen - 1) Step 2 mybuf = mybuf & Chr(sBuffer(i)) Next i mybuf = UCase(mybuf) SearchString = "STRINGFILEINFO" Position = InStr(mybuf, SearchString) Position = Position + Len(SearchString) mybuf = Mid(mybuf, Position) 'Remvoe returns mybuf = Trim(Replace(mybuf, Chr(13), "")) mybuf = Trim(Replace(mybuf, Chr(0), "")) MsgBox (mybuf) End Sub "IanC" wrote: If I hover the cursor over an Excel spreadsheet in Windows Explorer, a box appears showing some of the file data. Included in this is the Title as entered in the Properties page within Excel. Can I find that file title using VBA without opening the file? -- Ian -- |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Document name not displayed in Title Bar | Setting up and Configuration of Excel | |||
| why isn't my document name showing up in the title bar | Excel Discussion (Misc queries) | |||
| why isn't my document name showing up in the title bar | Excel Discussion (Misc queries) | |||
| Title for Excel Document | Excel Discussion (Misc queries) | |||
| title bar should show document name first | Excel Discussion (Misc queries) | |||
![IanC[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
