Refresh external data, crashes immediately, after latest update

Question

Excel 64-bit version 1710 (16.0.8625.2121)

An external data range in an XLSB file (external data come from a tab delimited file, about 900,000 rows) which I have been refreshing weekly for over 2 years, is now crashing Excel when I try to refresh it with new data. It is set to prompt for the file name, and it does not even get to the file prompt dialog box -- the "Microsoft Excel has stopped working" message appears immediately.

The refresh worked last week (and weeks and weeks before), so I am assuming it relates to a recent update. I see in Programs and Features that Office 365 click-to-run had an update earlier today.

I have another copy of Excel 64-bit (1705) on a different machine (older and with less RAM), and it refreshes the external data range just fine -- just takes about twice as long. I used a copy of the same XLSB file. Both pcs are running Windows 10 pro.

I also tried using an older version of the XLSB file on the Excel 1710 machine, and it exhibits the same problem, so I don't think it can be file corruption. It looks to me that it is something in the Excel update that has caused the disruption.

Any advice? Is it possible to "un-"update so I can get back to work with this important weekly report?

Answer 1

Office version: 1710 (Build 8625.2127 Click-to-run)

The external data file is a tab-limited text file, not a workbook. The text file is actually a .rpt file, exported from SQL server. But it is just a text file, not a workbook.

I created the connection over a year ago, using Excel 2010 64-bit, so it uses the legacy "from file, text/csv" sort of connection.

Refreshing the connection has been working just fine since I started using Excel 2016 64-bit about 6 months ago. It only started throwing the error this week, on Friday when I tried to refresh it.

As I said, I have a second copy of Office 2016 64-bit (an older version -- 1705 Build 8210.2200 click-to-run), and it does not produce the error.

I will check out your link and see if the roll-back helps solve this issue.

I have noticed several other problems with Excel in the last few months, and have already given feedback from within Excel. Along with the fundamental interface changes (SDI, recent file list, no recent folders, etc), Excel has been more and more difficult to work with. I spend more and more time finding workarounds for its problems. It is less and less friendly for power users.

Answer 2

Hi ExcelSince1992,

I notice the version of the Office you use is not the latest version. You can update the Office to the latest Version 1710 (Build 8625.2132) to see the result.

On the other hand, we don’t need the real .rpt file with the data, is that convenient for you to create a .rpt file with some test data? We just want to collect the file to test by our side.

Thanks,

Rena

Answer 3

Thanks for following up. However, I cannot supply the .rpt file, as it contains private customer information which cannot be shared. 

Also, I decided not to revert to a previous version. It was quicker to rebuild the source data page. I just don't have the time to go through a full re-installation of office, and I have too many settings and QAT customizations to have to recreate in the event that the program defaults are put in place as a result of the reinstallation.

The source file is also used in another Excel report file, and that too crashes immediately (but works fine on the older version of office).

This week's file is 891,242 lines long , in UTF-8 format. It is tab-delimited, with 18 fields and a CRLF at the end of each line. The length of the file changes a bit each week, but (so far) it has always been less than a million rows long.

Funnily, the external data refresh works fine on a 5-year old, 16 gb RAM, 4-core PC (with the slightly older version of Office 2016), but fails on a 6-month old, 32 gb RAM, 8-core PC.

Answer 4

Hi ExcelSince1992,

OK, you can provide us the result after you revert back the version.

In the meantime, please provide us a sample .rpt file so we can test by our side in different versions to see the result. I have sent you a private message to collect it. Please access it via the link below:

https://answers.microsoft.com/en-us/privatemessages/inbox

Please also help to confirm the steps:

1.       Data > From Text/CSV > select the .rpt file to import data.

2.       Data > Refresh all to refresh the data.

Thanks,

Rena

Answer 5

Hi ExcelSince 1992,

Please help to confirm the information below for our investigation:

1. The exact Office version you are using. Please go to Excel > File > Account > Product information to check. For example: For example: 1708(8431.2031)
2. Do you insert the external data by Data > Get Data > From file > From workbook?

In the meantime, here’s the reference for your need: How to revert to an earlier version of Office 2013 or Office 2016 Click-to-Run.

Thanks,

Rena