PDA

View Full Version : Suggestions for rebuilding personal.xls



TheAntiGates
07-09-2017, 07:28 AM
Mine is massive including multiple forms and modules. I suspect corruption so wish to reconstruct it (a couple of module level variables stopped responding to shift-F2, and now when I save changes in VBA Excel crashes). At least it's not a fully catastrophic corruption; everything seems to work, and vitally, I can still export VBA to text files.

One note that probably doesn't change things: I have so many macros that I've not only got multiple modules but multiple workbooks too. \XLSTART\ has a couple of friends living with personal.xls that I treat exactly the same way.

Here's my plan:
- Nothing to do outside of VBA; only empty worksheets.
- Create newest VBA exports to text files .BAS, .CLS, .FRM, and .FRX from Modules, Class Modules, Forms, ThisWorkbook, and Sheet code.
- I periodically accumulate (don't overwrite) generational copies when I modify code and forms, so I'll do text compares to see if I observe any radical/suspicious changes.
- Write down references in Project window (Control-R). I don't know how to export those.
- Visually browse tools/references and write down what was checked.
- Visually browse through every property in each Project Window object and write down anything that appears to be a "non-factory setting".
- Write down, copy/paste macro shortcut letters and descriptions by browsing through alt-F8 in Excel. (I don't know if importing the text .BAS files restores them. I do note that there are ATTRIBUTE statements in .BAS, one for the shortcut key and one for the description, which contain the shortcut letter and description.)
- Write down all VBA window customized menu items. The (insert prohibited banned words here: a$$^&*E Mo%&#@ F*%&$r) Microsoft interface designers have not yet destroyed the menu interface in the VBA window with a D.A. ribbon, so the customized accelerated shortcuts are valuable. However I don't know how to export that information (text file would be ideal).
- Write down any add-ins (actually, I've never seen one in my VBA window).
- Write down any digital signatures.
- Write down any VBA tools/options (in case I reinstall, something I'm really hesitant to do, really a last resort).

So, anything else, or other advice? I'm really hoping that I can delete personal.xls, reimport the text files, manually make the other changes written down above, and get back to exactly where I was.

One thing I will have to cross my fingers on are the .FRX graphics files for forms. I don't have the same comfort level with those as the pure text files. I'll just have to hope the saved ones will reimport unblemished.

TIA

rlv
07-09-2017, 10:38 AM
I assume you are aware of Rob Bovey's Code Cleaner?

http://www.appspro.com/Utilities/CodeCleaner.htm

While I think your plan covers most of the bases, if it were me, I'd try code cleaner first (after making the appropriate backup copies).

mdmackillop
07-09-2017, 10:52 AM
Note: That works only on 32bit programmes.

Logit
07-09-2017, 10:55 AM
RESTORE CORRUPT FILE


Restoring a Corrupted File
This is the reply from one of members re restoring a corrupted file


The techs at Microsoft were able to restore the Excel file!


I'm not sure what all they did (some of it was magic, evidently), but here's a tip they passed along.


If you're having a problem with an Excel file, save it as HTML (which is one of the file types in Excel), then save it back to .xls


They say that will often recover lost information, or restore a corrupt file.
=========================================================================== ======


http://directory.s2services.com/excel.htm
=========================================================================== ======


https://support.office.com/en-us/article/Repair-a-corrupted-workbook-153A45F4-6CAB-44B1-93CA-801DDCD4EA53
=========================================================================== ======


Another way of restoring broken/corrupted Excel files
I've been quite lucky actually opening corrupted files in OpenOffice, saving them, and then re-opening them in Excel. Sometimes the formatting will be a little off, but the data is almost always recovered, as well as most of the formatting. Unfortunately, not all of the formula and charts are, but when it happened I was happy anything was salvaged at all :yes

TheAntiGates
07-09-2017, 11:17 AM
Thank you. I am on XL03 (and will be until they pry it from my cold dead hands, because I like to work fast and intelligently, despite Microsoft's **** attempts to prevent that since Office 07 :mad2: :censored::boxer2: :banghead: ). I'm about to run it now. Though I implicitly trust people like Rob, John W, Jan Karel... it's still a little black box, so I'd like to also build a strong "manual" list as I have detailed above.

snb
07-09-2017, 12:30 PM
1. rename personal.xls
2. move the renamed file to another location
3. record a macro: a new personal xls file will be created.
4. open te renamed file and drag each module in the project explorer in VBEditor successively form teh renamed file iinto the personal.xls
5. test Excel's behaviour each time after dragging a new module into the personal.xls file

TheAntiGates
07-09-2017, 02:24 PM
Nice approach, snb. I might try that to save steps.

One thing I observed, the name property of forms is not shown on exports so that's one property might be added to the list in O.P.. In fact, unless they're in the .FRX file, nearly none of the form properties are exported. If you ever lose a form, I think your best hope is screen prints, or old saved working backup versions - brought in such as just suggested by snb.

I see a lot of properties to consider on ThisWorkbook that I hope are set okay. Until now I didn't even notice that they were there.

SamT
07-09-2017, 02:29 PM
I am on XL03 (and will be until they pry it from my cold dead hands, because I like to work fast and intelligently, despite Microsoft's **** attempts to prevent that since Office 07 :mad2:


I'm with you.

snb
07-09-2017, 11:33 PM
the name property of forms is not shown on exports

There's no need, it even should be discouraged to 'export' .frm or .bas files.
Dragging (in this case equivalent to copying) is the way to go.