Open Excel 'IN' vb.net?

[Dudestreet]

Hi Team,

I was wondering if I am the only one who is not able to do it or what.
Well, as the title suggests, I would like to OPen Excel in vb.net. I was able to find end number of articles online on this but none would help.

It all comes to this, as soon as I run my program, it gives me an option to Open, Save or Cancel but wouldn't open within VS.

here's my code. Please guide:

Imports Excel = Microsoft.Office.Interop.Excel
    Imports Office = Microsoft.Office.Core


    Public Class Training_Forms

        Private xlApp As New Excel.Application
        Private xlWook As Excel.Workbook

    Private Sub WebBrowser1_DocumentCompleted(ByVal sender As Object, ByVal e As System.Windows.Forms.WebBrowserDocumentCompletedEventArgs)
            With xlApp.CommandBars("Standard")
                .Position = Microsoft.Office.Core.MsoBarPosition.msoBarTop
                .Visible = True
            End With

            With xlApp.CommandBars("Worksheet Menu Bar")
                .Position = Microsoft.Office.Core.MsoBarPosition.msoBarTop
                .Visible = True
            End With
        End Sub

        Private Sub OpenToolStripMenuItem_Click(ByVal sender As Object, ByVal e As EventArgs) Handles OpenToolStripMenuItem.Click
            Dim OpenBatch As String = InputBox("Please Enter The Batch Number in Numerical Value Only", "Open a Batch Folder", "00")
            Try
                If OpenBatch = "00" Then
                    MsgBox("Please enter a valid batch number")
                ElseIf OpenBatch = "" Then
                    MsgBox("Process Cancelled")

                Else

                    Me.WebBrowser1.Navigate("\\10.197.50.253\Department\Training\Batch " + OpenBatch + "\BatchForm.xlsx") 'xlWook.FullName)

                End If


            Catch ex As Exception

            End Try
        End Sub

        Private Sub NewToolStripMenuItem_Click(ByVal sender As Object, ByVal e As EventArgs) Handles NewToolStripMenuItem.Click
            Dim NewBatch As String = InputBox("Please Enter The Batch Number in Numerical Value Only", "Create New Batch Folder", "00")

            Try
                If NewBatch = "00" Then
                    MsgBox("Please enter a valid batch number")
                ElseIf NewBatch = "" Then
                    MsgBox("Process Cancelled")

                Else

                    If (Not System.IO.Directory.Exists("\\10.197.50.253\Department\Training\Batch " + NewBatch + "")) Then
                        System.IO.Directory.CreateDirectory("\\10.197.50.253\Department\Training\Batch " + NewBatch + "")
                        FileToCopy = "\\10.197.50.253\Department\Training\BatchForm.xlsx"
                        NewCopy = "\\10.197.50.253\Department\Training\Batch " + NewBatch + "\BatchForm.xlsx"
                        MsgBox("Success!", MsgBoxStyle.Information)
                        System.IO.File.Copy(FileToCopy, NewCopy)

                    End If

                End If


            Catch ex As Exception

            End Try
        End Sub
        End Class

[Reverend Jim]

Have a look at these examples in the Code Snippet section. You can find them here and here

[Dudestreet]

Thanks Jim for getting back. I appriciate your prompt response.
I am looking for something like embedding an Excel Sheet(s) in VB:

PFA Image

[Reverend Jim]

According to Microsoft you cannot embed a spreadsheet directly in a vb.net form, however, you can embed it in a web browser control, then embed the web browser control in your form. There is an example of how to do that in c# here that may help you to do it in vb.net.

[Dudestreet]

Okay will try this way :)
Thanks again. Will close this tomorrow.

[Dudestreet]

Hi Jim,

I was able to manage to do exactly what it says in there.
I also found out the other tutorial that shows us on how to embed it using VB

But alas, it dosen't work either way. I am surprised but if you can suggest me anything else.

Note:It just pops out giving me an option to either save, open or cancel an xlsx file.

[samir_ibrahim]

