My name is XXXXX XXXXX I'll be assisting you with this issue.
Can you please upload the spreadsheet to wikisend.com if the file is confidential then please don't upload it.
Which version of excel are you using?
Hi There. The spreadsheet is confidential unfortunately. I was using 2010 but am now using the latest version as of a couple of days ago..but with the same issue.
how big is the file?
have you tried doing a select all and copy that into a new workbook
the workbook could be corrupted
No That sounds like good idea. How do I select all to copy the whole book?
press ctrl and a to select all
then ctrl and c to copy
then ctrl and v to paste in the new document
how do I do that for all the spreadsheets in the workbook at once?
press control and select each worksheet
then right click and select Move or Copy
then in the drop down arrow select the new spreadsheet
It says copy area and paste are not the same size??
no don't copy the cells
select the worksheets
at the bottom
press control and select each one
managed to do that ...it didn't fix it though.
I can sanitise the preadsheet and send to you...hoe do I upload it?
so go to wikisend.com
then upload it there. it will give you a download link
then provide me with the download link
http://wikisend.com/download/637874/My investments current with confid data removed.xlsx
let me have a look
do you know which tab has the hyperlinks?
most of the sheets have the links....if you go the individual company sheets (3 letter acronyms down near the bottom of each sheet you'll see the links)
from row 203 in each sheet after the first 10 odd sheets
make that after the first 20 odd sheets....
I went to the BHP tab
yep that should work
i can't see anywhere where it's grabbing the information from yahoo
row 203 for about 10 rows...if you click on data connections you'll see it update the link...
I don't see any data connections
i just have prev close:
with a figure of 35.80
actually its row 204 to row 210 ...this data is updated when you click on Data Connections and select BHP
I managed to find the data connection
clicked on Data then connections
so each sheet works individually...every single one of them, but my problem is when I try updating all of them in one go using Data refresh all
i'm currently trying to copy them
I'm wondering if there is an upper limit to the number of web links you can have that work with Refresh All??
but you mentioned it worked before
yes but it my have been when the sheet was smaller
which version of excel are you using
the most recent and before that 2010....it originally was working in 2010 (I think), but sometime it stopped...possibly when the sheet got bigger???
ie I'm using 2013 right now
i copied all of them into another file and currently running the refresh
looks like it hanged as well
looks liked the workbook connection can be manually refreshed indivually
so it's possible that it hangs because it tries to refresh the data all at the same time
It used to work fine...it used to go though updating a sheet at a time, and you could se it updating each shhet down the bottom of the screen...but now it just crashes? I've tried deleting about 20 sheets to see if there is a limit to the links, but it still crashes.
yes i deleted most of them and still same issue
did you generate this spreadsheet from somewhere else
I think in version 2010 you used to be able to press escape and you'd get an interesting message sequence??? But in 2013 it just hangs...
It was created in 2010 I think...but it could been something earlier.....
ok let me investigate and ill get back to you
do you have an old version that you know was working?
no, I've tried that
I wonder if its to do with the site making some change that caused it to stop working....
Yep same problem
yes i think there might be a limit
to the number of web queries
for a concurrent request
I tried deleting a bunch of sheets though (about half) and it still fails.....
well i copied 3 sheets
into a new spreadsheet
and it's also failing
when doing a refresh all
i'll now try it with 1 and see what happens
looks like it's being locked due to the number of connections
if i try the same 3 sheets it works after closing down excel
how do you mean after closing down excel?
i also tried leaving the excel file running to see if it eventually finishes
but it hasn't
been over an hour now
so definately something wrong
sayin the connection to this Internet site took longer than the alloted time
It used to update about 20 links in a matter of about 20 seconds...
Marco, I'm off to bed now (its late here in NZ). Really hoping we can get to the bottom of this...maybe Microsoft have an answer??
ok i've tested it in different scenarios, I don't think microsoft will have an answer. They will tell you that it's the excel file
i trimmed it so that it only has a few 7 sheets only with the data connections and only the Data from web:yahoo.com area and the problem still exists
So it does seem like it's the number of connections going to the yahoo that is the problem
have a look at this file: http://wikisend.com/download/133190/trimmed.xlsx
Hi there Marco.
I see that this trimmed model fails also......interestingly it didn't go to "not responding" and just hang, but rather didn't work and when you press escape you can elect to cancel.
Looking online at what others say It looks like I might need to use VBA to get around some sort of limitations of excel. Not sure how to do that but it may be that this is the next step for me?
it may be possible that VBA might fix the problem
but I'm not a VBA coder unfortunately
Marco I think we'll have to abandon this then ok.
ok no problems