none
How do I transfer data to an Excel file RRS feed

  • Question

  • Happy New Year to all our readers!

    Hi everyone,

    I have a need to write data to an Excel file.

    I found a KB file, 306023, which gives a lot of information and an example that works.

    I now want to add some of this code to a DLL that I am building.  However, having added all the recommended References, I still get errors.  Typically,

    public Excel.Application m_objExcel = null;

    gives an error:

     1 The type name 'Application' does not exist in the type 'AnnsonDll.Excel' .

    I don't understand why I get the error here, but not in the example code.

    I have searched the web, but I can find no useful information on any of this. 

    The KB file refers to using Excel = Microsoft.Office.Interop.Excel;  but this isn't recognised by my version of C#. 

    The nearest I can get is using Excel = Microsoft.Office.Core;

    I am using Microsoft Visual Studio 2005
    Version 8.0.50727.42  (RTM.050727-4200)
    Microsoft .NET Framework
    Version 2.0.50727

    Installed Edition: Standard

    Microsoft Visual C# 2005   77633-233-0110651-41645
    Microsoft Visual C# 2005

    Does nayone out there have any idea what I am missing?  Any help will be really welcome!

     

    Thanks for your time

    John

    Monday, January 8, 2007 6:53 AM

Answers

  • Hi,

    You must be missing the Excel reference, that is "Microsoft Excel 11.0 Object Library " from COM tab.

    That will add "Microsoft.Office.Interop.Excel.dll" in reference, and it will be appearing as "Excel" under references node in solution explorer.

    if you need further information, refer to the following link,

    http://support.microsoft.com/kb/302096

    HTH,

     

    Monday, January 8, 2007 9:04 AM
  • Hi,

    Probably I got your problem,

    You must not having PIAs installed on this machine where you are getting this problem, because by default Office 2003 doesnt install it AFAIK,

    So please check the following link and install the PIAs and again do the same process to add reference.

    The library to add from COM tab, Microsoft Excel 11.0 Object library will point to "...Program Files/Microsoft Office/Office11/Excel.exe"
    After adding the reference, you will see that 2 assemblies will be added on your project references. And the sample will work

    In references, "Microsoft.Office.Core" and "Excel" - two entries can be seen after

    http://support.microsoft.com/kb/827476/en-us
    Check the section under heading "Set PIAs to run from your computer" and follow the instructions to install PIAs

    I am sure your problem will be solved,

    Please inform me the results,

    HTH,

    Thursday, February 1, 2007 9:35 AM

