XL: Workbooks Containing User Forms Take Excessively Long Time to Save Across WAN Connections (254733)

WORKAROUND

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, please visit the following Microsoft Web site: For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site: To work around this problem, the following Microsoft Visual Basic for Applications (VBA) macros copy the document to the local drive, and then share that local copy back to the WAN.

To implement the workaround, follow these steps:

1. Start Excel and open the VBA editor by pressing ALT+F11.
2. On the Insert menu, click Module.
3. Type the following code in the new module:

   Option Explicit
   Private Const TEMPFILE As String = "c:\dlocal.xls" 
   'Note, ensure the file path is valid.
   
   Private Const TEMPFILE2 As String = "c:\dlocal2.xls" 
   'Note, ensure the file path is valid.
   
   Private strOriginalName As String
   Public bSaving As Boolean
   
   Public Sub SaveLocalThenCopy()
       Dim wbk As Workbook, wbk2 As Workbook
       Set wbk = ThisWorkbook
       If UCase(ThisWorkbook.FullName) <> UCase(TEMPFILE) Then strOriginalName = ThisWorkbook.FullName
       If Dir(TEMPFILE) <> "" Then Kill TEMPFILE
       If Dir(TEMPFILE2) <> "" Then Kill TEMPFILE2
       ThisWorkbook.SaveAs TEMPFILE
       ThisWorkbook.SaveAs TEMPFILE2
       FileCopy TEMPFILE, strOriginalName
       Set wbk2 = Workbooks.Open(strOriginalName)
       bSaving = False
       wbk.Close False
   End Sub
   						

   The following steps add code to the workbook's BeforeSave event to run the workaround above.
4. In the VBAProject window, double-click the ThisWorkbook icon to open the Code window.
5. At the top of this new Code window, there are two drop-down lists. In the Object (left) list, click Workbook; in the Procedure (right) list, click BeforeSave.
6. Type the following code in the Code window:

   Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
       If bSaving Then Cancel = False: Exit Sub
       Dim iAns As Integer
       iAns = MsgBox("Do you wish to Save As...?", vbYesNoCancel, "Save")
       Select Case iAns
           Case vbYes
               Cancel = True
               bSaving = True
               Application.Dialogs(xlDialogSaveAs).Show
               bSaving = False
               Exit Sub
           Case vbNo
               'Do normal - local save.
           Case vbCancel
               Cancel = True
               Exit Sub
       End Select
       bSaving = True
       Cancel = True
       Application.OnTime Now + TimeValue("00:00:01"), "SaveLocalThenCopy"
   End Sub
   					

Now, when you click Save As, Excel prompts you to choose either the default Excel Save As dialog box (click Yes), or to use the workaround above to save the document (click No).