Excel Crashes when Saving workbook

[fredlo2010]

Hello guys,

I have a problem with a workbook and really do not know how to fix it. I have been looking around Link 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]

hey fredlo.
i dont know if this is related with the problem. what is strDataSheet?

[fredlo2010]

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]

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]

Thanks for the suggestion Sam. I tried that just now and still nothing.

[SamT]

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]

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]

The code works on all but one workbook?

[fredlo2010]

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]

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]

I will try this on Monday Sam

Thanks

[SamT]

It is a built-in bottleneck, so put it in your bug tracker.

[fredlo2010]

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]

Well, I guess you know which workbook needs work on next.

[fredlo2010]

I know right