-
Mar 15th, 2023, 08:12 AM
#1
[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!
-
Mar 15th, 2023, 09:36 AM
#2
-
Mar 15th, 2023, 10:19 AM
#3
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
-
Mar 15th, 2023, 11:14 AM
#4
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!
-
Mar 16th, 2023, 06:55 AM
#5
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.
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Mar 16th, 2023, 08:52 AM
#6
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.
-
Mar 16th, 2023, 09:06 AM
#7
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!
-
Mar 16th, 2023, 09:23 AM
#8
Re: Excel Interop
Originally Posted by TysonLPrice
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.
-
Mar 16th, 2023, 09:55 AM
#9
Re: Excel Interop
Originally Posted by dbasnett
FWIW - in my case it was Microsoft Teams add ins.
That seems ironic
My usual boring signature: Nothing
-
Mar 16th, 2023, 10:47 AM
#10
Re: Excel Interop
Originally Posted by Shaggy Hiker
That seems ironic
Doesn't it.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|