PDA

View Full Version : [SOLVED] Excel Crashes when Saving workbook



fredlo2010
04-04-2014, 07:37 AM
Hello guys,

I have a problem with a workbook and really do not know how to fix it. I have been looking around Link (http://answers.microsoft.com/en-us/office/forum/office_2007-customize/excel-2007-crashes-when-saving-an-xlsm-file-filled/446ec156-84c6-460c-b7c6-75bec12216a8) but no luck. Basically it crashes every time I tried to save it via VBA.

These are all the things I have tried:


Save it manually (works)
Using Workbooks.Save (fails)
Using Workbooks.SaveAs (fails)
Using Workbok.Close true (fails)
Removing all ActiveX controls (fails)
After using CodeCleaner (fails)


Any ideas ?

This is the code I am using; its part of a bigger workbook that updates a sheet in several files. All the other files work.



Private Sub Update(strFileName, strPassword, strHomeName, intHomeRow, intHomeCol)


Dim wbToUpdate As Workbook


' Open the specified macro and make the fund sheet visible
Set wbToUpdate = Workbooks.Open(strFileName, False, False, , strPassword, strPassword)

With wbToUpdate
.Sheets(strDataSheet).Visible = True

' Paste the information
.Sheets(strDataSheet).Select
Cells(1, 1).Select
ActiveSheet.Paste

' Hide the sheet
.Sheets(strDataSheet).Visible = xlSheetVeryHidden


' Select the landing Home page and its corresponding cell.
.Sheets(strHomeName).Select
Cells(intHomeRow, intHomeCol).Select

' Save and close the workbook

'.Save
'.SaveAs strFileName, 52, strPassword, strPassword
'.Close SaveChanges:=True

.Close

End With
End Sub

Thanks a lot for the help :)

mancubus
04-04-2014, 08:07 AM
hey fredlo.
i dont know if this is related with the problem. what is strDataSheet?

fredlo2010
04-04-2014, 08:20 AM
Hi,

That was good question. "strDataSheet" is a module public constant for the name of the sheet where the data will be pasted. I changed it to "m_strDATA_SHEET_NAME" it's better now. :)

SamT
04-04-2014, 09:13 AM
:dunno

It might be the fact that you are hiding an active sheet.


Private Sub Update(strFileName, strPassword, strHomeName, intHomeRow, intHomeCol)

Dim wbToUpdate As Workbook


' Open the specified Book and paste the data from the clipboard
Set wbToUpdate = Workbooks.Open(strFileName, False, False, , strPassword, strPassword)
With wbToUpdate
.Sheets(m_strDATA_SHEET_NAME). Cells(1, 1).Paste


' Select the landing Home page and its corresponding cell. Redundant now?
.Sheets(strHomeName).Cells(intHomeRow, intHomeCol).Select
.Close SaveChanges:=True
End With

End Sub

fredlo2010
04-04-2014, 10:22 AM
Thanks for the suggestion Sam. I tried that just now and still nothing. :(

SamT
04-04-2014, 12:28 PM
This is the code I am using; its part of a bigger workbook that updates a sheet in several files. All the other files work.

Is the code that works on the other files substantially the same as this code?
What happens with

With wbToUpdate
.Sheets(m_strDATA_SHEET_NAME). Cells(1, 1).Paste

Do Events

' Select the landing Home page and its corresponding cell. Redundant now?

fredlo2010
04-04-2014, 12:33 PM
I forgot to mention that I also tried that and no luck.

Yep, its the same code. The outer Code is basically a loop that goes through a range and gets file names, paths and passwords.

Thanks for the help Sam.

SamT
04-04-2014, 02:23 PM
The code works on all but one workbook? :p

fredlo2010
04-04-2014, 02:34 PM
Yep!!! but its super old and complicated workbook that needs more than re-factoring. It has about thousands of cell styles, hundreds of conditional formatting. Because the macro keeps added stuff before clearing old data :) Every time I see it I tremble. :P

SamT
04-04-2014, 03:35 PM
Then the problem is in that workbook.

Until you get around to refactoring it, you can try a cludge that might work.


Private Sub Update(strFileName, strPassword, strHomeName, intHomeRow, intHomeCol)

Dim wbToUpdate As Workbook


' Open the specified Book and paste the data from the clipboard
Set wbToUpdate = Workbooks.Open(strFileName, False, False, , strPassword, strPassword)
With wbToUpdate
.Sheets(m_strDATA_SHEET_NAME). Cells(1, 1).Paste


' Select the landing Home page and its corresponding cell.
.Sheets(strHomeName).Cells(intHomeRow, intHomeCol).Select

If wbToUpdate.Name = "Old Ugly Workbook Name" Then
For i = 1 to 1000000
For j = 1 to 10000
j = j
Next j
Do Events
Next i
msgbox "Closing Mr. ugly now!"
.Close SaveChanges:=True
Else
.Close SaveChanges = True
End If
End With

End Sub

i is big changes in delay, j is small changes. Adjust i until the book always closes, then narrow in on the ideal delay with j. If it works at all.

With a 1Ghz processor, i*j as above = 10 seconds + DoEvents about every 10 milliseconds

fredlo2010
04-05-2014, 07:16 AM
I will try this on Monday Sam

Thanks

SamT
04-05-2014, 07:33 AM
It is a built-in bottleneck, so put it in your bug tracker.

fredlo2010
04-09-2014, 08:32 AM
Hi Sam,

I to use the code but still does not work. Hangs in there until it crashes. I just showed the users a message saying that this particular Workbook has to be updated manually.

Thanks a lot for the help

SamT
04-09-2014, 02:33 PM
Well, I guess you know which workbook needs work on next.

fredlo2010
04-11-2014, 08:53 AM
I know right :)