How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask businesslegions Your Own Question

businesslegions
businesslegions, IT Consultant
Category: Microsoft Office
Satisfied Customers: 688
Experience:  An IT Consultant with over 10 years experience
72952988
Type Your Microsoft Office Question Here...
businesslegions is online now
A new question is answered every 9 seconds

Hi there.I have web links established in a workbook involving

Resolved Question:

Hi there.
I have web links established in a workbook involving multiple spreadsheets (about 60 odd) and they all work fine individually, but when I use the refresh all command it always hangs.
Submitted: 1 year ago.
Category: Microsoft Office
Expert:  businesslegions replied 1 year ago.

Marco :

Hi there,

Marco :

My name is XXXXX XXXXX I'll be assisting you with this issue.

Marco :

Can you please upload the spreadsheet to wikisend.com if the file is confidential then please don't upload it.

Marco :

Which version of excel are you using?

Customer:

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.

Marco :

how big is the file?

Customer:

6 meg

Marco :

have you tried doing a select all and copy that into a new workbook

Marco :

the workbook could be corrupted

Customer:

No That sounds like good idea. How do I select all to copy the whole book?

Marco :

press ctrl and a to select all

Marco :

then ctrl and c to copy

Marco :

then ctrl and v to paste in the new document

Customer:

how do I do that for all the spreadsheets in the workbook at once?

Marco :

press control and select each worksheet

Marco :

then right click and select Move or Copy

Marco :

then in the drop down arrow select the new spreadsheet

Customer:

It says copy area and paste are not the same size??

Marco :

no don't copy the cells

Marco :

select the worksheets

Marco :

at the bottom

Marco :

press control and select each one

Customer:

managed to do that ...it didn't fix it though.

Customer:

I can sanitise the preadsheet and send to you...hoe do I upload it?

Marco :

use wikisend

Marco :

so go to wikisend.com

Marco :

then upload it there. it will give you a download link

Marco :

then provide me with the download link

Customer:

http://wikisend.com/download/637874/My investments current with confid data removed.xlsx

Marco :

ok thanks

Marco :

let me have a look

Marco :

do you know which tab has the hyperlinks?

Customer:

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)

Customer:

from row 203 in each sheet after the first 10 odd sheets

Customer:

make that after the first 20 odd sheets....

Marco :

I went to the BHP tab

Customer:

yep that should work

Marco :

i can't see anywhere where it's grabbing the information from yahoo

Customer:

row 203 for about 10 rows...if you click on data connections you'll see it update the link...

Marco :

I don't see any data connections

Marco :

i just have prev close:

Marco :

with a figure of 35.80

Customer:

actually its row 204 to row 210 ...this data is updated when you click on Data Connections and select BHP

Marco :

I managed to find the data connection

Marco :

clicked on Data then connections

Customer:

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

Marco :

ok

Marco :

i'm currently trying to copy them

Customer:

I'm wondering if there is an upper limit to the number of web links you can have that work with Refresh All??

Marco :

but you mentioned it worked before

Customer:

yes but it my have been when the sheet was smaller

Marco :

which version of excel are you using

Customer:

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???

Customer:

ie I'm using 2013 right now

Marco :

i copied all of them into another file and currently running the refresh

Marco :

looks like it hanged as well

Marco :

looks liked the workbook connection can be manually refreshed indivually

Marco :

so it's possible that it hangs because it tries to refresh the data all at the same time

Customer:

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.

Marco :

yes i deleted most of them and still same issue

Marco :

did you generate this spreadsheet from somewhere else

Customer:

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...

Customer:

It was created in 2010 I think...but it could been something earlier.....

Marco :

ok let me investigate and ill get back to you

Customer:

great tks.....

Marco :

do you have an old version that you know was working?

Customer:

no, I've tried that

Marco :

same problem?

Customer:

I wonder if its to do with the site making some change that caused it to stop working....

Customer:

Yep same problem

