locked
Convert Vb.net "CreateObject("Excel.Application")" into C#.net RRS feed

  • Question

  • Please can anyone help me to convert following code into c#

    to use it into SSIS script

     

    Dim objExcel As Object = CreateObject("Excel.Application")

    Dim objBook As Object = objExcel.Workbooks.Open("C:\temp.xls")

    Dim objSheet As Object = objBook.Worksheets(1)

    Output0Buffer.Date = CDate(objSheet.Range("b7").value)

    Output0Buffer.Amount = CDate(objSheet.Range("c7").value)
    Ashish Fugat (9960978134) Software Engineer
    Tuesday, September 20, 2011 6:46 AM

Answers

  • Hi,

    AFAIK the access of COM Objects in C# is not as easy as in VB. VB has a great advantage in this point (Just use this CreateObject call!). That was a reason why in the past some c# developers made a VB module :)

    But with C# 4 C# got dynamics which help a lot, too. There was a MSDN Magazine article on this:
    http://msdn.microsoft.com/en-us/magazine/gg598922.aspx

    There is also an example there showing how to easily access Excel.

    So your code could look a little like this (Just the first lines):

    // On top
    using Excel = Microsoft.Office.Interop.Excel;

    // Your code
    var objExcel = new Excel.Application();
    var objBook = objExcel.Workbooks.Open("C:\\temp.xls");
    var objSheet = objBook.Worksheets[1];

    With kind regards,

    Konrad 


    Edit: Of course: The code I showed has nothing to do with dynamics! But it can come in depending of what you do ... see MSDN article pls!
    • Edited by Konrad Neitzel Tuesday, September 20, 2011 7:19 AM
    • Marked as answer by Martin_Xie Thursday, September 29, 2011 2:43 AM
    Tuesday, September 20, 2011 7:18 AM
  • I see, c# needs another approach. First, you need to add a reference to the Microsoft Excel Object Library.
    1. On the Project menu, click Add Reference.
    2. On the COM tab, locate Microsoft Excel Object Library, and click Select.
    3. Click OK in the Add References dialog box to accept your selections. If you are prompted to generate wrappers for the libraries that you selected, click Yes.

     

                Microsoft.Office.Interop.Excel.Application objExcel = new Microsoft.Office.Interop.Excel.Application();
    
                Microsoft.Office.Interop.Excel.Workbook objBook = objExcel.Workbooks.Open(@"C:\temp.xls");
    
                var objSheet = objBook.Worksheets[1];
    
                Output0Buffer.Date = DateTime.Parse(objSheet.Range["b7"].value);
    
                Output0Buffer.Amount = DateTime.Parse(objSheet.Range["c7"].value); 
    
    

     

     



    • Edited by MCCZ Tuesday, September 20, 2011 7:26 AM
    • Marked as answer by Martin_Xie Thursday, September 29, 2011 2:44 AM
    Tuesday, September 20, 2011 7:14 AM
  • The equivalent of CreateObject is something like this:

    dynamic objExcel =
    Activator.CreateInstance(Type.GetTypeFromProgID("Excel.Application"));
    
    
    

     

    • Proposed as answer by kendfrey Tuesday, September 20, 2011 12:30 PM
    • Marked as answer by Martin_Xie Thursday, September 29, 2011 2:43 AM
    Tuesday, September 20, 2011 8:01 AM

