Controlling Alerts and Updating in MS Excel OLE Server

Workaround 2: Write Code to Avoid the Behavior

1. In Microsoft Project, create a new module sheet. To do this, on the Tools menu, click Macros, and then click New. For the Macro Name, type Delete_WorkSheet and click OK.
2. In the module, on the Tools menu, click References.
3. In the References dialog box, select the Microsoft Excel 5.0 Object Library check box and click OK.
4. On the new module sheet, enter the following macro (notice that the first and last line of code already exists from step 1 and does not need to be repeated):

   Sub Delete_Worksheet()
   
            ' Dimension variables.
   
            ' This assumes that a reference has been made to
            ' the Microsoft Excel 5.0 Object Library.
            Dim oXL As Excel.Application
            Dim oWBook As Object
   
            ' Starts a new invisible instance of Microsoft Excel.
            Set oXL = CreateObject("Excel.Application")
            ' Adds a new workbook to the running instance of Microsoft Excel.
            Set oWBook = oXL.Workbooks.Add
            ' Moves the first sheet of the workbook into a new workbook
            ' and makes the new workbook active.
            oWBook.Sheets(1).Move
            ' Closes the new workbook containing the undesired sheet
            ' without saving changes.
            oXL.ActiveWorkbook.Close False
            ' Save the original workbook minus the first sheet with
            ' name as listed below. If running the procedure on the
            ' Macintosh, you will need to change the next line to a valid
            ' location on the hard drive similar to the following:
            ' oWBook.SaveAs FileName:="Macintosh HD:test.xls"
            '
            oWBook.SaveAs FileName:="C:\my documents\test.xls"
            ' Closes the original workbook without saving changes.
            oWBook.Close False
            oXL.Quit    ' Closes the invisible instance of Microsoft Excel.
   
            ' Clear memory by removing the contents of the two object
            ' variables created.
            Set oXL = Nothing
            Set oWBook = Nothing
   
         End Sub
   						

1. In Microsoft Project, create a new module sheet. To do this, on the Tools menu, click Macros, and then click New. For the Macro Name, type Avoid_Replace_Existing and click OK.
2. In the module, on the Tools menu, click References.
3. In the References dialog box, select the Microsoft Excel 5.0 Object Library check box and click OK.
4. On the new module sheet, enter the following macro (notice that the first and last line of code already exists from step 1 and does not need to be repeated):

   Sub Avoid_Replace_Existing()
   
            ' Dimension variables.
   
            ' This assumes that a reference has been made to
            ' the Microsoft Excel 5.0 Object Library.
            Dim oXL As Excel.Application
            Dim oWBook As Object
            Dim Fname As String
   
            ' Assign workbook file & path that will be replaced to string
            ' variable. If running the procedure on the Macintosh, you will
            ' need to change the next line to a valid location on the hard
            ' drive similar to the following:
            ' Fname = "Macintosh HD:test.xls"
            '
            Fname = "C:\my documents\test.xls"
            ' Starts a new invisible instance of Microsoft Excel.
            Set oXL = CreateObject("Excel.Application")
            ' Adds a new workbook to the running instance of Microsoft Excel.
            Set oWBook = oXL.Workbooks.Add
   
            ' Checks to see if the file already exists.
            If Dir(Fname) <> "" Then
               ' Turn off error checking in case the file, "temp.xls"
               ' does not exist and causes an error when we try to delete it.
               On Error Resume Next
               ' Delete the temporary file (if it exists).
               ' If running the procedure on the Macintosh, you will need
               ' to change the next line to a valid location on the hard
               ' drive similar to the following:
               ' Kill "Macintosh HD:temp.xls"
               '
               Kill "C:\temp.xls"
               ' Disables "On Error Resume Next" and will allow
               ' Microsoft Excel to halt with an error for the remainder
               ' of the code.
               On Error GoTo 0
               ' Save the file in the normal format as 'temp.xls'
               ' If running the procedure on the Macintosh, you will need
               ' to change the next line to a valid location on the hard
               ' drive similar to the following:
               ' oXL.ActiveWorkbook.SaveAs FileName:="Macintosh HD:temp.xls"
               '
               oXL.ActiveWorkbook.SaveAs FileName:="C:\temp.xls"
               ' Close the workbook without saving changes.
               oXL.ActiveWorkbook.Close savechanges:=False
               Kill Fname 'Deletes the original file from the Hard Disk.
               ' Renames "temp.xls" as the file & path in the variable Fname.
               ' If running the procedure on the Macintosh, you will need
               ' to change the next line to a valid location on the hard
               ' drive similar to the following:
               ' Name "Macintosh HD:temp.xls" As Fname
               '
               Name "C:\temp.xls" As Fname
            Else
               ' Otherwise, if the file did not already exist in the path
               ' given, just save it there normally.
               oXL.ActiveWorkbook.SaveAs FileName:=Fname
            End If
            oXL.Quit ' Closes the invisible instance of Microsoft Excel.
            Set oXL = Nothing 'Removes the variable from memory.
            Set oWBook = Nothing 'Removes the variable from memory.
         End Sub