VB.NET Saving a workbook gives System.Runtime.InteropServices.COMException Error

[Thebatfink]

Hi,

Know this is not exactly VBA but I guess the experts know their VB even more so than their VBA!

I used to do this within Excel, but I'm trying to move away and into VB 2010. To me the code seems very simple and was pretty easy to migrate from VBA but it keeps hitting an error.

Imports Microsoft.Office.Interop

Public Class Form1

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Dim ExcelMasterPlan As Excel.Application = New Excel.Application
        Dim FileName As String
        Dim SaveFileName As String

        FileName = "\\server\folder\MyMaster.xlsm"
        SaveFileName = "\\server\folder\MyNewMaster.xlsm"

        Dim wb As Excel.Workbook = ExcelMasterPlan.Workbooks.Open(FileName)

        ExcelMasterPlan.Workbooks.Open(FileName)

        Try

            wb.SaveAs(SaveFileName, Excel.XlFileFormat.xlOpenXMLWorkbookMacroEnabled)

        Catch

            MsgBox("An error occured saving")
            ExcelMasterPlan.Workbooks.Close()
            ExcelMasterPlan.Quit()

        End Try
    End Sub

End Class

It doesn't seem to want to save, I get the error 'The object invoked has disconnected from its clients. (Exception from HRESULT: 0x80010108 (RPC_E_DISCONNECTED))'

I've tried various other things like holding the SaveFileName minus the extension, not setting the fileformat in the save line.. Nothing seems to work which makes me think its something I've not set correctly in Studio or something ill defined (I do have Microsoft Excel 12.0 Object Library added as a reference to my project).

Any one able to offer any help?

Thanks!

 

[Derek Brown]

Is it failing on the save? I am a bit puzzled by:

       Dim wb As Excel.Workbook = ExcelMasterPlan.Workbooks.Open(FileName)

        ExcelMasterPlan.Workbooks.Open(FileName)

It looks as though you are opening the same workbook twice?
In case it helps, here is an example (modified code) of one of my routines to open an Excel workbook and run a macro:

Imports System.IO
Module Module1
    Public Sub Main()
        RunMyScheduler01()
    End Sub
    Sub RunMyScheduler01()
        Const strControlFileName As String = "\\Server\folder\My_Scheduler_01.xlsm"
        Dim oExcel As Microsoft.Office.Interop.Excel.Application
        Dim oBook As Microsoft.Office.Interop.Excel.Workbook
        Dim oBooks As Microsoft.Office.Interop.Excel.Workbooks
        Dim strFilenameCheck As String
        Dim strMacroName As String
        Try
            strMacroName = "MyMacro01"
            oExcel = CType(CreateObject("Excel.Application"), Microsoft.Office.Interop.Excel.Application)
            oExcel.Visible = False
            oBooks = CType(oExcel.Workbooks(), Microsoft.Office.Interop.Excel.Workbooks)
            strFilenameCheck = Dir(strControlFileName)
            If strFilenameCheck <> "" Then
                oBook = CType(oBooks.Open(strControlFileName), Microsoft.Office.Interop.Excel.Workbook)
                oExcel.DisplayAlerts = False
                oExcel.Run(strMacroName)
                oExcel.DisplayAlerts = True
            Else
                Dim sw As New StreamWriter(Application.StartupPath & "\MyMacro01_error.log", True)
                sw.WriteLine(Now() & " - '" & strControlFileName & "' could not be accessed.")
                sw.Close()
                End
            End If
            '
        Catch ex As Exception
            Dim sw As New StreamWriter(Application.StartupPath & "\MyMacro01_Error.log", True)
            sw.WriteLine(Now() & " - " & ex.Message)
            sw.Close()
        Finally
            oBook.Close(False)
            System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook)
            oBook = Nothing
            System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks)
            oBooks = Nothing
            oExcel.Quit()
            System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel)
            oExcel = Nothing
            GC.Collect()
        End Try
    End Sub
End Module

It runs in "silent" mode overnight on a server.