CustomDocumentProperties

[Michelle]

Hello,

I want to make sure the user has only one bulk load spreadsheet open at a
time.I am trying to check this using the CustomDocumentProperties for
spreadsheet name. I had the following code but if the user saves the
spreadsheet under a new name this code will not see it. Can you help me?

For intI = 1 To Workbooks.count
If Workbooks.Item(intI).Name = "Document Control Markup.xls" Or _
Workbooks.Item(intI).Name = "Document Control Vendor Submittal.xls"
Or _
Workbooks.Item(intI).Name = "PLM_Author.xls" Then
blnOtherBulkLoadSSOpen = True
End If

Next intI

If blnOtherBulkLoadSSOpen Then
MsgBox "You cannot have MORE THAN ONE Bulk Load Spreadsheet open at
the same time!", _
vbCritical, "CLOSE OTHER INSTANCES OF BULK LOAD SPREADSHEETS"

 

[Peter T]

There are various ways to "mark" a workbook, one way

s = "whatever" ' eg wb.name
With wb.Names.Add("Bulk", s)
..Visible = False ' hide from user
End With


On Error Resume Next
For Each wb In Workbooks
Set nm = Nothing
Set nm = wb.Names("Bulk")
If Not nm Is Nothing Then
MsgBox wb.Name
' exit for etc
Else
Err.Clear
End If
Next

or maybe Customdocument properties

or perhaps assign say the prefix to the workbook's title

wb.Title = "Bulk_abc"

if instr(wb.title, "Bulk") = 1 then

Regards,
Peter T

 

[Michelle]

Thanks Peter but how can I tell if more than one spreadsheet is open or not?

 

[Peter T]

One way, taking the hidden name example

On Error Resume Next
For Each wb In Workbooks
Set nm = Nothing
Set nm = wb.Names("Bulk")
If Not nm Is Nothing Then
If Len(s) Then s = s & ","
s = s & wb.Name
Else
Err.Clear
End If
Next
On Error GoTo 0 ' or resume normal error hanfdling

v = Split(s, ",")
If UBound(v) > -1 Then
' look at the string s or the array v
End If

Other ways, in the loop add "bulk" wb's to a collection (wb.name or as the
object), or to an array, etc

Regards,
Peter T

 

[Michelle]

What if I use this code? Will this work or do I have to do something special
with CustomDocumentProperties? Thank you for your patience.

blnOtherBulkLoadSSOpen = False

For intI = 1 To Workbooks.count
If Workbooks.Item(intI).CustomDocumentProperties("Project").Value =
"Markup" Or _
Workbooks.Item(intI).CustomDocumentProperties("Project").Value =
"VendorSubmittal" Or _
Workbooks.Item(intI).CustomDocumentProperties("Project").Value =
"PLMAuthor" Then
blnOtherBulkLoadSSOpen = True
End If

 

[Michelle]

The problem with the hidden name is these bulkload spreadsheets act as
templates that are filled out and used to upload files into our PLM software.
The user will save this spreadsheet(workbook) under another name ex R0012345.
The hidden bulk name in your example won't help with this situation because
if R0012345 is open it won't recognize it. That's why I wanted to use the
custom properties to check for open workbooks.
I hope this makes more sense to you.

 

[Peter T]

I take it you haven't tried that code or you'd have found workbooks that do
not have the particular property will error when trying to read it. But
could do something along those lines, maybe something like this


Sub test()
Dim sProp As String
Dim wb As Workbook
Dim col As Collection

Set col = New Collection
On Error Resume Next
For Each wb In Workbooks
sProp = ""
sProp = wb.CustomDocumentProperties("Project").Value
If Len(sProp) Then
Select Case sProp
Case "Markup", "VendorSubmittal", "PLMAuthor"
col.Add wb, wb.Name
End Select
End If
Next
On error resume next

For i = 1 To col.Count
' all "marked" wb's (if any) are in the collection
' to do with as you will
Debug.Print col(i).Name
Next

End Sub

 

[Peter T]

I don't follow why defined names, if that's what you mean, would not be OK
vs doc-props which would. Or indeed any of the various other ways of
"marking" a workbook, eg something recognizable in the Title or Comments
fields etc

Regards,
Peter T