You can embed Excel and Word inside vb/c# form. I also suffered alot to get a working a code where I found alot of posts says that web browser can embed excel but that did not work with me

Here is my way

Add a Form, Button, Panel
This will display Excel inside the panel

Add Refrence to : Microsoft Office ?? Object Library

Imports Microsoft.Office.Interop

Public Class Form1
    Declare Auto Function SetParent Lib "user32.dll" (ByVal hWndChild As IntPtr, ByVal hWndNewParent As IntPtr) As Integer
    Declare Auto Function SendMessage Lib "user32.dll" (ByVal hWnd As IntPtr, ByVal Msg As Integer, ByVal wParam As Integer, ByVal lParam As Integer) As Integer
    Private Const WM_SYSCOMMAND As Integer = 274
    Private Const SC_MAXIMIZE As Integer = 61488
    Private Sub btnShowExcel_Click(sender As Object, e As EventArgs) Handles btnShowExcel.Click
        Dim sExcelFileName = "C:\myfolder\myexcel.xlsx"
        Dim oExcel As New Excel.Application
        oExcel.DisplayAlerts = False
        oExcel.Workbooks.Open(sExcelFileName)
        oExcel.Application.WindowState = Excel.XlWindowState.xlMaximized
        oExcel.Visible = True

        SetParent(oExcel.Hwnd, pnlExcel.Handle)
        SendMessage(oExcel.Hwnd, WM_SYSCOMMAND, SC_MAXIMIZE, 0)
    End Sub
End Class

[tarunap]

First place the import statements at the top of the form or code module you are working in

Imports Excel = Microsoft.Office.Interop.Excel
Imports Microsoft.Office

Public Sub OpenExcelDemo(ByVal FileName As String, ByVal SheetName As String)
    If IO.File.Exists(FileName) Then
        Dim Proceed As Boolean = False
        Dim xlApp As Excel.Application = Nothing
        Dim xlWorkBooks As Excel.Workbooks = Nothing
        Dim xlWorkBook As Excel.Workbook = Nothing
        Dim xlWorkSheet As Excel.Worksheet = Nothing
        Dim xlWorkSheets As Excel.Sheets = Nothing
        Dim xlCells As Excel.Range = Nothing
        xlApp = New Excel.Application
        xlApp.DisplayAlerts = False
        xlWorkBooks = xlApp.Workbooks
        xlWorkBook = xlWorkBooks.Open(FileName)
        xlApp.Visible = True
        xlWorkSheets = xlWorkBook.Sheets
        For x As Integer = 1 To xlWorkSheets.Count
            xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)
            If xlWorkSheet.Name = SheetName Then
                Console.WriteLine(SheetName)
                Proceed = True
                Exit For
            End If
            Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet)
            xlWorkSheet = Nothing
        Next
        If Proceed Then
            xlWorkSheet.Activate()
            MessageBox.Show("File is open, if you close Excel just opened outside of this program we will crash-n-burn.")
        Else
            MessageBox.Show(SheetName & " not found.")
        End If
        xlWorkBook.Close()
        xlApp.UserControl = True
        xlApp.Quit()
        ReleaseComObject(xlCells)
        ReleaseComObject(xlWorkSheets)
        ReleaseComObject(xlWorkSheet)
        ReleaseComObject(xlWorkBook)
        ReleaseComObject(xlWorkBooks)
        ReleaseComObject(xlApp)
    Else
        MessageBox.Show("'" & FileName & "' not located. Try one of the write examples first.")
    End If
End Sub
Public Sub ReleaseComObject(ByVal obj As Object)
    Try
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
        obj = Nothing
    Catch ex As Exception
        obj = Nothing
    End Try
End Sub

[Dudestreet]

You can embed Excel and Word inside vb/c# form. I also suffered alot to get a working a code w

I also suffered alot to get a working a code where I found alot of posts says that web browser can embed excel but that did not work with me

Here is my way

Add a Form, Button, Panel
This will display Excel inside the panel

Genius!! Excellent work there man. Thanks a lot. Moderators, request to kindly Bookmark this Public. It was actually a pain in the arse untill now.