logo

Excel Add-in in VB.Net - How to access the workbook/worksheet? (VB.NET)
Huzefa Motiwalla posted at Wednesday, May 06, 2009 6:27 PM

end of post
Reply    Reply Using Power Editor
  Rank Winnings Points
February 0 $0.00 0
January 0 $0.00 0
objExcel.Workbooks and objExcel.Worksheets
Venkat K provided a rated reply to Huzefa Motiwalla on Wednesday, May 06, 2009 8:55 PM

Private Sub Button1_Click(ByVal sender As System.Object, _
      ByVal e As System.EventArgs) Handles Button1.Click
        Dim objBooks As Excel.Workbooks
        Dim objSheets As Excel.Sheets
        Dim objSheet As Excel._Worksheet
        Dim range As Excel.Range

        ' Create a new instance of Excel and start a new workbook.
        objApp = New Excel.Application()
        objBooks = objApp.Workbooks
        objBook = objBooks.Add
        objSheets = objBook.Worksheets
        objSheet = objSheets(1)

        'Get the range where the starting cell has the address
        'm_sStartingCell and its dimensions are m_iNumRows x m_iNumCols.
        range = objSheet.Range("A1", Reflection.Missing.Value)
        range = range.Resize(5, 5)

        If (Me.FillWithStrings.Checked = False) Then
            'Create an array.
            Dim saRet(5, 5) As Double

            'Fill the array.
            Dim iRow As Long
            Dim iCol As Long
            For iRow = 0 To 5
                For iCol = 0 To 5

                    'Put a counter in the cell.
                    saRet(iRow, iCol) = iRow * iCol
                Next iCol
            Next iRow

            'Set the range value to the array.
            range.Value = saRet

        Else
            'Create an array.
            Dim saRet(5, 5) As String

            'Fill the array.
            Dim iRow As Long
            Dim iCol As Long
            For iRow = 0 To 5
                For iCol = 0 To 5

                    'Put the row and column address in the cell.
                    saRet(iRow, iCol) = iRow.ToString() + "|" + iCol.ToString()
                Next iCol
            Next iRow

            'Set the range value to the array.
            range.Value = saRet
        End If

        'Return control of Excel to the user.
        objApp.Visible = True
        objApp.UserControl = True

        'Clean up a little.
        range = Nothing
        objSheet = Nothing
        objSheets = Nothing
        objBooks = Nothing
    End Sub
Reply    Reply Using Power Editor
  Rank Winnings Points
February 19 $0.00 13
January 25 $0.00 15

here is the sample code
H K provided a rated reply to Huzefa Motiwalla on Wednesday, May 06, 2009 9:31 PM

Dim xlsApp As Excel.Application
Dim xlsWB As Excel.Workbook
Dim xlsSheet As Excel.Worksheet


xlsApp = New Excel.Application
xlsApp.Visible = True
xlsWB = xlsApp.Workbooks.Open(Path)
xlsSheet = xlsWB.Worksheets(1)

You can loop through xlsWB.Worksheets to access all the worksheets in the excel.

more samples refer
http://www.daniweb.com/forums/thread29055.html#
http://www.a1vbcode.com/snippet-3421.asp

Reply    Reply Using Power Editor
  Rank Winnings Points
February 0 $0.00 0
January 0 $0.00 0

easier way
Bill Look provided a rated reply to Huzefa Motiwalla on Wednesday, May 06, 2009 10:16 PM

Hi

you can access excel sheet  in vb.net easily ,to this you need add a lib to your project,using Microsoft.Office.Interop.Excel;

code:

Private bodynitsApplicationClass As ApplicationClass = Nothing
Private worksheetdetail As Microsoft.Office.Interop.Excel.Worksheet = Nothing

Public Sub ProcessExcell(ByVal filename As String, ByVal sheetno As String)

If bodynitsApplicationClass Is Nothing Then
bodynitsApplicationClass = New ApplicationClass()
End If
If Not System.IO.File.Exists(filename) Then
UpdateControl("Source file does not exist inside " & filename)
Exit Sub
End If
Try
' System.Data.DataTable extracteDataTAble = GetAllDataFromExcel(filename);
Dim workbooks As Microsoft.Office.Interop.Excel.Workbook = bodynitsApplicationClass.Workbooks.Open(filename, 0, True, 5, "", "", _
False, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, vbTab, False, False, 0, _
True, 1, 0)

Dim sheets As Microsoft.Office.Interop.Excel.Sheets = workbooks.Worksheets
TGSDTSysLogger.Log("Opening excel sheet...", LogForObjects.Adidas, LogInforType.Detail)


'
get header sheet to read
worksheetdetail = DirectCast(sheets.get_Item(Integer.Parse(sheetno)), Microsoft.Office.Interop.Excel.Worksheet)
' reade very cell based on your needs


Dim RLDZ As String = DirectCast(sheet.Cells(rowindex, 22), Microsoft.Office.Interop.Excel.Range).Value2.ToString().TrimEnd()
Catch ex As Exception

Throw ex

End Try
End Sub

thank you
Reply    Reply Using Power Editor
My name is Bill Look, and my profession is software developing since 2006 (having more than 4 years experience) and holding MCTS,MCPD certifications and most of developments are based on MS technologies .Specifically ASP.NET ,Sharepoint and Widows Mobiles. I have worked on ERP systems also.
  Rank Winnings Points
February 0 $0.00 0
January 0 $0.00 0

re
Web star replied to Huzefa Motiwalla on Thursday, May 07, 2009 12:27 AM

u simply download the office PIA and then put two dll in bin and add refereance to yr porject

Interop.Excel.dll

Interop.Microsoft.Office.Core.dll

 

then u will be able to access workbook/worksheet in your code as follows

using Excel;

using System.Runtime.InteropServices;

 

//using ApplicationClass u can access worksheet

Excel.ApplicationClass excelApp = new Excel.ApplicationClass();

Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);

Excel.Sheets excelSheets = excelWorkbook.Worksheets;

currentSheet = "sheetname";

Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelSheets.get_Item(currentSheet);

//u can get cell also in code

Excel.Range destination = (Excel.Range)excelWorksheet.get_Range("A" + RowCount1.ToString(), "M"+TotalRow.ToString());

Reply    Reply Using Power Editor
  Rank Winnings Points
February 7 $39.00 48
January 7 $36.00 129

re:
Filip Krnjic provided a rated reply to Huzefa Motiwalla on Monday, July 13, 2009 9:54 AM

Hi,

Best way to deal with Excel in VB is by using 3rd party component. It's much faster and easier way then Excel Automation. Try using GemBox spreadsheet component for working with Excel. Component is free for commercial use but with limit of 150 rows.

Here you can see a list of reason why GemBox.Spreadsheet much better tool to work with Excel Automation.

Filip
GemBox Software - easier and faster way to deal with Excel then Excel Automation
Reply    Reply Using Power Editor
  Rank Winnings Points
February 0 $0.00 0
January 0 $0.00 0


Didn't Find The Answer You Were Looking For?

EggHeadCafe has experts online right now that may know the answer to your question.  We pay them a bonus for answering as many questions as they can.  So, why not help them and yourself by becoming a member (free) and ask them your question right now?
Ask Question In Live Forum

If you have an OpenID and do not want to become a member of the EggHeadCafe forum, you can also sign on to Chat Chaos and post your question to our real time Silverlight chat application.
Ask Question In Chat Chaos










  $1000 Contest    [)ia6l0 iii - $228  |  Jonathan VH - $161  |  Huggy Bear - $135  |  F Cali - $95  |  egg egg - $94  |  more Advertise  |  Privacy  |   (c) 2010