Open Excel File in Vb.net

Question

 

Hi,

        I have an excel file,having lot or micros, connecting to database, doing some complex calculation..inshot that excel file is itself an application.

        Now I want to open that excel in VB.net form, on button click. I prefer to open that excel in vb.net form itself having all the excel tool bar etc, If that is not possible then opening in separate window using excel will also solve my purpose (coz when we open in excel it self we will get all the toolbar option etc automatically)

Please Help

Anurag

Answer 1

The following will open up the the excel sheet in excel - callable from VB.NET

 

System.Diagnostics.Process.Start("C:\test.xls")

 

If you want to embed the excel sheet in a vb.net form, then there is a control which you can put on a form

 

In the Visual Studio .NET Evironment, right-click on a toolbox tab and select add/remove items. There should be an Excel Spreadsheet 9.0, 10.0, or 11.0 control depending on your version of Office. I believe it is located under the COM Components tab. Select the control and click OK. The control will then be added to your toolbox

 

You can then add this to the form from the toolbox and use the Excel controls methods to load the file you want into the control.

 

Answer 2

Hi,

          Thanks for the reply, yes i want to open excel sheet in form it self. I tried to use excel spreadsheet control..I am able to add it into my tool bar and drag it on form too..but "how to open an existing excel file" into that contol is still a challange for me..

Please help

anurag_shr

Answer 3

You should be able to use the object browser / intellisense to determine the exact call you need to make to load the worksheet.

 

I would guess it would be using a similar object model

http://msdn2.microsoft.com/en-us/library/microsoft.office.interop.excel.workbooks.open(vs.80).aspx

 

So I would look for methods for the controls which deal with opening files to populate the control.  As I dont have excel on this machine cant give you exact method.

 

 

Answer 4

Hi,

     Thanks for reply, Using excel object its quite possible to open file

[code]

Imports Excel.WorkbookClass

Imports Excel.WorksheetClass

Imports Excel.ApplicationClass

 

Dim xlsApp As Excel.ApplicationClass

Dim xlsWB As Excel.WorkbookClass

Dim xlsSheet As Excel.WorksheetClass

xlsApp = New Excel.ApplicationClass

xlsApp.Visible = True

xlsWB = xlsApp.Workbooks.Open("C:\test1.xls")

[/code]

      but that is opening file in excel it self (new window) not in to the form

 

I tried very hard to get any method to open existing excel in our "Microsoft office spreadsheet control"..used object browser/intellisense..but no luck.

 

Thanks

anurag_shr

Answer 5

Not sure if this has been answered but the following my be helpful to you or any others who view this thread:

http://social.msdn.microsoft.com/Forums/da-DK/Vsexpressvb/thread/59de618b-22cb-43df-984f-f179b139e7c6

DP

Answer 6

Hi,

embedding Excel application into VB.NET form is probably not the best choice because it would be very hard to establish two-way communication between Excel app and your form and code would be dependent on Excel app version, so entire solution wouldn't be flexible (hard to add new functionality) and would have deployment issues.

Better solution would be to VB.NET import Excel to DataTable and bind DataTable to some grid control like with using this C# / VB.NET Excel library:

Dim bindingSource = DirectCast(Me.peopleDataGridView.DataSource, BindingSource)

Dim dataTable = DirectCast(bindingSource.List, DataView).Table

Dim ef = New ExcelFile()

ef.LoadXls("MyData.xls")

Dim ws = ef.Worksheets(0)

ef.Worksheets(0).ExtractToDataTable(dataTable, ws.GetUsedCellRange(True).Height, ExtractDataOptions.None, ws.Rows(1), ws.Columns(0))

But since your file is very complex (lot of macros, complex calculations) this might not be possible for you.