How to create and save Excel file using windows service created in VB.Net

Question

0.00/5 (No votes)

See more:

Hi,
Am new to VB.Net. I have created a windows service. Where my simple goal is to create an Excel file and save it to my local drive. I have used the following code.

VB

 Dim oExcel As Excel.Application
            Dim oBook As Excel.Workbook
            Dim oSheet As Excel.Worksheet
            oExcel = CreateObject("Excel.Application")
            oBook = oExcel.Workbooks.Add

'Start a new workbook in Excel.

            'Add data to cells of the first worksheet in the new workbook.
            oSheet = oBook.Worksheets(1)
            oSheet.Range("A1").Value = "Last Name"
            oSheet.Range("B1").Value = "First Name"
            oSheet.Range("A1:B1").Font.Bold = True
            oSheet.Range("A2").Value = "Doe"
            oSheet.Range("B2").Value = "John"

            oBook.SaveAs(strFileName)
            oSheet = Nothing
            oBook = Nothing
            oExcel.Quit()
            oExcel = Nothing
            GC.Collect()

Now this CreateObject throwing exception "Cannot create ActiveX component." I have added reference of Microsoft Excel & Office in my project.

Any idea what am doing wrong or what should be done? It is possible to create and save excel file from windows service right?

P.S - Am on windows 7 64 bit machine and using vs2010, office 2010.

Posted 26-Feb-13 0:24am

A Roychowdhury

Add a Solution

Comments

CHill60 26-Feb-13 6:44am

You should be using oExcel = new Excel.Application() not CreateObject

A Roychowdhury 26-Feb-13 7:11am

Thanks for your reply CHill60..however this time I got the following exception.
"Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80080005."

CHill60 26-Feb-13 7:21am

See solution below - you shouldn't be using a COM interface to excel so check how you added the references to your project

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2013-02-26T00:53:00

Solution 1

It may be possible as long as Excel is on the box that the service is running on, although I've heard on StackExchange that interop doesn't work as a server application

Have a look at this article[^] from Microsoft
or a very good alternative approach [^] from our very own CodeProject which may be the very solution you need for a service

Posted 26-Feb-13 0:53am

CHill60

Comments

A Roychowdhury 26-Feb-13 7:15am

The msdn article is referring to a console application. But am looking for a windows service to do the same.

CHill60 26-Feb-13 7:19am

They tend to use Console applications for ease of demonstration - so they can use consolewriteline to show the working. The main principles are the same.
[Edit] - do you specifically need a "service" if you are saving to your local drive - would a winform application be appropriate?

A Roychowdhury 26-Feb-13 7:25am

Actually I need a application which will automatically run,create and save some excel reports preodically. So, I figured out that windows service will be teh best solution to do so as it'll never need user intervention to start.

CHill60 26-Feb-13 7:29am

Cool - just thought I'd check :-) I like to "start simple" and layer up the complexity so maybe having a console app running under a windows scheduler for starters might get you going

A Roychowdhury 26-Feb-13 7:35am

So we can add scheduler to console application? that'll do. can you please share how to use that.

CHill60 26-Feb-13 8:23am

It's more a case of having a scheduler call your console (or other) application ... there's an intro here for Windows 7 http://windows.microsoft.com/en-GB/windows7/schedule-a-task and a more in depth example http://tinyhacker.com/hacks/complete-guide-to-windows-7s-task-scheduler/. We haven't got Win7 here yet so I won't be able to help much beyond this - sorry

A Roychowdhury 26-Feb-13 8:24am

I did add the console application under a windows scheduler task and its working fine. Just to give others the idea of how to add console app under the scheduler..follow the below steps:

1. run Start->All Programs-> Accessories->System Tools->Scheduled Tasks.

2. add Scheduled Task->Next->Select your console application->Next->Select Daily Perform this task->check the start time and start date, and let Perform this task Every 2 days->enter your name and password->check Open advanced properties…Finish option and then click finish button-> click Settings page->change the time of Stop the task if it runs for: 1 hour(s) 59 minute(s)-> at last click OK buttion to finish the configuration.

CHill60 26-Feb-13 9:05am

My 5 (if I could vote it up) for adding the extra detail for others.