1

I was working on an excel macro and apparently I did not saved with a format that support vba macros. So when I open the file I cannot found the vba code. Is there a way to restore the code I wrote, especially that I pushed the save button in the editor several times during the core writing ?

I would be grateful if you can save my 4 hours work.

Vityata
  • 42,633
  • 8
  • 55
  • 100
Ghassen Ben Lazhar
  • 19
  • 1
  • 1
  • 2
  • Unless you have some sort of file version control outside of Excel, I have bad news for you. – Maciej Lipinski Oct 10 '17 at 14:56
  • 3
    You haven't saved the file for more than 4 hours while working on it? – FunThomas Oct 10 '17 at 15:06
  • I did this once on a COBOL project in college. You want to talk about tedium... If the autorecover solution below doesn't help, unfortunately you'll have to rewrite the code. – Jimmy Smith Oct 10 '17 at 15:51
  • personally i'm a bit of nerdy on saving issues. So my worbooks always save a dated copy (with forced xlsm format), copies all code relative stuff (worksheet, userform, modules, class) in dated files (date format as precise to seconds), and finally sorts out the outdated copies to recycle bin (not kill). Love Excel crashes, bugs, format coruption, electricity out ... (i had it all, but no problem there ;-) ) – Patrick Lepelletier Oct 10 '17 at 22:05
  • also, check your recyclebin, google search : Where is the autosave folder in Excel? How to recover unsaved Excel files Go to FILE -> Open. Choose Recent Workbooks. Scroll down and click on the Recover Unsaved Workbooks button at the bottom of the list. Note. ... When the Open dialog box pops up, just select the necessary file and click Open. – Patrick Lepelletier Oct 10 '17 at 22:08
  • or, Where are temporary Excel files stored? Excel automatically saves every 10 minutes to the default location of "C:\ Documents and Settings\\Local Settings\Temp" or "C:\Users\\AppData\Local\Temp" on Windows 7/Vista. The files are identifiable as they are saved as a number .tmp e.g. "28.tmp". – Patrick Lepelletier Oct 10 '17 at 22:09
  • @PatrickLepelletier - OP has vba code in xlsx file, it should not be automatically saved as a temporary file due to security reasons (unless he asked for the saving, by clicking on the checkbox in Options). The devs from Microsoft have made their job well there. – Vityata Oct 11 '17 at 07:25
  • Not saving for security reasons should be opt-in because it is a niche usage. tbh security is the often an excuse for bad designs these days. It is just a design error from Microsoft on a feature (macro) which is used by power users (so it is ok). But turns out power users are still users and they can make mistakes. When you click save in your macros editor and it does not save and not telling you so because you have missed a dialog box two hours ago. I considered that a usability bug. – FKDev Nov 12 '18 at 08:25

2 Answers2

9

You lost your work.

The good news? Every time I had to re-write a piece of code, the 2nd time around was consistently much better than the first. So, take that lost work as an opportunity!

Unless... it's possible you have Excel's autosave feature turned on:

Excel Options / Save

Look under %appdata%\Microsoft\Excel; with a little luck you'll find a copy of your work saved as a .xlsb file, and all you'll have lost is 10 minutes of work. If you're lucky.

That said...

I pushed the save button in the editor several times during the core writing

The VBE doesn't do the saving. If you bring up the VBE in a brand new unsaved workbook and hit the VBE's [Save] button, what happens is that the VBE invokes the host application's save mechanism, and thus Excel prompts you for a file name.

If you really hit the [Save] button several times in the editor, either you did save your work, or you explicitly cancelled the "Save As" dialog that popped up every time - in which case your work is indeed lost.

If you did hit that [Save] button and Excel didn't prompt you for a file name, then you did save it. Try looking for it in Excel's recent documents.

And hit Ctrl+S more than once every 4 hours!

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
  • 3
    Just for the last line - I feel the pain after losing about 6 hours many years ago. I think those buttons get hammered more than my xbox controller these days. – Darren Bartrup-Cook Oct 10 '17 at 15:46
  • Every time I had to rewrite a piece of code due to a crash or not saving, I have lost about 30 minutes of drinking coffee & swearing. Did it twice in the last 2 years. – Vityata Oct 11 '17 at 07:28
3

Short answer: No

Long answer: If you can run fast, by breaking the speed of light, you would be able to turn back time, thus you would be able to go in the past and save your file correctly.

Answer bringing some value:

  • No, but make sure that you enable autosave next time.
  • Make a habit of saving manually every 10 minutes.
  • Start using version control system, where you commit every time you have something new - Best way to do Version Control for MS Excel
Vityata
  • 42,633
  • 8
  • 55
  • 100