hyperlinks to name tags
Martin Trautmann
How may I tell Excel to use links to name tags?
While I use a hyperlink of format
http://www.microsoft.com/excel#name
this link is clickable and DOES use the #name (jump to <a
name="name"></a>)
However, in order to re-edit this hyperlink, the part that begins with #
is not shown.
Saving this cell als html does produce
http://www.microsoft.com/excel
How may I use the named part as well?
Thanks for your help
Martin
David McRitchie
Your question is very ambiguous, perhaps if you used
actual references it would have been clearer what you want.
It would appear in the example you left out the file extension
name. Is this supposed to be .htm or .xls
Is the name field supposed to represent a named range
in an Excel file converted to HTML?
Perhaps the following pages would get you started or at
least provide the means to ask a more specific question:
Worksheet VBA Coding
http://www.geocities.com/davemcritchie/excel/sheets.htm
This Excel example for instance sheet3 is a sheetname:
=HYPERLINK("h:\excel2k\testng2k.xls#sheet3!a1","SHEET3")
in html to refer to sheet6
<a href="h:\excel2k\testng2k.xls#sheet6!a1">sheet6</a>
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Macros: http://www.geocities.com/davemcritchie/excel/excel.htm
Search Page: http://www.geocities.com/davemcritchie/excel/search.htm
"Martin Trautmann" <t-...@gmx.net> wrote in message news:slrn9qe83t...@ID-685.user.dfncis.de...
Martin Trautmann
> Hi Martin,
> Your question is very ambiguous, perhaps if you used
> actual references it would have been clearer what you want.
Hi David,
sorry for any ambiguity. Please find a sample file in your mailbox.
Exporting a hyperlink (from .xls saving als .htm) will truncate the named part.
> It would appear in the example you left out the file extension
> name. Is this supposed to be .htm or .xls
> Is the name field supposed to represent a named range
> in an Excel file converted to HTML?
It's to some named part in any other html file - the example that I used
was just fiction. Lets correct this fiction to
http://www.microsoft.com/excel.html#name
Given this link in the Cell of an Excel sheet, exporting does truncate
it to http://www.microsoft.com/excel.html
> Perhaps the following pages would get you started or at
> least provide the means to ask a more specific question:
>
> Worksheet VBA Coding
> http://www.geocities.com/davemcritchie/excel/sheets.htm
>
> This Excel example for instance sheet3 is a sheetname:
> =HYPERLINK("h:\excel2k\testng2k.xls#sheet3!a1","SHEET3")
>
> in html to refer to sheet6
> <a href="h:\excel2k\testng2k.xls#sheet6!a1">sheet6</a>
>
> HTH,
How do I export this Hyperlink to a HTML file? I expected Excel to do
this automatically.
Kind regards
Martin
David McRitchie
You new example is still fiction:
Sorry, there is no Microsoft.com web page matching your request.
But it appears that you simply want to refer to a regular HTML page
from Excel and I think you would have found the example in my
references that I gave you with a little bit of interpolation.
In any case here is one you can use in Excel. Make your
window wide before copying link you will still have to add the
last part if this breaks into two lines..
=HYPERLINK("http://www.geocities.com/davemcritchie/excel/sheets.htm#deletethissheet","Delete Sheet")
The above link works but I see in IE6 that you see an incorrect
http://www.geocities.com/davemcritchie/excel/deletethissheet
on the formula bar instead of
http://www.geocities.com/davemcritchie/excel/sheets.htm#deletethissheet
HTH, There is no need for me to open any attached file.
David McRitchie, Microsoft MVP - Excel
My Excel Macros: http://www.geocities.com/davemcritchie/excel/excel.htm
Search Page: http://www.geocities.com/davemcritchie/excel/search.htm
"Martin Trautmann" <t-...@gmx.net> wrote in message news:slrn9qesom...@ID-685.user.dfncis.de...
Martin Trautmann
> =HYPERLINK("http://www.geocities.com/davemcritchie/excel/sheets.htm#deletethissheet","Delete Sheet")
Ok - now please save this cell as HTML
> The above link works but I see in IE6 that you see an incorrect
> http://www.geocities.com/davemcritchie/excel/deletethissheet
> on the formula bar instead of
> http://www.geocities.com/davemcritchie/excel/sheets.htm#deletethissheet
This seems to be another bug, since
http://www.geocities.com/davemcritchie/excel/deletethissheet is not
valid. However, the data shown is correct, so this may be more some kind
of IE bug.
> HTH, There is no need for me to open any attached file.
This might have shown you that a 'save as html' will truncate every
#named part of the link, e.g.
http://www.geocities.com/davemcritchie/excel/sheets.htm
I need a workaround for this bug, since I do need the correct entry
point in a HTML file, identified by this name tag.
-- sample output from hyperlink.xls to hyperlink.htm :
<HTML>
<HEAD>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<TITLE>hyperlink</TITLE>
</HEAD>
<BODY>
<H1><CENTER>Tabelle1</CENTER></H1>
<!-- Die nachstehende Tabelle wurde mit dem Microsoft Excel Internet-Assistenten erstellt. -->
<!-- ------------------------- -->
<!-- START DER KONVERTIERTEN AUSGABE -->
<!-- ------------------------- -->
<Table border>
<TR VALIGN="bottom">
<TD ALIGN="left"><A HREF="http://www.geocities.com/davemcritchie/excel/sheets.htm"><FONT FACE="Arial" SIZE=-1 COLOR=#0000FF><U>http://www.geocities.com/davemcritchie/excel/sheets.htm#deletethissheet</U></FONT></A></TD>
</TR>
</Table>
<!-- ------------------------- -->
<!-- ENDE DER KONVERTIERTEN AUSGABE -->
<!-- ------------------------- -->
<FONT SIZE=-1><I>
<BR>Letzte Aktualisierung 19.09.01
<BR>Durch System Support
<FONT SIZE=+0></I>
</BODY>
</HTML>
Martin Trautmann
BTW: What's the reverse function to show the hyperlink data of a cell?
I'm willing to extract the link first, then merge both separately
afterwards again. ctrl-k shows
URL: //www.geocities.com/davemcritchie/excel/sheets.htm
Name (optional): deletethissheet
How do I get this object info?
And yet another problem: How to use the Macros that you provide? I
wanted to give BuildTOC a try and imported it as a module of its own.
I don't have very much scripting information since I canceled Excel AFAP
by frustration, when the more sophisticated macros I built did not
manage the conversion from Excel 5 to Excel 97 (some functions where
skipped without any replacement). So I'm no expert here.
The problem is due to version conflicts: MS VB seems to be unable to
copy or import properly from an English to a German version: Maybe the
macro is available not only as .txt, but as .xls or anything else which
converts properly to this version "Excel 97 SR-2" here?
Replace in Sub BuildTOC() is an unknown sub or command
Thanks
Martin
David McRitchie
I am using Excel 2000 (US version) and the conversion of a
HYPERLINK formula using File, Save As webpage
worked fine for me if 10,323 bytes is considered acceptable at
http://www.geocities.com/davemcritchie/excel/temp/martin_hyperlink.htm
compared to a more reasonable 2,096 bytes which can be seen at
http://www.geocities.com/davemcritchie/excel/temp/martin_hyperlink2.htm
Since my own conversion does not do HYPERLINK formulas, I coded
that in just to be able to compare the sizes. -- it would do the
hyperlink objects though.
Microsoft has provided different means of translating Excel to
HTML and has made that translation part of Excel versions or as an
addin to Excel making for inconsistencies and I guess added to
those inconsistencies are problems with language versions if
what you indicate having problems with would work in the
US version of Excel 97. I tend to shy away from MS creating
any HTML from Excel unless something is too complicated to
easily do it myself.
http://www.geocities.com/davemcritchie/excel/xl2html.htm
Since German is your language you might take a look at an addin
XL2HTML-Konverter for XL5/7 hwkkonv.zip
http://www.herber.de/free/hwhkonv.zip
by Hans W. Herber http://www.herber.de
You might check that your Calculation option remains as you
want if you implement the addin.
The reverse function to show the hyperlink data of a cell is not provided
as a Worksheet Function in Excel. But you can use VBA and you
will find such a function, URL, on my Build Table of Contents page
http://www.geocities.com/davemcritchie/excel/buildtoc.htm#url
which will pull in object hyperlinks and uncomplicated =HYPERLINK
formulas unencumbered by concatenation.
I think Replace would be available in Excel 97; otherwise, someone
would probably have mentioned it by now so I expect this is strictly
a language problem. But maybe someone would verify whether
REPLACE is in the macro language in Excel 97 in English and
even give you the German equivalent.
==== From VBE HELP for REPLACE =====
Replace Function
Description
Returns a string in which a specified substring has been replaced with another substring a specified number of times.
Syntax
Replace(expression, find, replace[, start[, count[, compare]]])
==================================
You might try Tom Ogilvy's tip on translating English functions to the language of your Excel.
1) Go into the VBE in the immediate window
2) Put in something like
Range("A1").Formula = "=Sum(B1:B10)"
3) Then hit enter. Now look at A1 and the formula should be translated.
Other tips as they are revealed to me can be found in
http://www.geocities.com/davemcritchie/excel/language.htm
HTH, my web pages may be inaccessible. Geocities has recently
placed a limit of traffic to Geocities sites in order to extract money
for the ability to continue as before with higher limits than they
have now imposed. I will have to do something to reduce traffic
such as an alternate site. In the meantime if you fail to get through
try again in another hour.
David McRitchie, Microsoft MVP - Excel
My Excel Macros: http://www.geocities.com/davemcritchie/excel/excel.htm
Search Page: http://www.geocities.com/davemcritchie/excel/search.htm
"Martin Trautmann" <t-...@gmx.net> wrote in message news:slrn9qh3no...@ID-685.user.dfncis.de...
Martin Trautmann
> I am using Excel 2000 (US version) and the conversion of a
> HYPERLINK formula using File, Save As webpage
> worked fine for me if 10,323 bytes is considered acceptable at
> http://www.geocities.com/davemcritchie/excel/temp/martin_hyperlink.htm
> compared to a more reasonable 2,096 bytes which can be seen at
> http://www.geocities.com/davemcritchie/excel/temp/martin_hyperlink2.htm
Thanks, I found it at
http://www.geocities.com/davemcritchie/excel/temp/martin_hyperlinks.htm
The size is due mainly to style sheet definitions
How did you create the second version?
Both include a proper link to
http://www.geocities.com/davemcritchie/excel/sheets.htm#deletethissheet
> Microsoft has provided different means of translating Excel to
> HTML and has made that translation part of Excel versions or as an
> addin to Excel making for inconsistencies and I guess added to
> those inconsistencies are problems with language versions if
> what you indicate having problems with would work in the
> US version of Excel 97. I tend to shy away from MS creating
> any HTML from Excel unless something is too complicated to
> easily do it myself.
> http://www.geocities.com/davemcritchie/excel/xl2html.htm
I don't use Excel very much in order to create HTML - but unfortunately,
I don't have any better tool at hand that may sort rows or change
columns easily.
In fact, I just create HTML code which then gets stripped to plain html
(getting rid of silly font assignments and other styles which I don't
want to include).
> Since German is your language you might take a look at an addin
> XL2HTML-Konverter for XL5/7 hwkkonv.zip
> http://www.herber.de/free/hwhkonv.zip
> by Hans W. Herber http://www.herber.de
That's an interesting idea. Up to now I used xlHtml 0.2.8 by Steve
Grubb. I just checked, and it ignored any hyperlink in its output.
> The reverse function to show the hyperlink data of a cell is not provided
> as a Worksheet Function in Excel. But you can use VBA and you
> will find such a function, URL, on my Build Table of Contents page
> http://www.geocities.com/davemcritchie/excel/buildtoc.htm#url
> which will pull in object hyperlinks and uncomplicated =HYPERLINK
> formulas unencumbered by concatenation.
Thanks, that's an idea. I just had a look at its code.
Unfortunately, I get the idea that this formula does check for the
command HYPERLINK in a field, defined as formula.
Does this work with manual Hyperlink declarations as well?
manual means: The Hyperlink was created either by import from an HTML
file or created manually by Insert > Hyperlink... (ctrl-k).
> I think Replace would be available in Excel 97; otherwise, someone
> would probably have mentioned it by now so I expect this is strictly
> a language problem. But maybe someone would verify whether
> REPLACE is in the macro language in Excel 97 in English and
> even give you the German equivalent.
From my previous experience, correcting one word, waiting for the next
problem does not help much. It might work to use the internal format and
open it directly (providing the macros e.g. within an excel database),
causing a proper conversion of terms. This may work, as long commands
are not nested in strings and extracted from there.
Good news that Excel 2000 has one bugs less (is there any doubt that
this is a bug?), bad news that I don't have access to this.
BTW: If you want ton idea of the problem, I just try to build a
dictionary of photographic expressions,
http://people.freenet.de/traut/Phictionary.html
Links within Excel, exported properly to html, may provide good
explanations while linking from the Phictionary directly to
http://somewhere.in.the.net/glossary/letter-A#some-a-expression
David McRitchie
http://people.freenet.de/traut/Phictionary.html
That file is created like the old RTF2HTML conversions that
carry the unneeded width to every cell in a column;
whereas, only the first usage is actually meaningful.
The URL macro converts both formulas and object
hyperlinks. Look again at the coding. Extra hint the next
macro after it only does object hyperlinks on my page.
http://www.geocities.com/davemcritchie/excel/buildtoc.htm
I used my program which you can get directly to the code with
http://www.geocities.com/davemcritchie/excel/code/xl2htmlx.txt
from it's descriptive web page.
http://www.geocities.com/davemcritchie/excel/xl2htmlx.htm
but as I said I manually (by typing in) created the hyperlink for that cell.
<a href="...">...</a>
strictly for comparison purposes.
It would not be difficult to change the macro to process
a single =HYPERLINK without any tests or concatenations, but
those complications are probably why I left it alone. Perhaps
I'll add it as a function so I don't really complicate my coding.
Excel 2000 (an of course Excel 2002) provides excellent
facility for pasting a web page into Excel. This is probably
the significant feature of Office 2000 products is to be able
to cut/copy and paste from one Office product to another
of course that means Office 2000 products and higher
and interacting with Internet Explorer.
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Macros: http://www.geocities.com/davemcritchie/excel/excel.htm
Search Page: http://www.geocities.com/davemcritchie/excel/search.htm
"Martin Trautmann" <t-...@gmx.net> -- see complete thread at
http://groups.google.com/groups?threadm=slrn9qe83t.pvl.t-use%40ID-685.user.dfncis.de
Martin Trautmann
> How do you pronounce that fictionary ??
> http://people.freenet.de/traut/Phictionary.html
I don't write it that way :-)
Is it objectionable? It may be in German, but I guessed that in the
states a 'u' is common instead of an 'i'.
> That file is created like the old RTF2HTML conversions that
> carry the unneeded width to every cell in a column;
> whereas, only the first usage is actually meaningful.
Is it so?
I replaced all s/<td[^>]*>/<td WIDTH="25%"/
Yeah, you are right - http://www.teamone.de/selfhtml/tcec.htm#a2
explained me that just the first declaration is required. This will help
to reduce the size of the file.
> The URL macro converts both formulas and object
> hyperlinks. Look again at the coding. Extra hint the next
> macro after it only does object hyperlinks on my page.
> http://www.geocities.com/davemcritchie/excel/buildtoc.htm
oh, maybe it's the URL = cell.Hyperlinks(1).Address part?
> "Martin Trautmann" <t-...@gmx.net> -- see complete thread at
> http://groups.google.com/groups?threadm=slrn9qe83t.pvl.t-use%40ID-685.user.dfncis.de
did you insert this manually or is this done automatically? Nice idea,
although I prefer a news server with higher storage capacity and (which
seems to be much more common in Germany) avoiding full quotes (which are
a waste of bandwidth and storage capacity). References to previous
messages are part of every usenet posting.
Thanks - I'll have a look for the other converter you mentionned
Martin
Martin Trautmann
> I used my program which you can get directly to the code with
> http://www.geocities.com/davemcritchie/excel/code/xl2htmlx.txt
Wow, I was suprised to see that this conversion worked very well.
xl2html did create a html file at least with link, although the name
part was stripped as well. The link of HYPERLINK formulas was not
created - but that's not a major drawback.
The HTML source code is much better than Excel's.
I did not check XL2HTMLs and XL2HTMLx yet - I guesst the first is for
mutlple sheets, the second including <th> cells.
> It would not be difficult to change the macro to process
> a single =HYPERLINK without any tests or concatenations, but
> those complications are probably why I left it alone. Perhaps
> I'll add it as a function so I don't really complicate my coding.
Is there a chance to get the name part as well?
I don't know VBA, since I used the Mac side before. For AppleScript I
can look at the dictionary of every program and check the properties of
an object 'cell' - I've seen cell.formula, cell.value,
cell.Hyperlinks(1) (which may have more properties than just address?)
I just find 'Property _Cells_ as _Range_', which does not explain anything
to me.
Regards
Martin
David McRitchie
I should probably refrain from attempting humor especially concerning
and across spoken languages.Actually I have run across the word before.
I knew that only the first WIDTH was used, but I would have been
hard pressed to find it in print, in fact it is even listed now as,
"Deprecated", meaning there is a better way of doing this now.
http://www.w3.org/TR/html4/struct/tables.html#edef-TD
> oh, maybe it's the URL = cell.Hyperlinks(1).Address part?
Yes, and I expect that was a rhetorical question.
The reference to the entire thread was pasted together, and modified
to use the least amount of space on line. More information can be
seen in http://www.geocities.com/davemcritchie/excel/xlnews.htm
HTH, [alternate site is http://dmcritchie.20megsfree.com/excel/excel.htm ]
David McRitchie, Microsoft MVP - Excel
My Excel Macros: http://www.geocities.com/davemcritchie/excel/excel.htm
Search Page: http://www.geocities.com/davemcritchie/excel/search.htm
"Martin Trautmann" <t-...@gmx.net> wrote in message news:slrn9qj8tp...@ID-685.user.dfncis.de...
Martin Trautmann
> Hi Martin,
> I should probably refrain from attempting humor especially concerning
> and across spoken languages.
> > How do you pronounce that fictionary ??
> > http://people.freenet.de/traut/Phictionary.html
> Actually I have run across the word before.
Oh, you did? Where? Is there any other dictionary, e.g. on Science
Fiction?
Yeah, google told me it's a game - didn't know about this before, but I
built a bicycle dictionary (Bictionary) some years ago and thus reused
this idea.
> I knew that only the first WIDTH was used, but I would have been
> hard pressed to find it in print, in fact it is even listed now as,
> "Deprecated", meaning there is a better way of doing this now.
> http://www.w3.org/TR/html4/struct/tables.html#edef-TD
Hm, interesting - I wanted to speed up display by adding fixed width
numbers. Breaking the full table to tables per first letter should
produce matching column widths.
I understand that web design should be done independent on any hardware
or browser limitations - but sometimes a predefined width appears to be
appropriate. Is there any replacemnt for width? I did not find any.
> > oh, maybe it's the URL = cell.Hyperlinks(1).Address part?
> Yes, and I expect that was a rhetorical question.
No, not necessarily - I don't know the purpose of
cell.Hyperlinks(1).Address yet, since I don't know for sure its
properties, return values or other properties. I did not see any
explanation or help from the VB editor. Maybe the return valued 'could'
be boolean and I missed the proper definition later on.
CU
Martin
David McRitchie
I thought the name part would be no problem but it is omitted by
the VBA code so I am at a loss for that. I am using Excel 2000.
I would need help myself on that.
Even though Excel passes the name portion as in object (rightclick)
hyperlink to the browser as in
http://www.geocities.com/davemcritchie/excel/sheets.htm#deletethis
the name portion is not picked up with the url in the following code.
urladdr = "" 'include hyperlinks but not =HYPERLINK()
On Error Resume Next
urladdr = Selection.Cells(r, c).Hyperlinks(1).Address
On Error GoTo 0
Hi Martin,
I could include something for simple HYPERLINK formulas, such as
=HYPERLINK("http://myweb.com/sheets.htm#deletethis","Delete")
and which I did in my URL formula,
but I would not be able to handle the parsing required for such things as
=HYPERLINK(A1 & B1, "see " & B1)
or even worse if conditions are involved.
=IF(A1="x","",HYPERLINK(A1 & B1, B1))
which is why I don't do the formulas.
HTH, (minor comments included below within your reply)
David McRitchie, Microsoft MVP - Excel
My Excel Macros: http://www.geocities.com/davemcritchie/excel/excel.htm
Search Page: http://www.geocities.com/davemcritchie/excel/search.htm
"Martin Trautmann" <t-...@gmx.net> wrote in message news:slrn9qjd7h...@ID-685.user.dfncis.de...
> On Wed, 19 Sep 2001 14:10:43 -0400, David McRitchie wrote:
> > I used my program which you can get directly to the code with
> > http://www.geocities.com/davemcritchie/excel/code/xl2htmlx.txt
>
> Wow, I was suprised to see that this conversion worked very well.
>
> xl2html did create a html file at least with link, although the name
> part was stripped as well. The link of HYPERLINK formulas was not
> created - but that's not a major drawback.
>
> The HTML source code is much better than Excel's.
Thanks, but it does depend on what you want and what your requirements
are. I would not suit those who want or expect it to look exactly like Excel.
Likewise those used to GML, HTML, SGML probably wished some of
Excel output looked more like them, especially when printing.
> I did not check XL2HTMLs and XL2HTMLx yet - I guess the first is for
> mutilple sheets, the second including <th> cells.
The second is for printing row (1-2-3) and Column (A-B-C) headings
with the selected portion. Something very useful for including examples.