Test if Workbook property exists

**djblois1** · 03-16-2012, 02:35 PM

I use custom workbook properties to save "settings" for each workbook. Is there a way to test if that property exists?

Here is one Property that I add:

wbWorking.CustomDocumentProperties.Add "Company", False, msoPropertyTypeString, stgCompany

How can I test if that property exists?

And also is there a way to test if Any Custom Properties exist, instead of specific ones?

**Kenneth Hobson** · 03-16-2012, 02:51 PM

See if Chip's routines help. http://www.cpearson.com/excel/docprop.aspx

**djblois1** · 03-19-2012, 02:48 PM

Ken,

Thank you for the help but I do not see anywhere in his routines, how to test if the property exists or not. I can only see how to set the property and how to test the value.

**Kenneth Hobson** · 03-19-2012, 03:25 PM

Did you download dsofile.dll and try his ReadPropertyFromClosedFile?

**djblois1** · 03-19-2012, 03:27 PM

I am not testing a property in a closed file. I am just testing if it exists in an open workbook.

**Kenneth Hobson** · 03-19-2012, 03:58 PM

In that case, simply:

Sub test_CustomDOcumentProperties()
  Dim stgCompany As String, tf As Boolean
  stgCompany = "ACME"
  MsgBox ThisWorkbook.CustomDocumentProperties.Count
  
  tf = cdpExists(ThisWorkbook, "Company")
  If tf Then
    MsgBox "Company Exists as a Custom Document Property in ThisWorkbook", vbInformation, "Company: " & ThisWorkbook.CustomDocumentProperties("Company").Value
    Else
    ThisWorkbook.CustomDocumentProperties.Add "Company", False, msoPropertyTypeString, stgCompany
  End If
End Sub

Function cdpExists(aWorkbook As Workbook, cdpName As String) As Boolean
  Dim cdp As Object
  For Each cdp In ThisWorkbook.CustomDocumentProperties
    If LCase(cdp.Name) = LCase(cdpName) Then
      cdpExists = True
      Exit Function
    End If
  Next cdp
End Function

The advantage to closed file processing is that it is faster. The file can be open for some routines as is this case. If you use Chip's routines, you will need to delete some "DSOFile." prefix parts. When you Compile, you can see which ones.

e.g.

Sub Test_ReadPropertyFromClosedFile()
  Dim s As Variant
  s = ReadPropertyFromClosedFile(ThisWorkbook.FullName, "Company", PropertyLocationCustom)
  If IsNull(s) Then s = ""
  MsgBox "Company: " & s
End Sub

**djblois1** · 03-20-2012, 12:01 PM

the problem is his routines say they do not work with Excel 2007 and later - which my company uses.

**Kenneth Hobson** · 03-20-2012, 02:58 PM

Just what routine is a problem? I don't know what Chip means by:

NOTE: The DSO OLE Document Property Reader does NOT parse properties out of Office 2007 and later files, since these are not OLE files.

The examples that I gave were for Excel 2010 that worked for me.

So, do my two solutions work for you?

**djblois1** · 03-20-2012, 03:04 PM

Thank you Ken,

I have not had the chance to respond but your code for an open workbook did work perfectly. I never tested to see if the code worked on 2007 files but if you say it does I will test it out because that could help me in another issue.