Add/ Embed excel file

Hello.

Is there a way to add/embed an excel file as a part of a project in vb2010?

I have tried to add it as a ''resources file'' as I have read about text files (http://support.microsoft.com/kb/319291) but it seems there is an issue.

I have tried just to open it with no luck. That's the code i have used so far

Code:

---

 
Imports Microsoft.Office.Interop

Public Class Form5

Dim app As New Excel.Application
Dim sheet As Excel.Worksheet
Dim book As Excel.Workbook

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
Button1.Click

Me.app.Workbooks.Open("WindowsApplication1.book.xls")

end sub

---

I get this
''ERROR: FILE COULD NOT BE FOUND...''

As far as i know there is no real way to embed the file, but opening it would be like the below, this will open it in excel

Code:

---

  Process.Start("C:\data\test.xls")

---

Hello,

Here I added Countries.xlsx to project resources, upon form1 load event firing we see if the file exists in the same folder as the app, if not we extract it. Next we get data from a specific sheet and a specific range. All memory is released properly when done.

Code:

---

Option Strict On
Option Infer On

Imports Excel = Microsoft.Office.Interop.Excel
Imports Microsoft.Office
Imports System.Runtime.InteropServices

Public Class Form1

    Private FileName As String = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Countries.xlsx")

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        If Not IO.File.Exists(FileName) Then
            My.Computer.FileSystem.WriteAllBytes(FileName, My.Resources.Countries, False)
        End If
        ListBox1.DataSource = OpenExcelReturnSomeCountries(FileName, "countrylist", "A85", "A185")
    End Sub
    ''' <summary>
    ''' </summary>
    ''' <param name="FileName"></param>
    ''' <param name="SheetName"></param>
    ''' <param name="StartOfRange"></param>
    ''' <param name="EndOfRange"></param>
    ''' <returns></returns>
    ''' <remarks>
    '''
    ''' </remarks>
    Public Function OpenExcelReturnSomeCountries(
                    ByVal FileName As String,
                    ByVal SheetName As String,
                    ByVal StartOfRange As String,
                    ByVal EndOfRange As String) _
    As List(Of String)

        Dim SomeCountries As New List(Of 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 = False

            xlWorkSheets = xlWorkBook.Sheets


            '
            ' For/Next finds our sheet
            '
            For x As Integer = 1 To xlWorkSheets.Count
                xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)

                If xlWorkSheet.Name = SheetName Then
                    Proceed = True
                    Exit For
                End If

                Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet)
                xlWorkSheet = Nothing

            Next

            If Proceed Then
                Try
                    Dim xlValuesRange As Excel.Range = xlWorkSheet.Range(StartOfRange, EndOfRange)
                    Dim Values(,) As Object = CType(xlValuesRange.Value, Object(,))
                    For i As Int32 = 1 To Values.GetUpperBound(0)
                        SomeCountries.Add(CStr(Values(i, 1)))
                    Next
                    Runtime.InteropServices.Marshal.FinalReleaseComObject(xlValuesRange)
                    xlValuesRange = Nothing
                Catch ex As Exception
                    Console.WriteLine(ex.Message)
                End Try

            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

        Return SomeCountries

    End Function
    Private 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
End Class

---

VS2012, VB.NET working project, if you get an error while compiling go to project properties, references and update the Excel reference.

IMPORTANT NOTE many developers will use simple code to work with Excel, generally speaking this leads to problems from memory leaks and more. Seealso

@kevininstructor
Nice project, but it isn't what I am looking for...
I just need a couple of things to do with my project.
1) I have already an excel with some data/functions
2) I want to find a way to add/emded the excel as a part of my project
3) Click a command button and open the excel to work with.

P.S. I get this error. I have already add the excel named ''book.xls'' to ''Resources''

''COM exception was unhandled.
WindowsApplication1.book.xls' could not be found. Check the spelling of the file name, and verify that the file location is correct.''

Quote:

---

Originally Posted by stratos

@kevininstructor
Nice project, but it isn't what I am looking for...
I just need a couple of things to do with my project.
1) I have already an excel with some data/functions
2) I want to find a way to add/emded the excel as a part of my project
3) Click a command button and open the excel to work with.

P.S. I get this error. I have already add the excel named ''book.xls'' to ''Resources''

''COM exception was unhandled.
WindowsApplication1.book.xls' could not be found. Check the spelling of the file name, and verify that the file location is correct.''

---

In regards to embedding, that is done (as I did in that project) via project properties->resources-> add existing file and gave you a method to open it. Now if you want to 'show' it then using my code this xlApp.Visible = False
changes to xlApp.Visible = True. In regards to the COM exception, again if you follow my code that will not happen.

A while back you could use the webbrowser control to view it but now you cant unless you ,odify the registry or are using office versions less than 2007
use webbrowser
http://support.microsoft.com/kb/304643
change behaviour
http://support.microsoft.com/kb/927009

microsoft recommends using a custom activex control in place of this