-
Mar 23rd, 2014, 05:48 PM
#1
Thread Starter
Member
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...''
-
Mar 23rd, 2014, 06:23 PM
#2
Fanatic Member
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
-
Mar 24th, 2014, 08:02 AM
#3
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
-
Mar 24th, 2014, 03:31 PM
#4
Thread Starter
Member
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.''
-
Mar 24th, 2014, 04:42 PM
#5
Re: Add/ Embed excel file
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.
-
Mar 24th, 2014, 04:44 PM
#6
Fanatic Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|