search
Japanese Chinese Nederlands Espanol Italiano Deutsch Francais Twitter Rss Feeds
MicrosoftArticlesForumsFAQs
C# .NET
VB.NET
Visual Studio .NET
ADO.NET
Xml / Xslt
VB 6.0
.NET CF
GDI+
LINQ
Deployment
Security
FoxPro
Silverlight / WPF
Entity Framework
RIA Services

Web ProgrammingArticlesForumsFAQs
JavaScript
ASP
ASP.NET
Web Services

Non-MicrosoftArticlesForumsFAQs
NHibernate
Perl
PHP
Ruby
Java
Linux / Unix
Apple
Open Source

DatabasesArticlesForumsFAQs
SQL Server
Access
Oracle
MySQL
Other Databases

OfficeArticlesForumsFAQs
Excel
Word
Powerpoint
Outlook
Publisher
Money

Operating SystemsArticlesForumsFAQs
Windows 7
Windows Server
Windows Vista
Windows XP
Windows Update
MAC
Linux / UNIX

Server PlatformsArticlesForumsFAQs
BizTalk
Site Server
Exhange Server
IIS

Graphic DesignArticlesForumsFAQs
Macromedia Flash
Adobe PhotoShop
Expression Blend
Expression Design
Expression Web

OtherArticlesForumsFAQs
Subversion / CVS
Ask Dr. Dotnetsky
Active Directory
Networking
Uninstall Virus
Job Openings
Product Reviews
Search Engines
Resumes

 

View Other VB.NET Posts   Ask New Question 
Excel Add-in in VB.Net - How to access the workbook/worksheet?
Huzefa Motiwalla posted at Wednesday, May 06, 2009 6:27 PM
end of post

 
  objExcel.Workbooks and objExcel.Worksheets
Venkata K replied to Huzefa Motiwalla at 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
 
  here is the sample code
H K replied to Huzefa Motiwalla at 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

 
  easier way
Bill Look replied to Huzefa Motiwalla at 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
 
re
Web star replied to Huzefa Motiwalla at 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());

 
  re:
Filip Krnjic replied to Huzefa Motiwalla at 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