Excel 2016 - Data from Web - 100+ sheets - Combine into a single sheet

Question

I am trying to pull data for each Sector at http://www.moneycontrol.com/stocks/marketstats/sec_performance/bse/abrasives.html, into Excel 2016 using "Data", "From Web" - There are a total of 112 sectors.

Initially I was getting out of memory error on my laptop which had 4 Gb RAM, after 50 sheets. I upgraded my RAM to 16 Gb, and now I cannot go beyond 93 sheets.

How can I pull all the 112 sheets ??

Further, I have to combine all these sheets into one. I have used "Data" "Query & Connections" "Append", but am not able to go beyond 78 sheet, after which my laptop kind of goes into a loop, even with 16 Gb RAM.

Answer 1

Do you use 32-bit Office or 64-bit Office? 32-bit Office is limited with 2 GB memory in calculation and analysis.

Go to File-> Account-> About Excel, check the version of Office.

If you're using 32-bit Office, change to 64-bit Office. This might be the reason of the problem. Try it.

Answer 2

If you are trying to do this in one “hit” ie all files at the same time, perhaps you should consider a “linear” approach (with a macro for example) to collect from each file sequentially, closing each file and saving the master before opening the next.

Search on stack Overflow : excel and vba as there are several examples of this type of approach.