Nov 06 2020 03:34 PM
Nov 06 2020 03:34 PM
This has caused me countless DAYS of reconstructing a semi complicated workbook of both my personal finances and of our ministry in Guatemala which is where we are currently living; and now it has happened again.
The first time this happened was after renewing my subscription to MSOffice Standard 2019. So I researched the web and did not find any answers or even suggestions. I thought it might be to some upgrades Microsoft performed. I first tried to recover back to before I renewed without success. Then I tried to download a current version of Office Standard 2019 but only received Office 365 from the cloud. But the same thing happened. Then I scanned my laptop from top to bottom and everywhere in between for any unknown viruses or malware only to find none. This is when I decided to reset my laptop to factory settings and reloading every program that I use including Office Standard 2019, but for some reason Microsoft would allow me to download the program I just purchased two weeks prior. After countless attempts to call tech support and being passed on the next Service provider having to repeat my name and provide proof it is me and then repeat the issue, I finally found someone that was kind enough to email me the directions to download what I paid for.
Finally, after downloading and installing Office Standard 2019. I opened my workbook only find the message, that there was an error found and needed to be recovered. After saving the file as a different name let Excel recover. BAM! Every table was disabled, every connection between tables was disabled, All external references for updating information was disabled, every conditional formatting was disabled. I had to reconstruct the entire last years of data over again in a new workbook.
Problem solved until just before I started typing this. EXCEL did it to me again. And now, because the great minds of Microsoft can't figure out why it is happening, I HAVE TO RECONSTRUCT IT AGAIN!!!!!!!!!!!!!
Nov 07 2020 07:46 AM
No specific suggestions, although I'd be happy to help if I can.
First, from a problem diagnostic point of view (having taught diagnostic thinking in technical environments for many years in my pre-retirement career)... just as a medical doctor isn't just going to prescribe X treatment based on the last set of patients with similar sounding complaints, but is first going to do some testing and asking of questions to get THIS patient's precise symptoms (or risk being sued for malpractice), it's important to know a bit more to be able to eliminate possible causes and think of what could be happening:
And so forth. Those really only start to scratch the surface.
Not directly relevant to your situation, but part of the reason that I'm happy to help is that I'm an MK and TCK myself, having grown up mostly in Asia where my parents had their ministry.
Nov 07 2020 10:03 AM
With the permission of all those involved,
I would like to provide you with this information in addition to the very good
and very interesting problem diagnostics from Mr. mathetes
(which I recommend you carry out and provide feedback).
Thank you for your patience and time.
I know I don't know anything (Socrates)
Nov 08 2020 05:58 PM
Nov 08 2020 06:12 PM
Thank you@NikolinoDE for the suggestion to review the Recover an earlier version of an Office file. I have read the article and it will not work as there are no unsaved files.
Nov 09 2020 03:17 AM
In the event that it was once saved in OneDrive.
I don't know if this will lead to success, but you could try it.
Restore overwritten data in Excel - via OneDrive backup If you save your Excel files on OneDrive in order to share them with colleagues, to edit them together or to use them on multiple devices, you automatically have a backup of the last saved versions.
OneDrive uses a simple version management system that enables you to retrieve the latest backup status of all files at any time.
To do this, just do the following:
Right-click the desired file in the OneDrive folder (in Explorer).
Select the "Version history" from the context menu.
There you will find the last backups of the file.
Click on the three-point icon on the right of the version you want.
There you can restore the Excel file under its original name or download it and save it under a different name in order to compare the two versions.
Thank you for your understanding and patience
I know I don't know anything (Socrates)
Nov 09 2020 03:20 AM
Nov 09 2020 07:59 AM
Well, let's go through your answers and maybe ask some follow-ups. As I said at the bottom of the list of original questions: And so forth. Those really only start to scratch the surface.
I was hoping just to prime the pump of your thinking. Particularly since you have, experience in diagnosing....let's take it further.
1. Is this the only file (or set of files) that gives you this problem? Assuming there are other Excel files that function smoothly, repeatedly, Answer: At first it happened with other workbooks until I downloaded and installed the last time. Now it appears to be just this one workbook of Spreadsheets. That is helpful info, and leads to the following which were intended as follow-ups the first time around. We just need to take them further.
2. what's distinctive about this one (this set)?
a. size of file(s) Answer: 403 KB But what's distinctive: 403 KB doesn't seem exceptionally large (in fact, I'm surprised at how small it is)....but how does it compare with the other files you routinely use is what the question was getting at.
b. number of linked sheets/workbooks I had the impression there were linkages to other workbooks or other spreadsheets. Is that the case? If so, is that unique in some way or other.
3. How many times are you able to open it and use it successfully without a problem (if at all)?
Answer: Approximately one (1) week. Is this one time per week, or for the period of one week it works successfully, and then not? I.e., be more detailed.
4. How long do you keep the file open during a typical session?
Answer: this depends on what needs to be done for the day’s input.
Well, again, what we're looking for is whether there's something about the extent of time it's open that might somehow contribute to the problem..... so when it happens, is it after a particularly long session? Follow-up questions like that.
5. Does this error condition occur after short sessions or long sessions (or no pattern discernible)? Answer: It has not happened enough to create a discernible pattern. (OK, that last question has been asked...but try to start noticing)
6. Are you the only one who has access to (who uses) this workbook? Answer: Yes
a. If not, what is the experience of others?
7. What about the machine on which you're doing the work;
a. How old is it? Answer: 7 years, ASUS Laptop, Core I7, 1TB Storage
I'm not familiar with that hardware. I use Macs only (for the last 20 years or so); my older iMac--since demoted to a minor project, digitizing my collection of LPs--is so old that its OS can no longer be updated. It's older than 7 years old.... I wouldn't totally discount this; but don't want to assume it's the cause.
8. Do you have another Mac/PC available to you and have you tried it? Answer: Yes, 4 Year old ASUS Laptop, Core I5, 500GB Storage. Yes, I transferred via Thumb Drive and same thing happened. So this is telling me that the file must be stored locally (on your system). Do you have access to one of the many cloud services? I notice that @NikolinoDE has suggested Microsoft's OneDrive service. I have no idea whether that's available internationally, but would be very surprised if it's not. Personally I'm very happy with the service, because I can go back and forth easily from place to place, machine to machine, sharing files with my wife, and others on occasion..... It also, conveniently, so long as you're working on a file that is up there in the cloud, stores it as you go along working...(when you're working with programs in the Microsoft suite of office software)...in other words it's integrated with the software nicely. Just a question/suggestion; it may or may not serve to resolve the problem.
9. What's the nature of your internet link in Guatemala (or is this all on your Mac/PC)? Answer: Fairly Stable but not what at the strength we pay for. This could be a hindrance for cloud services, I realize.
a. Are there other things about your physical location that might be a factor (stability of power source, etc) Answer: Power here is as stable as the weather and the volcanic activity. We have brown outs and spotty outages frequently, even throughout the day. Laptops, powered as they are from their internal batteries, shouldn't be affected by brown outs and the like.,,,,but you were an electronic tech in the service so might know otherwise whether a surge or drop could affect data integrity of files...
b. When you're home on furlough or for a home visit to the states, does the same thing happen? Answer: Last year when we were on back in the states on furlough, this was not an issue. We have not been back in over a year. I don't want to "jump to cause" on this, since there are so many other variables (nature of file, size of it, whatever, could also have changed), but that could be significant.
Bottom line: nothing of real significance has popped out yet. Those questions were definitely not rhetorical (to respond to that)....
For the time being, I am thinking the problem may lie in something about the file itself. I just noticed--I hadn't looked at your image of the error message before--now that I have I notice that it's a file with "xlsm" as it's file type. That would suggest it's a file containing macros. Is that the case? Your original posting refers to connections between tables being lost [this is where I had inferred possible connections with other spreadsheets].... it's great that you have such connections, assuming a good design enabling lookups for budget categories by code, etc. etc.
Anyway, I'm now wondering if there's just something about the way the thing is set up that is prone to corruption, somehow more vulnerable....
And if there are macros, for what purpose are they there? I've used spreadsheet macros maybe five times in 40 years. I'm a believer in using the power of functions whenever possible, and new ones keep getting added. I've seen people write macros that are utterly unnecessary....which isn't to say that's always the case, but especially people with programming experience can fall for the temptation to do for themselves what Excel already does very well, but maybe in one of those obscure functions that rarely see the light of day.
All of which is to say, if there are macros, what are they doing?
And that's enough for now.
Nov 12 2020 07:25 AM
Thank you@NikolinoDE for this info. I was not able to find "Office" inside "Microsoft". Only "Windows" folders. I checked all folders and did not find "Office". Thanks though.
Nov 12 2020 10:34 AM
Nice that I could help you.
However, if you will allow me to follow the approach to problem detection and resolution from Mr.
Wish you a nice day / night with lots of health, joy and love.
I know I don't know anything (Socrates)
* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.