All replies

  • Maybe this:

    object objExcel = CreateObject("Excel.Application");
    object objBook = objExcel.Workbooks.Open("C:\\temp.xls");
    object objSheet = objBook.Worksheets(1);
    Output0Buffer.Date = DateTime.Parse(objSheet.Range("b7").value);
    Output0Buffer.Amount = DateTime.Parse(objSheet.Range("c7").value);
    



    Mitja
    Tuesday, September 20, 2011 6:57 AM
  •             object objExcel = CreateObject("Excel.Application");
    
                object objBook = objExcel.Workbooks.Open(@"C:\temp.xls");
    
                object objSheet = objBook.Worksheets(1);
    
                Output0Buffer.Date = DateTime.Parse(objSheet.Range("b7").value);
    
                Output0Buffer.Amount = DateTime.Parse(objSheet.Range("c7").value); 
    
    

    Tuesday, September 20, 2011 6:58 AM
  •             object objExcel = CreateObject("Excel.Application");
    
                object objBook = objExcel.Workbooks.Open(@"C:\temp.xls");
    
                object objSheet = objBook.Worksheets(1);
    
                Output0Buffer.Date = DateTime.Parse(objSheet.Range("b7").value);
    
                Output0Buffer.Amount = DateTime.Parse(objSheet.Range("c7").value); 
    
    

    NOT WORKING ERROR :(


    Ashish Fugat (9960978134) Software Engineer
    Tuesday, September 20, 2011 7:01 AM
  • Where did you find this code?

    What exactlly are you trying to do (describe)?

    And did you add new reference of the Excel into a project?


    Mitja
    Tuesday, September 20, 2011 7:07 AM
  • Where did you find this code?

    What exactlly are you trying to do (describe)?

    And did you add new reference of the Excel into a project?


    Mitja

    Hi,

    I have one excel file from that I want to take two values for that someone suggest me to do script writting

    in SSIS package...

    I want value of price which is  at cell  b11 and date is at cell c3

    I have also tried like i took a excel source and in openrowset property i set 'SHEET1$B11:C3'


    Ashish Fugat (9960978134) Software Engineer
    Tuesday, September 20, 2011 7:13 AM
  • I see, c# needs another approach. First, you need to add a reference to the Microsoft Excel Object Library.
    1. On the Project menu, click Add Reference.
    2. On the COM tab, locate Microsoft Excel Object Library, and click Select.
    3. Click OK in the Add References dialog box to accept your selections. If you are prompted to generate wrappers for the libraries that you selected, click Yes.

     

                Microsoft.Office.Interop.Excel.Application objExcel = new Microsoft.Office.Interop.Excel.Application();
    
                Microsoft.Office.Interop.Excel.Workbook objBook = objExcel.Workbooks.Open(@"C:\temp.xls");
    
                var objSheet = objBook.Worksheets[1];
    
                Output0Buffer.Date = DateTime.Parse(objSheet.Range["b7"].value);
    
                Output0Buffer.Amount = DateTime.Parse(objSheet.Range["c7"].value); 
    
    

     

     



    • Edited by MCCZ Tuesday, September 20, 2011 7:26 AM
    • Marked as answer by Martin_Xie Thursday, September 29, 2011 2:44 AM
    Tuesday, September 20, 2011 7:14 AM
  • Hi,

    AFAIK the access of COM Objects in C# is not as easy as in VB. VB has a great advantage in this point (Just use this CreateObject call!). That was a reason why in the past some c# developers made a VB module :)

    But with C# 4 C# got dynamics which help a lot, too. There was a MSDN Magazine article on this:
    http://msdn.microsoft.com/en-us/magazine/gg598922.aspx

    There is also an example there showing how to easily access Excel.

    So your code could look a little like this (Just the first lines):

    // On top
    using Excel = Microsoft.Office.Interop.Excel;

    // Your code
    var objExcel = new Excel.Application();
    var objBook = objExcel.Workbooks.Open("C:\\temp.xls");
    var objSheet = objBook.Worksheets[1];

    With kind regards,

    Konrad 


    Edit: Of course: The code I showed has nothing to do with dynamics! But it can come in depending of what you do ... see MSDN article pls!
    • Edited by Konrad Neitzel Tuesday, September 20, 2011 7:19 AM
    • Marked as answer by Martin_Xie Thursday, September 29, 2011 2:43 AM
    Tuesday, September 20, 2011 7:18 AM
  • The Excel.Range.Cells[,] indexer returns any cells from the first area of the range. But note that Excel uses base 1 indexing (not base 0). So to access the top-left cell of the range, you could use to get a single value from cell:

     

    Excel.Range _currency = (Excel.Range)targetRange.Cells[2,11];
    Excel.Range _date = (Excel.Range)targetRange.Cells[3,3];
    


     


    Mitja
    Tuesday, September 20, 2011 7:25 AM
  • the method CreateObject  is defined in Microsoft.VisualBasic.Interaction


    the ideal way to manipulate EXCEL is treated as Database like MS-Access

    or add Excel COM reference (the excel version of runtime environment must be the same with the design environment)

     


    先查MSDN文件庫
    再用GOOGLE搜尋
    才到論壇來發問

     

    這是論壇不是技術支援中心
    沒有人得無償解答你的問題

     

    在標題或文章註明很急
    不會增加網友回覆速度
    • Edited by Alex_Lee Tuesday, September 20, 2011 7:29 AM
    Tuesday, September 20, 2011 7:26 AM
  • Bah, I really had to look much more ... I was sure I read on a MSDN Magazine about dynamics and COM and I was really frustrated to be unable to find it ... so I went through all issues ...

    My fault was, that it was not a real article ... it was just the "cutting edge" article in a magazine:
    http://msdn.microsoft.com/en-us/magazine/ff714583.aspx

    This covers the topic of the thread much better ...

    And by the way: Some more links about dynamics in C#:
    http://msdn.microsoft.com/en-us/magazine/ff796223.aspx
    http://msdn.microsoft.com/en-us/magazine/ee336309.aspx

    With kind regards,

    Konrad

    Tuesday, September 20, 2011 7:33 AM
  • I see, c# needs another approach. First, you need to add a reference to the Microsoft Excel Object Library.
    1. On the Project menu, click Add Reference.
    2. On the COM tab, locate Microsoft Excel Object Library, and click Select.
    3. Click OK in the Add References dialog box to accept your selections. If you are prompted to generate wrappers for the libraries that you selected, click Yes.

     

                Microsoft.Office.Interop.Excel.Application objExcel = new Microsoft.Office.Interop.Excel.Application();
    
                Microsoft.Office.Interop.Excel.Workbook objBook = objExcel.Workbooks.Open(@"C:\temp.xls");
    
                var objSheet = objBook.Worksheets[1];
    
                Output0Buffer.Date = DateTime.Parse(objSheet.Range["b7"].value);
    
                Output0Buffer.Amount = DateTime.Parse(objSheet.Range["c7"].value); 
    
    

     

     



    Is there another way to do my task please??
    because I'm using C# and I have vb script code so conversion having many problems...
    PLEASE LET ME KNOW HAW CAN I DO THIS WITH SIMPLE "EXCEL SOURCE"??

    Ashish Fugat (9960978134) Software Engineer
    Tuesday, September 20, 2011 7:56 AM
  • The equivalent of CreateObject is something like this:

    dynamic objExcel =
    Activator.CreateInstance(Type.GetTypeFromProgID("Excel.Application"));
    
    
    

     

    • Proposed as answer by kendfrey Tuesday, September 20, 2011 12:30 PM
    • Marked as answer by Martin_Xie Thursday, September 29, 2011 2:43 AM
    Tuesday, September 20, 2011 8:01 AM
  • You can do more and convert Vb.net/C#.net "CreateObject("Excel.Application")" into Vb.net/C#.net Google Spreadsheets.
    Use GSpread.NET. to work with Google Spreadsheets by using Microsoft Excel API.

    You do not need to rewrite all the code just add:

    Set objExcel = CreateObject("GSpreadCOM.Application")

    // Name             - User name, any you like
    // ClientIdAndSecret - `client_id|client_secret` format
    // ScriptId         - Google Apps script ID
    app.MailLogon(Name, ClientIdAndSecret, ScriptId);

    It's an OpenSource.
    http://scand.com/products/gspread/index.html
    Friday, May 27, 2016 1:43 PM