Excel automation in windows service

0

I just finished with excel automation on my windows console application and it works. Now i want to move it to windows service, but i faced an error, which dont kno how to solve. When i run my application i get this error in my log file:

System.Runtime.InteropServices.COMException (0x8001010A): The message filter indicated that the application is busy. (Exception from HRESULT: 0x8001010A (RPC_E_SERVERCALL_RETRYLATER))
   at System.RuntimeType.InvokeDispMethod(String name, BindingFlags invokeAttr, Object target, Object[] args, Boolean[] byrefModifiers, Int32 culture, String[] namedParameters)
   at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
   at Microsoft.VisualBasic.CompilerServices.VBBinder.InvokeMember(String name, BindingFlags invokeAttr, Type objType, IReflect objIReflect, Object target, Object[] args, String[] namedParameters)
   at Microsoft.VisualBasic.CompilerServices.LateBinding.LateGet(Object o, Type objType, String name, Object[] args, String[] paramnames, Boolean[] CopyBack)
   at Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateGet(Object Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack)

I heard that it could be a problem to run it from windows service but i do not belive cannot be done. Please of your support.

From console application everything works just fine. When it comes to windows service immediately after excel code starts i get the above error.

Windows service:

Imports Transform

    Public Class svcTransform

        Private Const EvtLogSource As String = "SvcTransform"
        Private Const EvtLogName As String = "SvcTransform"

        Private syncRoot As New Object
        Dim timer As System.Timers.Timer = New System.Timers.Timer()

        Sub New()

            ' This call is required by the designer.
            InitializeComponent()

            ' Add any initialization after the InitializeComponent() call.
            If Not System.Diagnostics.EventLog.SourceExists(EvtLogSource) Then
                System.Diagnostics.EventLog.CreateEventSource(EvtLogSource, EvtLogName)
            End If

            EventLog1.Source = EvtLogSource

        End Sub
        Protected Overrides Sub OnStart(ByVal args() As String)
            ' Add code here to start your service. This method should set things
            ' in motion so your service can do its work.
            EventLog1.WriteEntry("In OnStart")



            ' Set up a timer to trigger every minute.

            timer.Interval = 60000  '1 minutes



            'unfortunetly OnTimer event handler will be executed even already one is running if Interval is riched by default.
            'This is because it will go multiple threading and not in main thread unless a SynchronizingObject is supplied. (Which it wasn't.) below :
            'timer.SynchronizingObject = Me
            'this is solving problem. OTher way is to make lock within OnTimer event handler as its done right now.

            AddHandler timer.Elapsed, AddressOf Me.OnTimer
            timer.Start()
        End Sub

        Protected Overrides Sub OnStop()
            ' Add code here to perform any tear-down necessary to stop your service.
            EventLog1.WriteEntry("In OnStop")
        End Sub

        Protected Overrides Sub OnContinue()
            EventLog1.WriteEntry("In OnContinue.")
        End Sub

        Private Sub OnTimer(ByVal sender As Object, ByVal e As Timers.ElapsedEventArgs)
            SyncLock syncRoot
                Dim run As New CpRep("D:\Report\test.xlsx")
            End SyncLock
        End Sub



    End Class

CpRep class (just most important code responsible for excel):

 ....
       Protected _app As Application = Nothing
        Protected _workbooks As Workbooks = Nothing
        Protected _workbook As Workbook = Nothing
        Protected _worksheet As Worksheet = Nothing
        Protected _worksheets As Sheets = Nothing
        Protected _range As Range = Nothing

        Protected Sub Run()
            CreateExcelInstance()
        End Sub

       Public Sub New(configpath As String, reportPath As String)
            _serializerPath = configpath
            _reportPath = reportPath
            Run()  'do stuff here
        End Sub

      Private Sub CreateExcelInstance()
            _app = New Application
            _app.DisplayAlerts = False
            _workbooks = _app.Workbooks
            _workbook = _workbooks.Open(_reportPath)    
            _app.Visible = False
            _worksheets = _workbook.Sheets
        End Sub

        Protected Sub SaveCloseDispose()
            Try
                _app.ActiveWorkbook.Save()
                _workbook.Close()
                _app.UserControl = True
                _app.Quit()
            Catch ex As Exception
                Throw ex
            Finally
                DisposeObjects()
            End Try
        End Sub

        Protected Sub DisposeObjects()
            ReleaseComObject(_range)
            ReleaseComObject(_worksheets)
            ReleaseComObject(_worksheet)
            ReleaseComObject(_workbook)
            ReleaseComObject(_workbooks)
            ReleaseComObject(_app)
        End Sub

        Protected Sub ReleaseComObject(ByVal obj As Object)
            Try
                Runtime.InteropServices.Marshal.ReleaseComObject(obj)
                obj = Nothing
            Catch ex As Exception
                obj = Nothing
            End Try
        End Sub
c#
vb.net
asked on Stack Overflow Apr 12, 2017 by Dino • edited Apr 12, 2017 by Dino

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0