Hyperlinks & File Size

[MaxA]

My spreadsheet has 6,000 rows by 14 colums; the 1st column is hyperlinks to
URLs. The file size is 7.5MB with the hyperlinks, but when I save the file
without the hyperlinks (copy column and paste special-values), the file is
only 0.5MB. Is this file size difference OK? The large file has some
unusual behavior (some math errors for averages) and I'm not sure if the file
is corrupt.

 

[Jim Cone]

Use the Hyperlink function instead of inserting hyperlinks.

=HYPERLINK("http://www.microsoft.com/en/us/default.aspx",B5)

A large number of the later can corrupt a workbook.
Also, the file size should be smaller.

Math errors could be from file corruption, operator error or trying to use
more than 15 decimal places.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"MaxA" <[email protected]>
wrote in message
My spreadsheet has 6,000 rows by 14 colums; the 1st column is hyperlinks to
URLs. The file size is 7.5MB with the hyperlinks, but when I save the file
without the hyperlinks (copy column and paste special-values), the file is
only 0.5MB. Is this file size difference OK? The large file has some
unusual behavior (some math errors for averages) and I'm not sure if the file
is corrupt.

 

[Dave Peterson]

Just my opinion.

First, I wouldn't guess that the math errors are caused by your hyperlinks.
I've had trouble with lots of hyperlinks in a worksheet/workbook, but not
formula problems. I'd guess those math errors are either mistakes in your
formulas or mistakes in the data. But that's just a guess.

Second, I find that using lots of =hyperlink() worksheet functions cause a lot
less problems than using lots of Insert|Hyperlink hyperlinks.

Depending on what's in your first column, maybe you could add another column
(B?) and use a formula like:

=hyperlink(a1,a1)
=hyperlink(a1,"Click me")
=hyperlink("http://"&a1, "click me")
=hyperlink("file:////" & a1, "click me")

 

[onewildchild]

You speak to average() formula errors. Just checking , do you realize that
the average formula reacts this way?

If an array or reference argument contains text, logical values, or empty
cells, those values are ignored; however, cells with the value zero are
included.
Arguments that are error values or text that cannot be translated into
numbers cause errors.


So when I do some dumps from our main frame a zero becomes an empty cell in
excel. If I need it to see a zero, it is an error in what I want to achieve.

OWC

 

[MaxA]

Dave,

Thank you so much for your response. I agree with your assessment and I was
hoping you could further help. I tried your suggestion of using
=hyperlink(a1,a1) but I have a problem. The cells with the hyperlinks disply
text which is not the hyperlink. For example, cell A2 displays text "Great
Business", and when you right-click on A2 and select "Edit Hyperlink", the
actual hyperlink is "http://greatbusiness.com/...". I added column B and
inserted "=hyperlink(a2,a2)" in cell B2. The Text in B2 displays properly
(i.e., "Great Business"), but when I click on the cell a pop-up appears with
the error message "Cannot open the specified file". I'm assuming this is
because the text in A2 is not the actual hyperlink.

Is there any way to use the =HYPERLINK function as you suggest without
actually typing in the complete URLs? There are over 6,000 and it would not
be practical.

Again, thank you so much for your help. I sincerely appreciate it.

 

[MaxA]

OWC,

Thank you. Yes I am aware of this and have replaced zeros with text (i.e.,
"N/A")so that the cells will not be included in the average calculations.

I appreciate your reponse. I believe I'm experiencing two separate and
unrelated problems. One is a pilot error (me) for the math calculations and
the other may be a corrupted Excel file due to too many Hyperlinks.

Thanks again.

MaxA

 

[Dave Peterson]

How about this.

Your original hyperlink is in column A.
You can use a formula to extract that hyperlink's address in column B
Then use the =hyperlink() in column C.

=hyperlink(b1,a1)

And then you can hide columns A and B.

Here's a User defined function that will retrieve the URL.

Saved from a previous post:

Option Explicit
Function GetURL(Rng As Range) As String
Application.Volatile

Set Rng = Rng(1)

If Rng.Hyperlinks.Count = 0 Then
GetURL = ""
Else
GetURL = Rng.Hyperlinks(1).Address
End If

End Function

So if you had a hyperlink in A1, you could put =getURL(a1) in that adjacent
cell.

Be aware that if you change the hyperlink, then this formula cell won't change
until your workbook calculates.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=getURL(a1)

=========
After you extract your email addresses, you could select that column B1:B500 and
edit|copy followed by edit|paste special values

Then you can remove the hyperlinks from column A.

Select column A.
Hit alt-f11 to get back to the VBE
hit ctrl-g to see the immediate window
type this and hit enter:
selection.hyperlinks.delete

Make sure you convert column B to values before you do this step!

Then edit|replace
what: mailto:
with: (leave blank)
replace all

or just use:
=mid(geturl(a1),8,255)
and leave it a formula.

 

[MaxA]

Jim,

Thanks; I believe I have too many inserted hyperlinks (over 6,100) and may
have corrupted the workbook. I'm trying to see if I can salvage it without
having to manually enter the hyperlinks (the cells display different text
than the actual hyperlink - i.e., they disply text "Great Business" instead
of "http://greatbusiness.com/..." - see my repsonse to Dave Peterson).