All replies

  • Hi,

    You must be missing the Excel reference, that is "Microsoft Excel 11.0 Object Library " from COM tab.

    That will add "Microsoft.Office.Interop.Excel.dll" in reference, and it will be appearing as "Excel" under references node in solution explorer.

    if you need further information, refer to the following link,

    http://support.microsoft.com/kb/302096

    HTH,

     

    Monday, January 8, 2007 9:04 AM
  • Hi,

    Many thanks for your speedy response to my plea for help!

    I can see why you might think that I am missing the "Microsoft Excel 11.0 Object Library ", but strangely, I have it installed and it will  appears as "Excel" under references node in solution explorer.  I have checked its properties against the ones for the same Reference in the DEMO, which works, & they are exactly the same, except for the path to the DLL.  I have checked everything else and I can't see any differences, except that I get the errors.

    I have tried building a new version of the DEMO code from the KB file and it works fine!

    Is there a problem with loading this code into a DLL, rather than an application?

    Is there any documentation on the commands used in these examples?

    All suggestions welcome, well most anyway!!!

     

    John

     

     

     

    Monday, January 8, 2007 12:25 PM
  • Seems difficult to visualize your problem as your DEMO works...

    Ok, can you please do one thing?, if your demo works then please add Microsoft.Office.Interop.Excel.dll from your DEMO project's bin/debug folder in your main application and check if it works... i am suggesting this because you said that this dll name/path is diff in your main app than your DEMO app..

    Give it a try and inform me the result,

    HTH,

    Tuesday, January 9, 2007 5:21 PM
  • Hi,

    My apologies for disappearing.  I have been unexpectedly stuck out on site for the last few weeks and I am afraid that this has had to be put on the back burner.

    However, I am now back in the office and have cleared my desk of a mountain of junk and am now stuck with my old problem.

    I will try your suggestion and get back to you.

    John

    Tuesday, January 30, 2007 11:28 AM
  • Hi Mahesh,

    I have found out quite a lot this morning.

    1. I didn't get a DLL file saved in BIN\DEBUG until I 'built' the dll.  Fair enough.
    2. When I did get a dll saved, it is called Interop.Excel.dll
    3. The line using Excel = Microsoft.Office.Interop.Excel;  still causes an error 'The type or namespace name 'Interop' does not exist in the namesapce 'Microsoft.Office'.

    4. The definitions in the KB file, such as private Excel.Application m_objExcel = null need to be changed to  public static Excel.Application m_objExcel = null; otherwise I get errors in the program lines, such as m_objExcel = new Excel.Application();. Error is 'An object reference is required for the nonstatic field, method or property 'AnnsonExcelDLL2.WriteExcel.m_objExcel'

    The upshot of all this is that I am making progress, but it is the progress of a blind man in a dark room!!   

    Is there any documentation anywhere that explains lines like

    m_objRange = m_objSheet.get_Range("A1", m_objOpt);?? 

    I can understand what it does and I can understand how to adapt it to do what I want it to do, but not where it comes from and how to create the line in the first place without looking at a KB file

     

    Many thanks

    John Woodiwiss

    Wednesday, January 31, 2007 9:50 AM
  • Hi,

    1. Are you sure that you have added "Microsoft Excel 11.0 Object Library " in your project from Add Reference... -> Com tab? Please verify it again. It automatically create the interop dll and add it to your project and then adding "using" statement for excel should work fine. Its working with me. I have MS Office 2003 installed in my system and i hope its intalled there in your machine too.

    2. I have not much worked on this so cant say about the methods etc but you can refer the links i have posted in the following post link if it can help you,
    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1156768&SiteID=1
    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1147069&SiteID=1

    HTH,

    Wednesday, January 31, 2007 3:13 PM
  • Hi,

    I have done it again as follows:

    1. I created a new application called TestExcel3.
    2. Using Project\Add reference I clicked on the COM tab and scrolled down to "Microsoft Excel 11.0 object library version 1.5 in Path C:\Program files\Microsoft\Office\Excel.exe".
    3. I used Build\Build TestExcel3.
    4. I have Interop.Excel.dll in Visual Studio 2005\Projects\TestExcel3\TestExcel3\Bin\Debug
    5. I can add using System.Runtime.InteropServices;
    6. If I try to add using Excel = Microsoft.Office, the only option I get is Core
    7. I have opened EXCEL and see Excel 2003 ( 11.8105.8107 ) SP2.

    Thanks for the links, I will check them out whilst I am installing Windows XP on a new machine.  Isn't life exciting?

    Many thanks for your help

    John Woodiwiss

    Thursday, February 1, 2007 7:40 AM
  • Hi,

    Probably I got your problem,

    You must not having PIAs installed on this machine where you are getting this problem, because by default Office 2003 doesnt install it AFAIK,

    So please check the following link and install the PIAs and again do the same process to add reference.

    The library to add from COM tab, Microsoft Excel 11.0 Object library will point to "...Program Files/Microsoft Office/Office11/Excel.exe"
    After adding the reference, you will see that 2 assemblies will be added on your project references. And the sample will work

    In references, "Microsoft.Office.Core" and "Excel" - two entries can be seen after

    http://support.microsoft.com/kb/827476/en-us
    Check the section under heading "Set PIAs to run from your computer" and follow the instructions to install PIAs

    I am sure your problem will be solved,

    Please inform me the results,

    HTH,

    Thursday, February 1, 2007 9:35 AM
  • Hi Manesh,

    Many thanks for your message.

    It took me 3 goes to find all the hidden settings in the Office repair screen, but I finally got there.

    I now have access to using Excel = Microsoft.Office.Interop.Excel;

    I don't know what I am going to do with it, but at least it's there!!!

     

    However, all this has not made any difference to my main problem.

    If I have a definition:

    private Excel.Application m_objExcel = null

    which I copied from the KB file, then, when I attempt to use the definition as in;

     m_objExcel = new Excel.Application();

     then I get an error message;

    An object reference is required for the nonstatic field, method or property AnnsonDll.excel.m_objExcel. 

    I can only remove this error by changing the definition to

    private static Excel.Application m_objExcel = null;

    Is this either correct or OK?

    Many thanks for all your help and patience.

     

    John Woodiwiss

    Thursday, February 1, 2007 11:42 AM
  • Hi,

    Yes that is correct as far as you are calling it from STATIC method. Non-static member cant be called from static method so if you are calling that from Main method (which is static) of form then you must add "static" for m_objExcel which you did and thats correct.

    Good to know that finally it has been solved...

    Regards,

    Thursday, February 1, 2007 12:27 PM
  • I have an example that shows how to open an Excel workbook and save it as CSV. It doesn't do exactly what your after but it should get you started in the right direction. http://www.jarloo.com/excel-to-csv/
    Thursday, October 13, 2011 7:06 PM