locked
Microsoft.Office.Interop.Excel RRS feed

  • Question

  • User1888449941 posted

     I have the following function that I need help into converting to VB and also have it reference an existing Excel file... the function seems to be creating a new one.... Also if it's possible to include the whole procedure as one, there seems to be 3 separate steps...

     

    using Microsoft.Office.Interop.Excel;

           ApplicationClass excelApp = new ApplicationClass();
                excelApp.Visible = true;
                excelApp.UserControl = true;
                Workbook wbook = excelApp.Workbooks.Add(1);
                Worksheet wsheet = wbook.Sheets[1] as Worksheet;

    then use the columns collection to add columns.

    // to write to the sheet is easy

            #region " Excel Sheet Formating "
            private void CreateHeaders(Worksheet wsheet)
            {
                wsheet.Cells[1, 1] = "ContractID";
                wsheet.Cells[1, 2] = "ClientID";
                        
            }
            private void WriteEntry(Worksheet wSheet,int iRow,int iColumn,string sData)
            {
                wSheet.Cells[iRow, iColumn] = sData;
            }
            #endregion

     

    Thursday, March 26, 2009 5:40 PM

Answers

  • User218491331 posted

     Hi Belami,

    using Microsoft.Office.Interop.Excel

    becomes 

    Imports Microsoft.Ofifice.Interop.Excel

    is not a part of your code but a directive to include necessary namespace, it goes at the very top of your class, please note for it to work you need to incldue reference to the necessary assembly (to see how to do it, see http://www.dynamicajax.com/fr/Creating_Excel_From_VB_DOT_NET-.html)

    This code goes into your main procedure of button click handler or anywhere you need it:

    Dim excelApp As ApplicationClass = New ApplicationClass
    Dim wbook As Workbook
    wbook = excelApp.Workbooks.Open("FILE_PATH.xls", 0, False, 5, _
    System.Reflection.Missing.Value, System.Reflection.Missing.Value, _
    False, System.Reflection.Missing.Value, System.Reflection.Missing.Value, _
    True, False, System.Reflection.Missing.Value, False)

    Dim wsheet As Worksheet = wbook (1)

    wsheet.Rows(0).Cells(0).Value = "ContractID"

    wsheet.Rows(0).Cells(1).Value = "ClientID"

    However, you cannot add the content of your Write Entry function to the same procedure as this function write the data into a particular cell, for example if you need to write something into cell A1, then you would call the function:

    WriteEntry(wsheet, 1, 1, 'Sample Data')

    But the Sub itself will look like that:

    Sub WriteEntry(ByRef wSheet As Worksheet, ByVal iRow As Integer, ByVal iColumn As Integer, ByVal sData As String)

    wSheet.Rows(iRow).Cells(iColumn).Value = sData

    End Sub

    I hope it helps

    Maxxxy

    PS. Actually I hope it compiles as I don't have VB.Net compiler handy, but if you have issues using it - drop me a line I will review the errors


     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, March 27, 2009 3:02 AM

All replies

  • User218491331 posted

     Hi Belami,

    using Microsoft.Office.Interop.Excel

    becomes 

    Imports Microsoft.Ofifice.Interop.Excel

    is not a part of your code but a directive to include necessary namespace, it goes at the very top of your class, please note for it to work you need to incldue reference to the necessary assembly (to see how to do it, see http://www.dynamicajax.com/fr/Creating_Excel_From_VB_DOT_NET-.html)

    This code goes into your main procedure of button click handler or anywhere you need it:

    Dim excelApp As ApplicationClass = New ApplicationClass
    Dim wbook As Workbook
    wbook = excelApp.Workbooks.Open("FILE_PATH.xls", 0, False, 5, _
    System.Reflection.Missing.Value, System.Reflection.Missing.Value, _
    False, System.Reflection.Missing.Value, System.Reflection.Missing.Value, _
    True, False, System.Reflection.Missing.Value, False)

    Dim wsheet As Worksheet = wbook (1)

    wsheet.Rows(0).Cells(0).Value = "ContractID"

    wsheet.Rows(0).Cells(1).Value = "ClientID"

    However, you cannot add the content of your Write Entry function to the same procedure as this function write the data into a particular cell, for example if you need to write something into cell A1, then you would call the function:

    WriteEntry(wsheet, 1, 1, 'Sample Data')

    But the Sub itself will look like that:

    Sub WriteEntry(ByRef wSheet As Worksheet, ByVal iRow As Integer, ByVal iColumn As Integer, ByVal sData As String)

    wSheet.Rows(iRow).Cells(iColumn).Value = sData

    End Sub

    I hope it helps

    Maxxxy

    PS. Actually I hope it compiles as I don't have VB.Net compiler handy, but if you have issues using it - drop me a line I will review the errors


     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, March 27, 2009 3:02 AM
  • User1888449941 posted

     I will try it right away...however I dont see where do I specify the worksheet's file name

    Friday, March 27, 2009 8:20 AM
  • User218491331 posted

     in that example - you don't you simply get it by index, in this case (1), but if you need to get it by name

     

    Maxxxy

    Friday, March 27, 2009 6:14 PM
  • User1888449941 posted

     oh lol so index (1) is the 1st worksheet?

    By trhe way... the function doesn't give any results... this is how it looks like

    >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

    Imports Microsoft.Office.Interop.Excel

    -----------------------------------------------------------------------


                Dim excelApp As New Microsoft.Office.Interop.Excel.Application
                Dim wbook As Microsoft.Office.Interop.Excel.Workbooks
                wbook = excelApp.Workbooks.Open(Server.MapPath("~/ExcelImport.xls"))
                excelApp.Visible = True
                excelApp.UserControl = True

                Dim wsheet As Worksheet = wbook(1)

                WriteEntry(wsheet, 1, 15, "ATTD_1")
                WriteEntry(wsheet, 1, 16, "ATTD_2")
                WriteEntry(wsheet, 1, 17, "ATTD_3")
                WriteEntry(wsheet, 1, 18, "ATTD_4")

    End Sub

    -----------------------------------------------------------------------------

     Sub WriteEntry(ByRef wSheet As Worksheet, ByVal iRow As Integer, ByVal iColumn As Integer, ByVal sData As String)

            wSheet.Rows(iRow).Cells(iColumn).Value = sData

        End Sub

    ---------------------------------------------------

     

    Saturday, March 28, 2009 9:57 AM
  • User218491331 posted

     So what do you mean it does not work for you?

    Maxxxy

    Sunday, March 29, 2009 7:11 PM
  • User1888449941 posted

    Thank you very much. I really appreciate your help. I tried the code you suggested and it worked perfectly. Thank you again for your time

    Deeply appreciated

    Cesar

    Monday, March 30, 2009 12:57 AM