Excel VBA: store version code readable by another workbook

Question

We have a workbook template emailed out, filled and returned by email. When returned, we assign a unique number to the workbook. Currently the unique number is appended to the workbook name.

This is a problem when the workbook isn't filled in correctly and we have to send it out for correction and receive it back with the name changed.

The workbook is processed by VBA and a database is updated based on the unique number. If a workbook with the same number comes in again, it is assumed to be an update and records are replaced.

Our idea is to store the unique number in a hidden worksheet. Is there another way that doesn't require a hidden worksheet - such as the ability to read and write some hidden fields or global constants in a workbook through code?

Variables don't exist until code is compiled & run so I'd say no, but maybe someone here will come up with something. — Absinthe
– Absinthe, Commented Mar 16, 2017 at 23:20

John Coleman · Accepted Answer · 2017-03-16 23:46:56Z

2

You can use a WorkBook's Name collection:

Sub StoreName(key As String, val As Variant)
    Dim WB As Workbook
    Set WB = ThisWorkbook
    WB.Names.Add key, "=" & val 'to force val to be interpreted as a named formula
End Sub

Function ReadName(key As String) As Variant
    Dim WB As Workbook
    Set WB = ThisWorkbook
    ReadName = Mid(WB.Names(key), 2)
End Function

Tested like:

Sub test()
    StoreName "bob", "1234"
    Debug.Print ReadName("bob")
End Sub

Which prints 1234.

There are still other ways to store persistent data. For example, here is an interesting blog post.

edited Mar 16, 2017 at 23:46

answered Mar 16, 2017 at 23:40

John Coleman

52.1k7 gold badges59 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

barrowc · Accepted Answer · 2017-03-17 00:14:31Z

1

You could use the CustomDocumentProperties collection to store the value you need, like this:

Dim p As DocumentProperty

' Delete any existing property with this name
For Each p In ThisWorkbook.CustomDocumentProperties
    If (p.Name = "uniqueNumber") Then
        p.Delete
        Exit For
    End If
Next p

' Create the property with the required value
ThisWorkbook.CustomDocumentProperties.Add "uniqueNumber", False, msoPropertyTypeString, "42846479"

MsgBox ThisWorkbook.CustomDocumentProperties("uniqueNumber")

answered Mar 17, 2017 at 0:14

barrowc

10.7k2 gold badges47 silver badges59 bronze badges

2 Comments

Variatus Over a year ago

Actually, you could use one of the built-in properties, like Category or Comment or Status. Read/Write ThisWorkbook.BuiltinDocumentProperties("Comments")

Vlad Over a year ago

This worked successfully for both writing and reading from a different workbook. Regarding BuiltinDocumentProperties, this is also a good solution however there seems to be a greater chance of being overwritten inadvertently as they are common to workbooks and accessible in the information tab.

Collectives™ on Stack Overflow

Excel VBA: store version code readable by another workbook

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related