How to Extract Properties from Excel/Word without Opening File using VBA

How to get CustomProperties from Excel Workbook/Word Document (VBA) without physically opening the file


There are many cases where we need to get the document property without the file being opened in VBA.

This can be achieved by using the objects available in DSOFile.dll. This file can be downloaded from http://support.microsoft.com/kb/224351

Once this downloaded and installed. You need to add a reference to DSO Ole Document's property library (refer image below)

Function GetPropFromDSO(ByVal sFile As String, ByVal sCP As String) As String

Dim oFil As DSOFile.OleDocumentProperties
Dim oCP As DSOFile.CustomProperties

On Error GoTo Err_Tp
    
    Set oFil = New OleDocumentProperties
    
    oFil.Open sFile, True
    
    Set oCP = oFil.CustomProperties
    GetRevFromDSO = oCP(sCP)
.Value    
    oFil.Close

Err_Tp:
If Err <> 0 Then
    Err.Clear
    Resume Next
End If

End Function

The function gets the Filename and the Property to be extracted and returns the property value.

Here are some important custom properties

How to know if a Excel Workbook has Macro without opening it

How to link Excel Table to ListBox using VBA

Fill a ListBox from Excel Table using VBA / Populate a ListBox from Excel Table using VBA

Let us take a Excel table as shown below - a list of Top 10 All time hits .

Let us assume that we need to populate the Listbox with values from Column 2

The following code will help you populate the data

Dim oWS As Worksheet
    Set oWS = ThisWorkbook.Sheets(3)
    Me.ListBox1.List = oWS.ListObjects(1).ListColumns("Title").DataBodyRange.Value
End Sub

How to XCOPY files using VBA

How to copy set of files from one folder to another using VBA / How to run DOS Commands in VBA

After a long hibernation I am posting in this blog, thanks to Yaswi.

There is nothing like using the command prompt. This gives a good satisfaction for any programmer / administrator as s/he moves around the files, typing the commands etc

Here is a simple code that moves all the files from one folder to another using XCOPY. You can use all the options of XCOPY with VBA

Sub Copy_Bunch_Of_Files()

Shell "cmd /c xcopy /y c:\temp\*.* C:\Temp\Backup"

End Sub