none
Open Excel file in VB.NET for format created from embedded RRS feed

  • Question

  • Hi.

    I have used method(WriteAllBytes) to create excel file from embedded excel.  Then i am trying to open it using Microsoft.Office.Interop.Excel.Application in order to format some cells. I am getting always error: "The server threw an exception. (Exception from HRESULT: 0x80010105 (RPC_E_SERVERFAULT))"

    if i am taking regular excel file(not embedded) it works.

    my code:

     Dim targetfile As String = "c:\newfile.xls"
     Dim s As Stream = Assembly.GetExecutingAssembly().GetManifestResourceStream("MyApplication.embedded.xls)
     Dim bytes(s.Length) As Byte
     s.Read(bytes, 0, bytes.Length)
     Using fs As FileStream = File.Create(target)
    fs.Write(bytes, 0, bytes.Length)
     End Using

    Dim excel As New Microsoft.Office.Interop.Excel.Application
    Dim wb As Microsoft.Office.Interop.Excel.Workbook
    wb = excel.Workbooks.Open(targetfile) 'error is here
    excel.Visible = True
    wb.Activate()

    I like working with embedded - it fast by using oledb update, but i need also to format file after filling data.

    Please, help


    Michael

    Tuesday, June 13, 2017 6:42 AM

Answers

  • Hi.

    I have used method(WriteAllBytes) to create excel file from embedded excel.  Then i am trying to open it using Microsoft.Office.Interop.Excel.Application in order to format some cells. I am getting always error: "The server threw an exception. (Exception from HRESULT: 0x80010105 (RPC_E_SERVERFAULT))"

    if i am taking regular excel file(not embedded) it works.

    my code:

     Dim targetfile As String = "c:\newfile.xls"
     Dim s As Stream = Assembly.GetExecutingAssembly().GetManifestResourceStream("MyApplication.embedded.xls)
     Dim bytes(s.Length) As Byte
     s.Read(bytes, 0, bytes.Length)
     Using fs As FileStream = File.Create(target)
    fs.Write(bytes, 0, bytes.Length)
     End Using

    Dim excel As New Microsoft.Office.Interop.Excel.Application
    Dim wb As Microsoft.Office.Interop.Excel.Workbook
    wb = excel.Workbooks.Open(targetfile) 'error is here
    excel.Visible = True
    wb.Activate()

    I like working with embedded - it fast by using oledb update, but i need also to format file after filling data.

    Please, help


    Michael

    Your bytes() array is one byte too large so there will be an extra zero at the end of the stream.  This may be what is corrupting your file.

    You should have:

     Dim bytes(s.Length - 1) As Byte


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    • Marked as answer by grmotild Wednesday, June 28, 2017 5:55 AM
    Thursday, June 15, 2017 12:44 PM
    Moderator

All replies

  • Hi Michael,

    Welcome to the MSDN forum.

    Refer to your description, your issue is about the VB development. Since our forum is to discuss the Visual Studio WPF/SL Designer, Visual Studio Guidance Automation Toolkit, Developer Documentation and Help System, and Visual Studio Editor, I will help you move this thread to the appropriate forum: Visual Studio Languages  >  Visual Basic to seek for a more professional support, thank you for your understanding.

    Best regards,

    Sara


    MSDN Community Support<br/> Please remember to click &quot;Mark as Answer&quot; the responses that resolved your issue, and to click &quot;Unmark as Answer&quot; if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact <a href="mailto:MSDNFSF@microsoft.com">MSDNFSF@microsoft.com</a>.

    Wednesday, June 14, 2017 1:58 AM
  • Hi grmotild,

    I try your code at my side and Comment some of the code , it works fine, it can open excel successfully.

    Dim targetfile As String = "D:\test\6.xlsx"
            'Dim s As Stream = Assembly.GetExecutingAssembly().GetManifestResourceStream("MyApplication.embedded.xls")
            'Dim bytes(s.Length) As Byte
            's.Read(bytes, 0, bytes.Length)
            'Using fs As FileStream = File.Create(target)
            '    fs.Write(bytes, 0, bytes.Length)
            'End Using
    
            Dim excel As New Microsoft.Office.Interop.Excel.Application
            Dim wb As Microsoft.Office.Interop.Excel.Workbook
            wb = excel.Workbooks.Open(targetfile) 'error is here
            excel.Visible = True
            wb.Activate()

    Please check your references. For example, if you have Excel 2000 set as default on the computer you are running this project on, but the reference is set for Excel 2007 (would show as Excel 12.0 object library) you will receive the above HRESULT error.

    Quickest way to check IMO, is to look in your Solution Explorer and click the drop down arrow beside References and select the Microsoft.Office.Interop.Excel and see what version is listed in the Description.

    If I owe anything, please tell me.

    Best Regards,

    Cherry



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, June 14, 2017 3:32 AM
    Moderator
  • Hello,

    You can simplify the extraction.

    IO.File.WriteAllBytes("c:\newfile.xls", My.Resources.embedded)

    If you were using the current Excel format there is a free library called SpreadSheetLight that is easy to use and will dispose of all objects used unlike Excel automation where it's easy to miss disposing of one or more objects.

    I've written a MSDN code sample for using SpreadSheetLight in VB.NET since all the examples at the site are in C#. The code sample in the first paragraph explains how to installed SpreadSheetLight from NuGet.

    To open and close an excel file using a specific sheet.

    Public Sub SimpleOpen(ByVal FileName As String, ByVal SheetName As String)
        Using sl As New SLDocument(FileName, SheetName)
    
        End Using
    End Sub

    To open , write a integer value to a specific cell and save.

    Public Sub SimpleOpenWriteOneValueAndSave(
    ByVal FileName As String,
    ByVal SheetName As String,
    ByVal CellAddress As String,
    ByVal CellValue As Integer)
    
        Using sl As New SLDocument(FileName, SheetName)
            sl.SetCellValue(CellAddress, CellValue)
            sl.Save()
        End Using
    End Sub

    For you, extract file from resource, set some values, set style for two cells and values then set two other cells without formating and save.

    Form code

    Public Class ExtractFromResourceForm
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim FileName As String = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "KarenPayne.xlsx")
            Dim ops As New Operations
            If ops.OpenFromResource(FileName, My.Resources.embedded) Then
                MessageBox.Show("Done")
            End If
        End Sub
    End Class

    Class code

    Imports DOS = DocumentFormat.OpenXml.Spreadsheet
    Imports SpreadsheetLight
    Imports SpreadsheetLight.Drawing
    Public Class Operations
        Public Function OpenFromResource(ByVal FileName As String, ByVal Contents As Byte()) As Boolean
    
            IO.File.WriteAllBytes(FileName, Contents)
    
            Using doc As New SLDocument(FileName, "Sheet1")
    
                Dim Style1 = doc.CreateStyle
                Style1.Font.FontColor = Color.Pink
                Style1.Font.Strike = False
                Style1.Font.Underline = DOS.UnderlineValues.None
                Style1.Font.Bold = True
                Style1.Font.Italic = False
                Style1.Fill.SetPattern(DOS.PatternValues.Solid, Color.Black, Color.Pink)
                Style1.Alignment.Horizontal = DOS.HorizontalAlignmentValues.Right
    
                doc.SetCellStyle("B2", Style1)
                doc.SetCellStyle("C2", Style1)
    
                doc.SetCellValue("B2", "Karen")
                doc.SetCellValue("C2", "Payne")
    
                doc.SetCellValue("B3", 100)
                doc.SetCellValue("C3", 200)
    
                doc.Save()
            End Using
    
            Return True
    
        End Function
    End Class

    Finished work

    Downside is this library does not work with .xls, only .xlsx.

    If you are going to use Excel automation rather than the above, see my MSDN code sample for vb.net

    Basics of using Excel automation in VB.NET with emphasis on creating and destroy 


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Wednesday, June 14, 2017 8:51 AM
    Moderator
  • Hi Michael

    Is C:\newfile.xls actually getting created properly, i.e. can you open this file in Excel manually?  Also is you code from a windows forms application or an ASP.NET application?

    Alan


    Alan Moseley

    Thursday, June 15, 2017 11:34 AM
  • Hi.

    I have used method(WriteAllBytes) to create excel file from embedded excel.  Then i am trying to open it using Microsoft.Office.Interop.Excel.Application in order to format some cells. I am getting always error: "The server threw an exception. (Exception from HRESULT: 0x80010105 (RPC_E_SERVERFAULT))"

    if i am taking regular excel file(not embedded) it works.

    my code:

     Dim targetfile As String = "c:\newfile.xls"
     Dim s As Stream = Assembly.GetExecutingAssembly().GetManifestResourceStream("MyApplication.embedded.xls)
     Dim bytes(s.Length) As Byte
     s.Read(bytes, 0, bytes.Length)
     Using fs As FileStream = File.Create(target)
    fs.Write(bytes, 0, bytes.Length)
     End Using

    Dim excel As New Microsoft.Office.Interop.Excel.Application
    Dim wb As Microsoft.Office.Interop.Excel.Workbook
    wb = excel.Workbooks.Open(targetfile) 'error is here
    excel.Visible = True
    wb.Activate()

    I like working with embedded - it fast by using oledb update, but i need also to format file after filling data.

    Please, help


    Michael

    Your bytes() array is one byte too large so there will be an extra zero at the end of the stream.  This may be what is corrupting your file.

    You should have:

     Dim bytes(s.Length - 1) As Byte


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    • Marked as answer by grmotild Wednesday, June 28, 2017 5:55 AM
    Thursday, June 15, 2017 12:44 PM
    Moderator
  • This is vb.net win forms

    Michael

    Wednesday, June 28, 2017 5:57 AM