Use Custom Document Property to identify xls files not yet process

[Guest]

I have a macro that goes through each spreadsheet in a folder and formats the
sheet.

I'd like to have the code first check to see if there is a Custom Document
Property and if so if it is set to True (this will tell the macro to close
the file and move to the next one - the current one has already been
formatted).

If it doesn't find the property (or if the property is set to false) it
would format the file, then add the property (or set it to true).

Can this be done via VB?

Thanks!

 

[Guest]

See:

http://www.cpearson.com/excel/docprop.htm

 

[Leith Ross]

I have a macro that goes through each spreadsheet in a folder and formats the
sheet.

I'd like to have the code first check to see if there is a Custom Document
Property and if so if it is set to True (this will tell the macro to close
the file and move to the next one - the current one has already been
formatted).

If it doesn't find the property (or if the property is set to false) it
would format the file, then add the property (or set it to true).

Can this be done via VB?

Thanks!

Hello Robert,

Yes, it can. To check if the Workbook has any custom properties use
this code...

Cnt = ThisWorkbook.CustomDocumentProperties

If the Cnt = 0 then there are no Custom Document Properties.

To set a Custom Document Property use this code...

Sub AddCustomProperty()

Dim CustomProp As Object

Set CustomProp = ThisWorkbook.CustomDocumentProperties

With CustomProp
.Add Name:="User Name", LinkToContent:=False,
Type:=msoPropertyTypeString, Value:="Leith Ross"
End With

End Sub

Other TYPE constants are: msoPropertyTypeBoolean, msoPropertyTypeDate,
msoPropertyTypeFloat, msoPropertyTypeNumber, or msoPropertyTypeString.

Sincerely,
Leith Ross