Marco :

yes i think there might be a limit

Marco :

to the number of web queries

Marco :

for a concurrent request

Customer:

I tried deleting a bunch of sheets though (about half) and it still fails.....

Marco :

well i copied 3 sheets

Marco :

into a new spreadsheet

Marco :

and it's also failing

Marco :

when doing a refresh all

Marco :

i'll now try it with 1 and see what happens

Marco :

looks like it's being locked due to the number of connections

Marco :

if i try the same 3 sheets it works after closing down excel

Customer:

how do you mean after closing down excel?

Marco :

yes

Marco :

i also tried leaving the excel file running to see if it eventually finishes

Marco :

but it hasn't

Marco :

been over an hour now

Marco :

so definately something wrong

Marco :

sayin the connection to this Internet site took longer than the alloted time

Customer:

It used to update about 20 links in a matter of about 20 seconds...

Customer:

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??

Marco :

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

Marco :

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

Marco :

So it does seem like it's the number of connections going to the yahoo that is the problem

Marco :

have a look at this file: http://wikisend.com/download/133190/trimmed.xlsx

Customer:

Hi there Marco.

Customer:

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.

Customer:

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?

Marco :

Hi there

Marco :

it may be possible that VBA might fix the problem

Marco :

but I'm not a VBA coder unfortunately

Customer:

Marco I think we'll have to abandon this then ok.

Marco :

ok no problems

businesslegions, IT Consultant
Category: Microsoft Office
Satisfied Customers: 688
Experience: An IT Consultant with over 10 years experience
businesslegions and 5 other Microsoft Office Specialists are ready to help you

JustAnswer in the News:

 
 
 
Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.
 
 
 

What Customers are Saying:

 
 
 
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
< Last | Next >
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
  • Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know. Robin Elkton, Maryland
 
 
 

Meet The Experts:

 
 
 
  • jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
< Last | Next >
  • http://ww2.justanswer.com/uploads/JS/jstinehelfer/2012-6-6_215146_Pictureofme1.64x64.png jstinehelfer's Avatar

    jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
  • http://ww2.justanswer.com/uploads/JA/JasonJames122/2011-12-16_135647_jasonjamesheadshotweb.64x64.jpg JasonJames122's Avatar

    JasonJames122

    Computer Enthusiast

    Satisfied Customers:

    0
    I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business
  • http://ww2.justanswer.com/uploads/JE/jessmagz/2012-6-6_18129_jm.64x64.jpg Jess M.'s Avatar

    Jess M.

    Computer Support Specialist

    Satisfied Customers:

    301
    Computer Software Specialist for more than 10 years
  • http://ww2.justanswer.com/uploads/CH/chutz747/2011-11-6_14231_me.64x64.jpg The-PC-Guy's Avatar

    The-PC-Guy

    Computer Manager

    Satisfied Customers:

    274
    20 years experience providing remote computer support
  • http://ww2.justanswer.com/uploads/SP/spherrod/2012-6-6_174244_1000852.64x64.JPG Steve Herrod's Avatar

    Steve Herrod

    Computer Support Specialist

    Satisfied Customers:

    125
    Trained in Microsoft Office from 2000 through to latest version 2010.
  • http://ww2.justanswer.com/uploads/SE/Seashore2011/2011-12-6_33418_111205223015.64x64.jpg Chris L.'s Avatar

    Chris L.

    Support Specialist

    Satisfied Customers:

    124
    Certified Expert with over 10 years experience.
  • http://ww2.justanswer.com/uploads/KR/krkljatorm/2012-6-24_153417_ITMiro.64x64.jpg IT Miro's Avatar

    IT Miro

    Computer Scientist

    Satisfied Customers:

    97
    Bachelor's Degree in Information Technology, Microsoft Certified Professional
 
 
 
Chat Now With A Microsoft Office Technician
businesslegions
businesslegions
17 Satisfied Customers
An IT Consultant with over 10 years experience