T/F Can't set Hyperlink base from within macro

[Chris Beall]

Excel 2000.

I wanted to set the Hyperlink base from within a macro. Then I read:

"BuiltinDocumentProperties Property
Returns a DocumentProperties collection that represents all the built-in
document properties for the specified workbook. Read-only."

Among the listed built-in properties is "Hyperlink Base". Putting that
together with "read only", I deduce that although I can set the
Hyperlink Base as a user, this cannot be done via a macro.

Am I correct or is there a loophole somewhere that would allow me to do
this?

Chris Beall

 

[Guest]

Hi Chris:

Sub chris3()
ActiveWorkbook.BuiltinDocumentProperties(29) = "G:\"
End Sub

 

[Dave Peterson]

I'm not sure I'd trust that number to always be the same. (I don't know if it
changes at all, though.)

But I'd use the name

ActiveWorkbook.BuiltinDocumentProperties("Hyperlink Base") = "G:\"

 

[Chris Beall]

Hi Chris:

Sub chris3()
ActiveWorkbook.BuiltinDocumentProperties(29) = "G:\"
End Sub

GS,

OK, I'll bite. Where did you get the 29 from?

I'm not questioning it, just trying to learn what the documentation
sources are.

Thanks,
Chris Beall

 

[Chris Beall]

I'm not sure I'd trust that number to always be the same. (I don't know if it
changes at all, though.)

But I'd use the name

ActiveWorkbook.BuiltinDocumentProperties("Hyperlink Base") = "G:\"

Dave,

Yep, I prefer the name, if for no better reason that it will help me
remember in 6 months what the heck I was doing.

But how did you determine that this was valid to even attempt, given the
'read only' statement that I quoted, which came from the Excel Help?
I'm trying to determine where to get authoritative documentation and if
the MS doc isn't authoritative, what is?

Thanks,
Chris Beall

 

[Dave Peterson]

Search for BuiltinDocumentProperties in VBA's help.

You'll see a list in the Remarks section.

 

[Guest]

Hi Chris:

Its in Help. You can always generate the numbers, name, and values with:

Sub chris4()
i = 1
For Each p In ActiveWorkbook.BuiltinDocumentProperties
Cells(i, 1) = i
Cells(i, 2) = p.Name
On Error Resume Next
Cells(i, 3) = p.Value
i = i + 1
Next
End Sub

 

[Chris Beall]

Hi Chris:

Its in Help. You can always generate the numbers, name, and values with:

Sub chris4()
i = 1
For Each p In ActiveWorkbook.BuiltinDocumentProperties
Cells(i, 1) = i
Cells(i, 2) = p.Name
On Error Resume Next
Cells(i, 3) = p.Value
i = i + 1
Next
End Sub

GS,

Well, sorta. In Excel 2000 Help, there is indeed a list of the Built-in
properties, and if you notice that they are NOT in alphabetical order
and then count down the list, starting with the first column, you'll
find that, indeed, Hyperlink Base is the 29th one.

Of course, they might have said something like, "The following list
shows the properties IN THE ORDER OF THEIR INDEX NUMBERS."

Anyway, thanks again.

Chris Beall

 

[Chris Beall]

Search for BuiltinDocumentProperties in VBA's help.

You'll see a list in the Remarks section.

Gary,

OK, I see what threw me off. The Help for BuiltinDocumentProperties
Property says "read only", but that means the list of properties itself
(i.e. you can't create new built-in properties), not to each individual
property. My error.

Thanks again,
Chris Beall

 

[Guest]

Chris:
You are doing just fine. We will always perservere in spite of imperfect
documentation.

If you run into a snag, just post.