Resolved How to edit excel file that include at the project

Tamiryosef

Active member
Joined
May 2, 2022
Messages
36
Programming Experience
1-3
Hello to all
I am new at visual basic.
I build windows form that get data and stored it on firebase.
Now I try to get the data the user enter also go to a invoice that I build on excel.
I added the excel invoice to my project resources library , the excel form is open.

How I can use the code to edit the excel form that add to the project?

I want to add the textbox that the user enter to be add to the excel form that open in my resource library?
Screenshot_20220502-212456_Gallery.jpg
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,717
Location
Sydney, Australia
Programming Experience
10+
Firstly, please don't post pictures of code. Screenshots are bad enough but a photo of your screen is even worse. Copy and paste the code directly as text and format it as code. That makes it easy for everyone to read, especially on a phone or other mobile device, plus it means that we can copy it to run it for ourselves or edit it and paste it back into a response. We shouldn't have to rewrite your code if we want to show you how to fix it.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,717
Location
Sydney, Australia
Programming Experience
10+
As for the issue, if you want to modify something then don't embed it as a resource. The whole point of resources is that they are embedded in the binary data of the executable and are thus read-only. If you want to modify a file then you need a file.
 

Tamiryosef

Active member
Joined
May 2, 2022
Messages
36
Programming Experience
1-3
So what i need to do ?
I want the excel file will be included on my project and install to the user pc when he install my project .
Because I like to use the excel from to automation to create invoice.

How I do that ?
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,717
Location
Sydney, Australia
Programming Experience
10+
If you want to add a file to your project then add a file to your project. Don't add a resource. You already know how to add items to your project because you've already done it. Just select the appropriate menu item for an existing file. Make sure that you set the Copy to Output Directory property for the file so that the file gets copied from your project folder to the output folder when you build. You then distribute the contents of the output folder, including your EXE and the data file and whatever else.
 

Tamiryosef

Active member
Joined
May 2, 2022
Messages
36
Programming Experience
1-3
How I do It ?

i add the file to my project but how i set the folder the file will be when i install my app?
on the prophy of the file at build action i set to "content"
and the copy to output directory i set to "copy always"

and the second this is the code i will use to edit the excel file but I get error "System.Runtime.InteropServices.COMException:"


excel:
Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles Button1.Click

        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet

        xlApp = New Excel.ApplicationClass
        xlWorkBook = xlApp.Workbooks.Open("c:\test1.xlsx")
        xlWorkSheet = xlWorkBook.Worksheets("sheet1")
        'display the cells value B2
        MsgBox(xlWorkSheet.Cells(2, 2).value)
        'edit the cell with new value
        xlWorkSheet.Cells(2, 2) = "test"
        xlWorkBook.Close()
        xlApp.Quit()

        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)

    End Sub

    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

End Class
 
Last edited:

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,717
Location
Sydney, Australia
Programming Experience
10+
In a WinForms project, you use Application.StartPath to get the path of the folder that the current EXE was run from, e.g.
VB.NET:
Dim filePath = IO.Path.Combine(Application.StartupPath, "MyFile.ext")
 

Tamiryosef

Active member
Joined
May 2, 2022
Messages
36
Programming Experience
1-3
i new at vb.net

i do it like that ?
i add the code at the form1 class ?
how i access the excel file to edit it ?


excel:
Public Class Form1
  


    Dim path = path.Combine(Application.StartupPath, "Invoice Template.xlsm")
 

Tamiryosef

Active member
Joined
May 2, 2022
Messages
36
Programming Experience
1-3
what i need is that the excel form will open from the file that i add to the folder in my vb.net project
know i open form my pc folder this is the code i use to open it

excel:
xlWorkBook = xlApp.Workbooks.Open("C:\InvoiceTemplate.xlsm")
        xlWorkSheet = xlWorkBook.Worksheets("Invoice Template")
 

Tamiryosef

Active member
Joined
May 2, 2022
Messages
36
Programming Experience
1-3
OK i success i do it like that and it open the file
thanks for the help

excel:
Dim filepath As String = IO.Path.Combine(Directory.GetCurrentDirectory(), "File\Invoice Template.xlsm")
 xlWorkBook = xlApp.Workbooks.Open(filepath)
 xlWorkSheet = xlWorkBook.Worksheets("Invoice Template")
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,717
Location
Sydney, Australia
Programming Experience
10+
How about you do what I told you instead? The current directory will often be the startup directory but it won't always be. It might be something else by default and it might change over the course of a session. The startup directory never changes, so use that. If you want a subfolder then just specify the subfolder as you already are:
VB.NET:
Dim filePath = IO.Path.Combine(Application.StartupPath, "File\Invoice Template.xlsm")
 

Tamiryosef

Active member
Joined
May 2, 2022
Messages
36
Programming Experience
1-3
hi
do like you say and know i get error that say the file it not found ,i give the path

i give the path to the file but he say the file location not found or chance
excel:
Dim files = IO.Path.Combine(Application.StartupPath, "Excel\Invoice Template.xlsm")

11.jpg
 
Last edited:

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,717
Location
Sydney, Australia
Programming Experience
10+
You've changed the name of the folder in the code. Did you change the name of the actual folder? If you want the file to be in a subfolder of the program directory then it needs to be in the same subfolder in the project directory and you need to configure it to be copied to the output folder on build.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,717
Location
Sydney, Australia
Programming Experience
10+
Argh! Where is that Excel file located? Is it under the File\Excel subfolder? If so then is it not obvious that you need to specify that folder in the path of the file when you want to open it. Look at your code. Are you doing that? Why do you even have that File folder in the first place
 

Tamiryosef

Active member
Joined
May 2, 2022
Messages
36
Programming Experience
1-3
yes the file is under File\Excel
the message i get it that he show this path "\bin\Debug\File\Excel\Invoice Template.xlsm. "

i add the code at the NewOrderScreen.Designer.vb

i have Form1 that you can access to the form call NewOrderScreen


333.jpg

44.jpg
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,717
Location
Sydney, Australia
Programming Experience
10+
It's like you're trying to make this as difficult as possible. You've actually got the right path now and you go and put it in the designer code file. WHY would you do that?! Don't touch the designer code file unless there's a specific issue with it. Put it where you put the rest of the code.
 

Tamiryosef

Active member
Joined
May 2, 2022
Messages
36
Programming Experience
1-3
It's like you're trying to make this as difficult as possible. You've actually got the right path now and you go and put it in the designer code file. WHY would you do that?! Don't touch the designer code file unless there's a specific issue with it. Put it where you put the rest of the code.

i do it and i get the same message i lose my mind!!!

555.jpg
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,717
Location
Sydney, Australia
Programming Experience
10+
I'm losing my mind too because you're not doing the simplest things. Did you look at what files actually contains, because you certainly haven't told us? Did you call File.Exists with that path?
 
Top Bottom