Results 1 to 10 of 10

Thread: [RESOLVED] Excel Interop

  1. #1

    Thread Starter
    Powered By Medtronic dbasnett's Avatar
    Join Date
    Dec 2007
    Location
    Jefferson City, MO
    Posts
    9,754

    Resolved [RESOLVED] Excel Interop

    Over the years I have used this pattern to create a new Excel document,
    Code:
            Dim objApp As Excel.Application
            Dim objBook As Excel.Workbook
            Dim objSheet As Excel.Worksheet
    
            objApp = New Excel.Application
            objBook = objApp.Workbooks.Add()
            objSheet = CType(objBook.Sheets(1), Excel.Worksheet)
    On this line I get the error shown,
    Code:
            objBook = objApp.Workbooks.Add() ' <<<   {"The server threw an exception. (Exception from HRESULT: 0x80010105 (RPC_E_SERVERFAULT))"}
    I went back to some applications that I've had for years with the same pattern and they also fail, both in Visual Studio and in the published/installed version.

    I don't even know where to start and/or I haven't had enough coffee. HELP!
    My First Computer -- Documentation Link (RT?M) -- Using the Debugger -- Prime Number Sieve
    Counting Bits -- Subnet Calculator -- UI Guidelines -- >> SerialPort Answer <<

    "Those who use Application.DoEvents have no idea what it does and those who know what it does never use it." John Wein

  2. #2
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,393

    Re: Excel Interop


  3. #3
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: Excel Interop

    The first thing I'd look at would be whether you are referencing the right interop version for your installed version of Excel. I'd also be inclined to switch over to late binding and see whether or not the problem persisted, after all late binding is ideal for working with Excel in this fashion.

    To switch to late binding, set Option Strict OFF for the code page, then remove the reference to the Excel interop entirely. Naturally this will result in a series of undefined type errors. For all those types, change them to Object. By doing this, the program will use whichever version of the interop happens to be installed. It will fail if the methods used were changed between the version you developed the code under and the version you currently have installed, but changes to the interop are rare, and almost always involve changes to things in the obscure, rarely visited, corners of the interop. The one place I have seen the interop change (and I haven't looked extensively) had to do with programmatically creating pivot tables in spreadsheets. All the standard stuff, such as what you have shown, will work.
    My usual boring signature: Nothing

  4. #4
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: Excel Interop

    Here is an example I use:

    Code:
      Dim xl As Object
            Dim xlWorkBooks As Object
            Dim xlWorkBook As Object
            Dim xlWorksheet As Object
            xl = CreateObject("Excel.Application")
    
            xl.DisplayAlerts = False
            xlWorkBooks = xl.Workbooks
            xlWorkBook = xl.Workbooks.Open("C:\TestIt.xlsx")
            xlWorksheet = xl.Sheets(1)
            xlWorksheet.Activate()
    
            xlWorksheet.Cells(1, 2).Value = "XX"
    
            xl.Application.ActiveWorkbook.SaveAs("C:\TestItOut.xlsx")
    
    
            xlWorkBook.Close()
            xlWorksheet = Nothing
    
            xl.Quit()
            xl = Nothing
    
            ReleaseComObject(xlWorkBook)
            ReleaseComObject(xlWorkBooks)
            ReleaseComObject(xl)
    
            GC.Collect()
    Please remember next time...elections matter!

  5. #5
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: Excel Interop

    Also take note if you are using a 2010 office and the apps where made for previous 32 bit office.
    There is a confusion there, one of our admis installed 32 bit on 64 bit office or the opposite, can't recall but had to do it by console as MS won't let you.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  6. #6

    Thread Starter
    Powered By Medtronic dbasnett's Avatar
    Join Date
    Dec 2007
    Location
    Jefferson City, MO
    Posts
    9,754

    Re: Excel Interop

    I tried this with the same results,

    Code:
    Option Strict Off
    Public Class Form1
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim xl As Object
            Dim xlWorkBooks As Object
            Dim xlWorkBook As Object
            Dim xlWorksheet As Object
            xl = CreateObject("Excel.Application")
    
            xl.DisplayAlerts = False
            xl.Visible = True
            xlWorkBooks = xl.Workbooks
            '
            'The server threw an exception. (Exception from HRESULT: 0x80010105 (RPC_E_SERVERFAULT))
            xlWorkBook = xl.Workbooks.Add() '  same error here
            xlWorksheet = xl.Sheets(1)
            xlWorksheet.Activate()
    
            xlWorksheet.Cells(1, 2).Value = "XX"
    
        End Sub
    End Class
    In my original post and this one Excel starts and the workbook is created.
    I tried repairing Office also.
    My First Computer -- Documentation Link (RT?M) -- Using the Debugger -- Prime Number Sieve
    Counting Bits -- Subnet Calculator -- UI Guidelines -- >> SerialPort Answer <<

    "Those who use Application.DoEvents have no idea what it does and those who know what it does never use it." John Wein

  7. #7
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: Excel Interop

    I googled and saw this:

    https://superuser.com/questions/1124...-e-serverfault

    After a long research I succeeded to solve this problem due to this link. Essentially, the problem was caused by a third-party Excel COM plug-in, in my case it was "FoxitReader PDF Creator COM Add-in". After disabling it, the problem was gone!
    How to disable the plugin: Excel > File > Options > Add-ins > Manage, then choose "COM add-ins" > Go. And then untick the problematic plugin.

    This link they said solved it:

    https://www.ibm.com/support/pages/st...eported-values
    Please remember next time...elections matter!

  8. #8

    Thread Starter
    Powered By Medtronic dbasnett's Avatar
    Join Date
    Dec 2007
    Location
    Jefferson City, MO
    Posts
    9,754

    Re: Excel Interop

    Quote Originally Posted by TysonLPrice View Post
    I googled and saw this:

    https://superuser.com/questions/1124...-e-serverfault



    How to disable the plugin: Excel > File > Options > Add-ins > Manage, then choose "COM add-ins" > Go. And then untick the problematic plugin.

    This link they said solved it:

    https://www.ibm.com/support/pages/st...eported-values
    WINNER, WINNER, ...

    I went to the machine that worked and looked at the add-ins on it. I made my add-ins the same and voila! Thank you, thank you, thank you!

    FWIW - in my case it was Microsoft Teams add ins.
    My First Computer -- Documentation Link (RT?M) -- Using the Debugger -- Prime Number Sieve
    Counting Bits -- Subnet Calculator -- UI Guidelines -- >> SerialPort Answer <<

    "Those who use Application.DoEvents have no idea what it does and those who know what it does never use it." John Wein

  9. #9
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: Excel Interop

    Quote Originally Posted by dbasnett View Post
    FWIW - in my case it was Microsoft Teams add ins.
    That seems ironic
    My usual boring signature: Nothing

  10. #10

    Thread Starter
    Powered By Medtronic dbasnett's Avatar
    Join Date
    Dec 2007
    Location
    Jefferson City, MO
    Posts
    9,754

    Re: Excel Interop

    Quote Originally Posted by Shaggy Hiker View Post
    That seems ironic
    Doesn't it.
    My First Computer -- Documentation Link (RT?M) -- Using the Debugger -- Prime Number Sieve
    Counting Bits -- Subnet Calculator -- UI Guidelines -- >> SerialPort Answer <<

    "Those who use Application.DoEvents have no idea what it does and those who know what it does never use it." John Wein

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width