dcsimg
Results 1 to 6 of 6

Thread: Add/ Embed excel file

  1. #1

    Thread Starter
    Member
    Join Date
    Sep 2010
    Location
    Greece
    Posts
    55

    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...''

  2. #2
    Fanatic Member
    Join Date
    Oct 2011
    Location
    Sydney, Australia
    Posts
    756

    Re: Add/ Embed excel file

    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")
    My CodeBank Submissions
    • Listbox with transparency and picture support - Click Here
    • Check for a true internet connection - Click Here
    • Open Cash drawer connected to receipt printer - Click Here
    • Custom color and size border around form - Click Here
    • Upload file to website without user logins, includes PHP - Click Here
    • List All Removable USB Storage Devices - Click Here
    • Custom On/Off Slide Control - Click Here
    • Insert multiple rows of data into one database table using parameters - Click Here
    • Trigger USB/Serial Cash Drawer - Click Here

  3. #3
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,450

    Re: Add/ Embed excel file

    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

  4. #4

    Thread Starter
    Member
    Join Date
    Sep 2010
    Location
    Greece
    Posts
    55

    Re: Add/ Embed excel file

    @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.''

  5. #5
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,450

    Re: Add/ Embed excel file

    Quote Originally Posted by stratos View Post
    @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.

  6. #6
    Fanatic Member
    Join Date
    Oct 2011
    Location
    Sydney, Australia
    Posts
    756

    Re: Add/ Embed excel file

    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
    My CodeBank Submissions
    • Listbox with transparency and picture support - Click Here
    • Check for a true internet connection - Click Here
    • Open Cash drawer connected to receipt printer - Click Here
    • Custom color and size border around form - Click Here
    • Upload file to website without user logins, includes PHP - Click Here
    • List All Removable USB Storage Devices - Click Here
    • Custom On/Off Slide Control - Click Here
    • Insert multiple rows of data into one database table using parameters - Click Here
    • Trigger USB/Serial Cash Drawer - Click Here

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width