Run excel macro from Windows Service

[vijay2482]

Hi all,

I have an excel work book that contains a macro that search the outlook mail for some particular subject and creates a txt file.
In my windows service vb.net program, I would like to open the excel call the macro and close the excel in the OnStart() event.
code:

Public Class Service1

    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.
       
        Dim oExcel As Excel.ApplicationClass
        Dim oBook As Excel.WorkbookClass
        Dim oBooks As Excel.Workbooks

        'Start Excel and open the workbook.
        oExcel = CreateObject("Excel.Application")
        oExcel.Visible = True
        oBooks = oExcel.Workbooks
        oBook = oBooks.Open("C:\CAD Audit\make_job_v3.xlsm")
        'oBook.Sheets("Feuil1").select()
        'Run the macros.
        oExcel.Run("test_lecture_mail")

        'Clean-up: Close the workbook and quit Excel.
        oBook.Close(False)
        System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook)
        oBook = Nothing
        System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks)
        oBooks = Nothing
        oExcel.Quit()
        System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel)
        oExcel = Nothing

    End Sub

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

I have the above code, but I dont see the excel macro running and no txt file created.

Any help/suggestions will be very much helpful.
Thanks in advance.

[Grimfort]

If your service takes longer than 30 seconds to start, it will automatically fail.

If you are trying to access Office from a service, M$ do not support it, however it is possible. One of the most likely failures you are having is this, and you can fix it simply by creating the folder specified in the first solution section.

[vijay2482]

Thanks for the reply.

I have tried this option and now, I get a different error:

error 1053 the service did not respond to the start or control request in a timely fashion

I find that the excl is locked by the 'SYSTEM' when the process is running.
I think, before running the macro, the error appears.

[Grimfort]

Like I said, if your service does not start within 30 seconds, it will fail. There is no point in having a service that just does one thing, normally you move the actions off the startup method to allow it to start, then process on a timer, or an event when you want to do the real work.

If you are automating Office, you have to turn off alerting messages as well otherwise things may be stuck asking for user interaction.

[vijay2482]

In the below code, I have set the excel workbook to visible, but i dont see the workbook and in the same way, the excel is locked by the system and the macro doesnt seem to run.

oExcel.Visible = True

Only when i force and stop the service, it stops and even then the excel workbook is locked.
I have to restart my system to unlock the excel workbook.
This is because, the service was stopped forcefully before completing the task(run macro).
any suggestion....

[Grimfort]

You need to understand what a service is to be able to code against it. A service runs as a hidden user, like a command line sitting in memory that you can not access. They do not have a profile, they do not have access to networks, printers and other things without requesting access. There are ways to interact with them, but generally they do things for you automatically.

Some simple things to think about:
Have you tried the code above running as a normal windows form?
Does your macro send an email AS a specific user, if so your windows service needs to run AS that user.

[vijay2482]

yes, I have the same code running well with windows form application.
and emails are sent.
in the properties of the service, I have loged on as the Local system account.

Thanks

[Grimfort]

The local system account will not have access to the users email details.

[vijay2482]

the excel macro, searches the inbox of the local system for particular subjects and writes the contents of the mail into a txt file.

This is what the macro does.
how do you say that the local system's mail box cannot be read?

[Grimfort]

Your inbox is for a user, not for a machine. If you were to log in as a different user, you get a different mailbox. You are running your service as a non-user, so you would get no mailbox. You can change a service to run as a specific user, which may or may not be able to access the info (like I said earlier, not all information is available to a windows server).

Are you sure you even need a service for this?

[vijay2482]

I changed the log on property from local system to this account(where I have set my account details.. user id and password).
When I run the service, I have also written the writeLog statements to check at which line the service is currently running.
I found out that, after onStart(), i didnot see any of the msges in the event log.
And I got this error:

The service1 service on local computer started and then stopped. Some services stop automatically if they are not in use by other services or programs.

Could'nt find a solution for the windows service until now.
Its my first windows service program.

[Grimfort]

You have no TryCatch in that whole sub, anything that goes wrong will cause the service not to start.

[vijay2482]

I have written the try catch block and then run the service, but even then I have the same problem.
There are 2 places in 2 routines where the service fails.
1) oExcel.run("macroName")
2)oMsg.send()

I'm not sure how to resolve this issue.
Anybody, please suggest a solution for this case.

Thaks in advance.

[Grimfort]

Well, usually when you get an error, you get ... the information about the error. What failed, what was the exception? We cannot guess . The first one is trying to run the macro, have you looked up reasons why a macro might fail? A quick google came up with issues about security running macros under a service, have you checked out how to remove such an issue like setting the security to low?

[vijay2482]

Yes, in Macro Security -->macro settings - I have set "Enable all macros".

[Nikos Rubin]

Several posts there are wrong: The Excel can be launched fine from any user Service under Local System Account!!! But... you need know sevral important things to 2 this:
1) Not use CreateObject/CreateObject to create Excel object - Launch the Excel using ShellExecuteEx, and after that attach to Excel Appliaction object using GetActiveObject. Now you can use Excel CON interfaces safely.
2) Before run Excel from service under Local System Account you must manually(!!!) create 2 folders:
C:\Windows\system32\config\systemprofile\desktop
C:\Windows\SysWow64\config\systemprofile\desktop
3) In the first under new user Excel shows the User credentials window (name and initial letters). If the Excel launched from service it is hidden, you cannot view this window and Excel will freeze in this stage. to avoid this problem disable user credentials window in Excel options (manually or edit registry keys from you program). You also can enter user credentials for Local System Account manually: run Excel from service with active "desktop interaction" option, switch to Excel after Windows shows popup alert, enter user credentials and close Excel, restore working options (uncheck desktop interaction) and restart you service - Excel will work fine.
Best regards. There is small article about this problem - but in the russian..

[Grimfort]

I am not quite sure what part about my posts are wrong, you are welcome to point them out so it is obvious to readers. I did not post that it could not be done, please check the context of what the user is actually doing, then wanted to access data visible to a user profile. The link in my first post explains how to get it working using the same advice you posted! I have not tested starting the app directly as you suggest, as everything has worked without having to do this, so at least dragging this 2 year old post up had some merit .