I appreciate your help and would also appreciate any thoughts you may have
to extract these "hidden" hyperlinks to rebuild the workbook (it took a lot
of time to aggregate the data). Thanks again, Jim.

MaxA

 

[Jim Cone]

You are in good hands with Mr. Peterson.
--
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html



"MaxA" <[email protected]>
wrote in message
Jim,
Thanks; I believe I have too many inserted hyperlinks (over 6,100) and may
have corrupted the workbook. I'm trying to see if I can salvage it without
having to manually enter the hyperlinks (the cells display different text
than the actual hyperlink - i.e., they disply text "Great Business" instead
of "http://greatbusiness.com/..." - see my repsonse to Dave Peterson).

I appreciate your help and would also appreciate any thoughts you may have
to extract these "hidden" hyperlinks to rebuild the workbook (it took a lot
of time to aggregate the data). Thanks again, Jim.
MaxA

 

[MaxA]

Dave,

Thank you. I am new to Macros and User Defined Functions, but your
directions were excellent. I got most of the way through your directions,
but I encountered a challenge.

I extracted the URLs and pasted special-values in column C -

After you extract your email addresses, you could select that column B1:B500 and
edit|copy followed by edit|paste special values

The URLs appear in Column C, but there is no hyperlink. That is, if you
right-click on cell C2 and then click Hyperlink, the "Text to display" field
shows the URL (i.e., http://greatbusiness.com/...), but the "Address" field
is blank (i.e., there is no hyperlink to go-to).

I stopped there before proceeding to remove the hyperlinks from column A.

Did I do something wrong or am I missing something? Any suggestions.

Thanks again, Dave. I sincerely appreciate your help.

MaxA

 

[Dave Peterson]

Did you put that UDF in column C or column B.

In my example, column A held the old hyperlink.
I put that formula (=geturl()) in column B.

But then I converted that column to values

and used this in C1:
=hyperlink(b1,a1)

Dave,

Thank you. I am new to Macros and User Defined Functions, but your
directions were excellent. I got most of the way through your directions,
but I encountered a challenge.

I extracted the URLs and pasted special-values in column C -

After you extract your email addresses, you could select that column B1:B500 and
edit|copy followed by edit|paste special values

The URLs appear in Column C, but there is no hyperlink. That is, if you
right-click on cell C2 and then click Hyperlink, the "Text to display" field
shows the URL (i.e., http://greatbusiness.com/...), but the "Address" field
is blank (i.e., there is no hyperlink to go-to).

I stopped there before proceeding to remove the hyperlinks from column A.

Did I do something wrong or am I missing something? Any suggestions.

Thanks again, Dave. I sincerely appreciate your help.

MaxA

 

[MaxA]

Dave,

Your instructions solved my problem. Thank you so much.

I put the UDF in column B; Pasted Special | Values Only in column C; and in
column D I put =hyperlink(C1, "Link"). I then re-created my spreadsheet
using a new, blank file and copied the values or formulas from the original
spreadsheet. The new file size is 3.0 MB vs. 7.5 MB before this process.

I just have two quick questions.
1) At the end of your original instructions, there was a step I did not
understand so I did not do it:What was this supposed to do and is it OK that I didn't do it?

2) My file now has 6,100 cells (in column B) with the text of the link
(i.e., "http://greatbusiness.com/...") that does not contain an embedded
Hyperlink but the value is used by column C (i.e., "=hyperlink(B2, "Link").
Is this what I need for the final solution? As I said, the file size is now
3MB and performance is significantly better. I justed wanted to make sure
I'm not creating a problem with all those cells with the URLs for their text
value.

Dave, thanks again for your patience, help and excellent directions. I
sincerely appreciate your help and you've saved me an enormous amount of time
and aggrevation. Thank you.

MaxA

 

[Dave Peterson]

#1. The mailto: stuff was left over from a previous post. Sorry.

#2. Yep. The only thing I try to remember is to make sure that when I
add/change something that looks like a hyperlink, that I hit Edit|Undo when
excel converts it to a link. Otherwise, the number of Insert|Hyperlink
hyperlinks would start increasing.

Dave,

Your instructions solved my problem. Thank you so much.

I put the UDF in column B; Pasted Special | Values Only in column C; and in
column D I put =hyperlink(C1, "Link"). I then re-created my spreadsheet
using a new, blank file and copied the values or formulas from the original
spreadsheet. The new file size is 3.0 MB vs. 7.5 MB before this process.

I just have two quick questions.
1) At the end of your original instructions, there was a step I did not
understand so I did not do it:What was this supposed to do and is it OK that I didn't do it?

2) My file now has 6,100 cells (in column B) with the text of the link
(i.e., "http://greatbusiness.com/...") that does not contain an embedded
Hyperlink but the value is used by column C (i.e., "=hyperlink(B2, "Link").
Is this what I need for the final solution? As I said, the file size is now
3MB and performance is significantly better. I justed wanted to make sure
I'm not creating a problem with all those cells with the URLs for their text
value.

Dave, thanks again for your patience, help and excellent directions. I
sincerely appreciate your help and you've saved me an enormous amount of time
and aggrevation. Thank you.

MaxA