Go Back  Xtreme Visual Basic Talk > Visual Basic .NET (2002/2003/2005/2008, including Express editions) > .NET Office Automation > VB.NET Excel Automation - Call Rejected Problem


Reply
 
Thread Tools Display Modes
  #1  
Old 12-17-2010, 10:59 AM
nguyenmiket nguyenmiket is offline
Newcomer
 
Join Date: Oct 2010
Location: San Diego, CA
Posts: 1
Lightbulb VB.NET Excel Automation - Call Rejected Problem


I have been pulling my hair out trying to fix this.

Environment: I have an excel spreadsheet that pulls data in every five minutes and does calculations on specific cells. I have used Mike Rosenblum's example from http://www.xtremevbtalk.com/showthread.php?t=160433 and it works great.....except when excel is in the middle of calculating the cell values. I understand that excel is a single thread application from previous posts, but how wait for the calculation to finish so my VB.NET application can go and grab the cell values? If excel is not in the middle of a calculation, it works beautifully. If excel is in the middle of a calculation, i get the following error:

System.Runtime.InteropServices.COMException (0x80010001): Call was rejected by callee. (Exception from HRESULT: 0x80010001 (RPC_E_CALL_REJECTED))
at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)
at Microsoft.Office.Interop.Excel.Range.get_Value(Object RangeValueDataType)
Code:
My Procedure in my application:
Imports Excel = Microsoft.Office.Interop.Excel
Imports System.Runtime.InteropServices
Imports System.Data
Imports System.IO

Public Sub GetPositionData()
        Dim objExcel As New Excel.Application
        Dim objWrkBk As Excel.Workbook
        Dim objSht As Excel.Worksheet

        Try
           
            'only when workbook is excel 2003
            objWrkBk = GetObject("C:\Documents and Settings\JDCAdmin\Desktop\futures_ats.xls")

            objSht = objWrkBk.Worksheets("Main") ' worksheet name

            'Write the values to csv file
            Dim strPositionFileToUpload As String = "Position_" & String.Format("{0:MMddyyyy}", Date.Now) & _
                "_" & String.Format("{0:HHmmss}", Date.Now) & "-CHECK_SUM.csv"

            FileWriter = New StreamWriter("C:\Blotter_Files\To_Be_Processed\" & strPositionFileToUpload)

            'get cell value
            FileWriter.WriteLine(objSht.Range("I17").Value.ToString & "," & objSht.Range("J17").Value.ToString & "," & _
                objSht.Range("K17").Value.ToString & "," & objSht.Range("L17").Value.ToString & "," & _
                objSht.Range("M17").Value.ToString & "," & objSht.Range("N17").Value.ToString & "," & objSht.Range("O17").Value.ToString & _
                "," & objSht.Range("P17").Value.ToString)
            

            ' close filewriter
            FileWriter.Dispose()

            'UNLOAD THE FORM for next run
            Me.Dispose()
        Catch ex As Exception
           
            SendEmail2("ERROR:  Blotter New has generated an error while trying to create position file." & _
                "<P>", "Details: " & ex.ToString, "nguyen.mike.t@gmail.com")

        Finally
            If Not objExcel Is Nothing Then
                GC.Collect()
                GC.WaitForPendingFinalizers()
                GC.Collect()
                GC.WaitForPendingFinalizers()

                Marshal.FinalReleaseComObject(objSht)

                'If Not objWrkBk Is Nothing Then
                'objWrkBk.Close(SaveChanges:=False)
                Marshal.FinalReleaseComObject(objWrkBk)
                'End If

                objExcel.Quit()
                Marshal.FinalReleaseComObject(objExcel)

            End If

        End Try
    End Sub

Last edited by Colin Legg; 12-17-2010 at 12:54 PM. Reason: added code tags
Reply With Quote
  #2  
Old 12-19-2010, 04:05 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Hi nguyenmiket,

This is very difficult to deal with. Can I ask why you are latching onto an existing Excel instance instead of creating a new one? Creating a new one would give you full control and would not conflict with the user.

Give us a sense of your reasoning here, and we might be able to come up with a work-around...

-- Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
Reply

Tags
call rejected, excel automation, vb.net


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
 
 
-->