WinForms COM Exception opening Excel workbook

Question

I'm writing a very basic WinForms app using VB.Net, everything is going smoothly until I try and open an Excel file that I've written to the tempLocation via a Stream object (The workbook is in my project resources and the build action is set to "Embedded Resource")

Here's the code that causes the error:

Dim xlWBTemp As Excel.Workbook
Dim xlApp As Excel.Application
'// Below resolves to "C:\Users\MacroMan\LockTemplate.xlsm"
Dim tempLocation As String = Environ("USERPROFILE") & "\LockTemplate.xlsm"
...
xlWBTemp = xlApp.Workbooks.Open(tempLocation) '<~~ error here.

The error I get is:

System.Runtime.InteropServices.COMException was unhandled
ErrorCode=-2146827284
HResult=-2146827284
Message=Exception from HRESULT: 0x800A03EC

I've checked and the file definitely gets created, Excel is running and has already successfully opened a workbook at this point. I can't figure this out at all so any pointers truly welcome.

Update:

The file (tempLocation) does exist in the correct location, but when I open it outside of the application, Excel gives me the "found unreadable content" error. The file does still open successfully and has no problems once opened. I can now get around this error by using the CorruptLoad argument in the Workbooks.Open() method but I feel this is blindly ignoring the issue.

@varocarbas It's an array list of user selected files, that bit works fine. — SierraOscar, Oct 21 '15 at 16:23
@MatthewD good catch - I tried to open the file externally and got the "found unreadable content" message. The file then opens and nothing is missing, totally functional - so is there something wrong with the way I'm creating it? — SierraOscar, Oct 21 '15 at 16:23
@varocarbas `tempLocation` is a string with the full filepath/name — SierraOscar, Oct 21 '15 at 16:25
If `tempLocation` is a string referring to the file. What are you doing with this file? What is all the remaining code about? Why should we know what your variables are? Don't post lots of code. Post code which is relevant and which can be understood. You shouldn't come from the assumption that you are doing everything right (and that we should assume that you are doing everything right) because in that case no error would be triggered :) — varocarbas, Oct 21 '15 at 16:26
@varocarbas all of my variables are declared in the code provided? The issue is purely with the line trying to open the file that I've created, which is probably because the file has been created incorrectly, so I've posted the code relevant to creating the file and opening it. — SierraOscar, Oct 21 '15 at 16:27
(you shouldn't write variables with no explanation -> what is `Environ`?) You are using a stream, `fst` and not disposing it (= problems of the kind you are complaining about). But this whole post is too unclear (-> saying that after having read the code properly): you are complaining about an error triggered in a line which is created in an unkown way (`Environ`), while dealing with an extension method of a variable you are saying nothing about (`xlApp`), which has been affected in a way we don't even know (`Workbooks.Open(fil)`). — varocarbas, Oct 21 '15 at 16:37
IMHO all the code you originally posted is relevant to this question because your methodology may be what is causing problems. — D_Bester, Oct 22 '15 at 13:27
XLSM files are zip files at heart. It is possible that writing a zip file using a stream is causing problems. But I can't say for sure. — D_Bester, Oct 22 '15 at 13:30
The workaround would be to distribute the XLSM file using another method. — D_Bester, Oct 22 '15 at 13:32
@D_Bester This is the same conclusion I find myself arriving at - The objective is to use this file as a template (there's some pre-written VBA in a locked project) but I want the template to be in the app itself so that the end user can't amend it in anyway. Unfortunately I don't know any other way of doing this :( — SierraOscar, Oct 22 '15 at 13:35

score 4 · Answer 1 · edited Oct 27 '15 at 21:51

If it helps here is an example of a simple VB.NET app that I wrote a long time ago to test speed of VB vs C#. It is a different way of going about creating a workbook and writing to it.

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Dim xlApp As New Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value

        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkBook.Application.Visible = True
        xlWorkBook.Application.ScreenUpdating = False

        xlWorkSheet = xlWorkBook.Sheets("sheet1")
        xlWorkSheet.SaveAs("C:\\vbexcel.xlsx")

        Dim l As Long
        l = 1
        Do While l < 500
            'xlWorkSheet.Cells(l, 1) = l
            xlWorkSheet.Range("A" & l).Value = l
            'xlWorkSheet.Cells(l, 2) = l
            'xlWorkSheet.Cells(l, 3) = l
            l = l + 1
        Loop

        xlWorkBook.Application.ScreenUpdating = True
        xlWorkBook.RefreshAll()
        xlWorkBook.Save()

        'xlWorkBook.Close()
        'xlApp.Quit()

        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)

        MsgBox("Done")
    End Sub

    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub
End Class

Thanks for the effort, unfortunately the original file is in my project resources which is why I'm having to write it to another location using a `Stream` object - A good example of Excel interop though :) — SierraOscar, Oct 22 '15 at 13:28
With this I get, "Type 'Excel.Application' is not defined." on the first line ("Dim xlApp As New Excel.Application"). — B. Clay Shannon-B. Crow Raven, Oct 27 '15 at 21:22
@B. Clay Shannon That most likely has to do with the VS version and the reference version. There were some changes over the years. I actually had to change that line when I dug this up and got it working in VS2010 to post here. When I get back to my computer I'll post the way it was in the older VS. — MatthewD, Oct 28 '15 at 23:53

WinForms COM Exception opening Excel workbook

1 Answers1

Linked