PDA

View Full Version : Huge working excel document



lilybates
05-24-2018, 09:00 AM
I created huge excel document couple months ago. Almost every day I added a lot of data in it. Today I've decided to separate it to some smaller files. At a half of process Excel 2007 bugged and was closed. Now at opening I have only a half of data in my file. I've no idea what to do to repair excel file, I'm afraid to do some actions after this trouble.

mattreingold
05-24-2018, 10:47 AM
Sometimes when excel crashes and you re-open it, a pane on the left hand side comes up prompting you with cached previous revisions/saves, did this happen for you? Also - where were you exporting it, those may have saved? For future aid, I would suggest backing up important files, especially when you are about to perform a large scale or high risk task on it.

I hope the recovery window stored your previous version!

Isarez
05-27-2018, 10:42 AM
I created huge excel document couple months ago. Almost every day I added a lot of data in it. Today I've decided to separate it to some smaller files. At a half of process Excel 2007 bugged and was closed. Now at opening I have only a half of data in my file. I've no idea what to do to repair excel file, I'm afraid to do some actions after this trouble.
The first thing to try is to open it with the Repair option in Excel. You can do this by clicking on File and then Open. In the Open dialog box, single click on the Excel file and then click on the small arrow next to the Open button.

Go ahead and click on Open and Repair and you’ll get a message asking if you want to repair, which will recover as much data as possible or if you simply want to extract data, which will copy out the formulas and values.

You can try to repair first and see if it can recover the whole file, if not, you can then try to extract the data with the help of Excel Online Service (https://onlinefile.repair/en/excel.html)

SamT
05-28-2018, 05:31 PM
Good luck on your file recovery.

you might want to create a "Backup" folder in the folder your file is in and add this to your File's ThisWorkbook Code


Private Sub Workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As Boolean)
Me.SaveCopyAs (Me.Path & "\Backup\" & CDbl(Now) & "- " & Me.Name)
End Sub

The "CDbl(Now)" insures that all backups have a unique Name and that they will be listed in Windows Explorer in chronological order.

You can change "Me.Path & "\Backup"" to any valid path.

It's possible that you ant to save backups of many files in the same folder. In Excel, this same code will work for all workbooks of any type. You will need to edit it for different applications.


Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As Boolean)
Dim myPath As String
Dim myName As String
Dim myExt As String
Dim Dot As Long

With Me
myPath = "C:\Archive\" 'Edit to suit. Note trailing backslash
Dot = InStrRev(.Name)
myName = Mid(.Name, 1, Dot - 1)
myExt = Mid(.Name, Dot)
End With

Me.SaveCopyAs (myPath & myName & " - " & CDbl(Now) & myExt)
End Sub

That will display in Windows Explorer in Name order, then in chronological order...ie
MyWorkbookName - 123456.78912345.xlsm
MyWorkbookName - 123456.79012345.xlsm
MyWorkbookName - 123457.78912345.xlsmNyWorkbookName - 123456.78912345.xlsm
NyWorkbookName - 123456.79012345.xlsm
NyWorkbookName - 123457.78912345.xlsm