Excel slow open/save i think because of hyperlinks??

We have a file in our company, and it takes 25mins to save.  The file is 24mb before, but now it is 5mb because I saved it in "xlsb". I've searched answers and tried disabling auto calculation and some other stuff but still can't save it faster.

The file contains 13,000+ cells of hyperlinks to our network. I think the hyperlinks are the problem.

The file is locally stored in my hard drive, I'm the one who is updating it. It only contains hyperlinks and some formulas to count how many open and close issues are there.

hyperlinks looks like this....

\\companyname.com\server112\E&PM\PM\Project Filing\Transmittals\Incoming\D1-T-P-8241

I think hyperlinks are the problem, because I''ve tried removing hyperlinks from i.e Row 5-4000, and the saving time is being reduced. Hope you can help me

Thanks, I appreciate your reply.

Hi,

Try this and see if the file save faster.

Excel options->advanced-->general-->web options-->files(tab)--(uncheck - tick box) update links on save if it's checked.

Originally Posted by elayaz

Hi,

Try this and see if the file save faster.

Excel options->advanced-->general-->web options-->files(tab)--(uncheck - tick box) update links on save if it's checked.

wow I'm surprised! it save in just 5 seconds! I really appreciate it! By the way I did not say that opening takes 10mins. How can I open faster? Is there an option for don't update links on open?

Originally Posted by FDibbins

Hi and welcome to the forum

those are just links, not hyperlinks

Are there any that reference old data, that you could probably convert to values now, that wont change any more?
Are there any that reference multiple links and then do calcs on those links? It might speed things up if you just reference the various cells and then do the calcs separately if you do, for instance instead of...
=[Book2]Sheet1!$A$1*[Book2]Sheet1!$B$1
maybe in book1
sheet1!A1=[Book2]Sheet1!$A$1
sheet1!A2=[Book2]Sheet1!$B$1
sheet1!A3=sheet1!A1*sheet1!A2

oh sorry, i thought they were hyperlinks. Now it is saving fine but opening the document is my last problem now. But Im happy the saving thing is solved.

If you are not going to click and open the webpage (hyperlink) from excel, then convert the hyperlinks to text.

Just copy all the hyperlinks and paste as values in another column. Then save the file, see if the file size is reduced or the time to open reduces.

Thanks
----------------------------------------------------------------------------------------------------------------------

If my reply helped, Please add reputation (click on the star below the post)
If the reply solves your problem, Don't forget to mark the thread as "Solved" (Thread Tools->Mark thread as Solved)

Originally Posted by elayaz

If you are not going to click and open the webpage (hyperlink) from excel, then convert the hyperlinks to text.

Just copy all the hyperlinks and paste as values in another column. Then save the file, see if the file size is reduced or the time to open reduces.

Thanks
----------------------------------------------------------------------------------------------------------------------

If my reply helped, Please add reputation (click on the star below the post)
If the reply solves your problem, Don't forget to mark the thread as "Solved" (Thread Tools->Mark thread as Solved)

I will open the links from the excel, because my co-workers will just click that link then the file/folder will be opened. Any idea on how can i open faster? I tried Options>Trust Center>Trust Center Settings>disable automatic update of workbook links

Still 10mins to open....

Ok got it. Let's try this.

Click on any cell on the sheet. Now in keyboard click Ctrl+end. See which cell is highlighted. If the selected/highlighted cell is far below the last row containing any content, or far to the right of the last such column, then try & copy all the data to a new worksheet and remove the old sheet.

Now save & see if the file size is reduced and opens faster.

Originally Posted by elayaz

Ok got it. Let's try this.

Click on any cell on the sheet. Now in keyboard click Ctrl+end. See which cell is highlighted. If the selected/highlighted cell is far below the last row containing any content, or far to the right of the last such column, then try & copy all the data to a new worksheet and remove the old sheet.

Now save & see if the file size is reduced and opens faster.

Actually I've tried that and now I'm using a new workbook and the last cell is my last data, no excess cells. Hmm....what seems to be the problem in opening?

Not sure

Now let's try this. Press F5 --->special -->objects. See if this selects any objects in your sheet.

If the sheet has objects & If you don't need those, delete and re-save.. re-open..

Originally Posted by elayaz

Not sure

Now let's try this. Press F5 --->special -->objects. See if this selects any objects in your sheet.

If the sheet has objects & If you don't need those, delete and re-save.. re-open..

It is saying no objects found, hmm what else should we do? hehe

Okay.

To check if hyperlinks is causing the slow down, duplicate the file, copy the hyperlinks column -->paste as values in a new column. Delete the hyperlink column. ( This is just to check, i know your co-workers need these links as hyperlinks)

Save the file. See if the file size is reduced or it's opening quickly.

If not then, it's something else that causing slowness, which we will need to identify..

Originally Posted by elayaz

Okay.

To check if hyperlinks is causing the slow down, duplicate the file, copy the hyperlinks column -->paste as values in a new column. Delete the hyperlink column. ( This is just to check, i know your co-workers need these links as hyperlinks)

Save the file. See if the file size is reduced or it's opening quickly.

If not then, it's something else that causing slowness, which we will need to identify..

