Excel Workbook Address in VBA

[donaldrnoble]

Is it possible to get the web address of an Excel workbook using VBA.
The only address I can find in the VBA help refer to cell addresses, or
to the outlook address book.

The string I am looking for is that displayed in the 'address' field of
the 'web' toolbar.

Thanks for your help,
Donald

 

[Jake Marx]

Hi Donald,

Is it possible to get the web address of an Excel workbook using VBA.
The only address I can find in the VBA help refer to cell addresses,
or to the outlook address book.

The string I am looking for is that displayed in the 'address' field
of the 'web' toolbar.

AFAIK, you can use ThisWorkbook.FullName, which should return the full URL
(Path/Filename) of the workbook.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[Donald Noble]

AFAIK, you can use ThisWorkbook.FullName, which should return the full URL
(Path/Filename) of the workbook.

ThisWorkbook.FullName does return the full path of the workbook, which
is the same as the web address for local files.

Where the files are stored on a server, it is a bit different

ThisWorkbook.FullName gives the mapped drive and path like
Z:\path\to\file.xls which depends on the mapped drives on the local
computer.

The web toolbar gives \\server\full\path\to\file.xls which would work
for any computer that can connect to that server.

Thanks for your help though Jake.

Regards, Donald

 

[Jake Marx]

Hi Donald,

Not sure if this will work, but it's worth a try:

MsgBox Application.CommandBars("Web").Controls(10).Text

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[donaldrnoble]

Hi Donald,

Not sure if this will work, but it's worth a try:

MsgBox Application.CommandBars("Web").Controls(10).Text

That works perfectly, as long as the web toolbar hasn't been cutomised.

After some searching, trial, and errors, I found that the name of this
control is actually "Address:" including a colon!

so MsgBox Application.CommandBars("Web").Controls("Address:").Text
works as long as the Web toolbar has the address field.

Thanks again for your help