So I've done what you told me to do, I've deleted the hyperlinks column and save it. Then I open it, only takes 1min to open compare to the 10mins before. So hyperlinks are the problem? And the file is reduced by 1mb

Ok. Cool.

Now, we will hyperlink the text we pasted (since we pasted as values). Assuming you have the webpage path in al, in b1 type

=hyperlink(a1)

& drag for all the webpages path you have in the column.

Now save the file and reopen and see if time increases.

Originally Posted by elayaz

Ok. Cool.

Now, we will hyperlink the text we pasted (since we pasted as values). Assuming you have the webpage path in al, in b1 type

=hyperlink(a1)

& drag for all the webpages path you have in the column.

Now save the file and reopen and see if time increases.

Hmm...i think i won't be able to to "=hyperlink (cell#)" because I deleted the column and pasted as values.
So the text is simply the document# and no hyperlinks ie. "P1-T-QC-SA-UA-8350"

No worries.,Just copy the hyperlinks data from the original file and paste as values in the new file.

Then do =hyperlink() and save the file.

See if the file size reduces less than the earlier file and if it's opening faster.

What i am here trying to check is, whenever you copy or import any webpage path to excel, instead of pasting using ctrl+v, paste as values.

Then use hyperlink() and give link to the webpage in excel.

Originally Posted by elayaz

No worries.,Just copy the hyperlinks data from the original file and paste as values in the new file.

Then do =hyperlink() and save the file.

See if the file size reduces less than the earlier file and if it's opening faster.

What i am here trying to check is, whenever you copy or import any webpage path to excel, instead of pasting using ctrl+v, paste as values.

Then use hyperlink() and give link to the webpage in excel.

Ok, so I tried a little experiment as you told me. Example in my starting cell =hyperlink (a1)
So in A1, there is this link to a specific folder in our server. So from the starting cell up to the last cell I copy paste "=hyperlink (a1). And even I put more links I open it in just 5 seconds. Well that's what I want.....but the links are not incrementing, I don't have time to copy paste each link to a new workbook. If I paste values it is just pasting the text itself not the link

ie. Link#1=folder1 in our network
Link#2=Folder20 in our network

I hope I've explained my point, sorry for my english. thanks for replying, I appreciate it

Sorry. I didn't got you.. If you have all the links ( as values) in a column, then in the next column type

=hyperlink(valuecolumn)

See if the attached file helps. Else please upload a excel sheet with your data ( dummy data if confidential), will take a look where it stuck.

I've seen a problem thru my experiment......so I managed to put all my link cells with hyperlinks to a folder in our network and it opens fast.
Then I changed the link into a link that will open a file.


if for example:
\\company123.com\folder1\outoing\transmittals\electrical <<even I spam the excel with this one it will open fast (5 secs)
\\company123.com\folder1\outoing\transmittals\electrical\document.pdf << I copy paste all of the cells with this one and opening is slow (7mins)

Because our links sometime contains opening a folder, and sometimes opening a pdf file or word file.

Hmm...what else can we try?

Originally Posted by elayaz

Sorry. I didn't got you.. If you have all the links ( as values) in a column, then in the next column type

=hyperlink(valuecolumn)

See if the attached file helps. Else please upload a excel sheet with your data ( dummy data if confidential), will take a look where it stuck.

I can't download a file from the net, that's our company policy. Because we are just users and don't have a privilege of an admin.

How about this one?

if for example:
\\company123.com\folder1\outoing\transmittals\electrical <<even I spam the excel with this one it will open fast (5 secs)
\\company123.com\folder1\outoing\transmittals\electrical\document.pdf << I copy paste all of the cells with this one and opening is slow (7mins)

Ok. Take a look at the file at home or somewhere else.

Whether it's a folder or file, whenever you copy-paste any folder/file path, don't use ctrl+V. Instead use paste-values and create link using hyperlink().

That should definitely keep the excel file size low and the file will open quickly. (at least for me).

Originally Posted by elayaz

Ok. Take a look at the file at home or somewhere else.

Whether it's a folder or file, whenever you copy-paste any folder/file path, don't use ctrl+V. Instead use paste-values and create link using hyperlink().

That should definitely keep the excel file size low and the file will open quickly. (at least for me).

Ok so I've done making the excel file using hyperlink(link location,[friendly name])
And it opens in 5 secs even it is a document, you are correct.

So I want to rebuild my workbook, but how can I extract the links from the previous? Because the links are not made from hyperlink(link location,[friendly name])

The links are made from "right click>edit hyperlink"

Follow the instructions in the below page to extract the path of already existing link.

http://howtouseexcel.net/how-to-extr...rlink-on-excel

Then copy and paste as values and apply hyperlink().

That should work.

Originally Posted by elayaz

Follow the instructions in the below page to extract the path of already existing link.

http://howtouseexcel.net/how-to-extr...rlink-on-excel

Then copy and paste as values and apply hyperlink().

That should work.

I'm really happy that you have helped me on this, all our workbooks now are opening and saving in just 5 seconds! haha!
I just use hyperlink(), but first I extracted the links from the slower workbooks. It just took 1 day to finish all our workbooks.

Thanks for the help, really appreciate it.

Good to know it's opening fast now