-
May 31st, 2021, 10:59 PM
#1
Thread Starter
Addicted Member
Creating a New Project in Vb.net 2019 for Excel
Hi
As this is my first thread i am confussed for creating a new project in VS 2019 with Vb.net 19
Can anyone help step by step as to how can I create a new project thru. VB.net for creating an Excel Application. and which would be the ideal template selection As there are so many template options. Totaly confused
Your guidance will be highly appreciated
SamD
-
May 31st, 2021, 11:24 PM
#2
Re: Creating a New Project in Vb.net 2019 for Excel
There are numerous project templates because you can do various specific things. Please provide the specific details of what you want to do. "for Excel" is far too vague. Are you talking about an add-in for Excel itself or a GUI application that uses Excel as a data source or something else? You need to be specific.
-
Jun 1st, 2021, 12:25 AM
#3
Thread Starter
Addicted Member
Re: Creating a New Project in Vb.net 2019 for Excel
jmcilhinney
There are numerous project templates because you can do various specific things. Please provide the specific details of what you want to do. "for Excel" is far too vague. Are you talking about an add-in for Excel itself or a GUI application that uses Excel as a data source or something else? You need to be specific.
Appreciate your quick response.
Well at present i am not talking on any kind of Add-in(s) for Excel nor GUI application although not far if oppurtunity rises for the same.
First of all It is to work with data from Excel sheet to Vb.Net Userform and vice versa ie to incorporate data into Excel sheet from vb.net Userform.
Once Succeeded with your guidance I am curious if VBA syntaxes in Excel which could be used in vb.Net Userform, Module etc.
Thanks
SamD
-
Jun 1st, 2021, 12:45 AM
#4
Re: Creating a New Project in Vb.net 2019 for Excel
You say that you don't want a GUI but then you talk about "Userforms", so you appear to be contradicting yourself. If you want forms then that is, by definition, a GUI. It sounds like what you want is a Windows Forms Application project. That is how you design and display your own forms in VB.NET. What you do in the code contained in those forms is up to you. Pulling data from and pushing data to Excel worksheets is just one option.
To interact with Excel, you will probably want to use Office Automation. That means referencing the appropriate Office library and then effectively remote-controlling an instance of the Excel application in your app. The code you write will be VB.NET, but it will use the same functionality as VBA does, so will look similar.
I'm not sure whether it is absolutely necessary but I strongly recommend that you select the project template that explicitly states that it targets .NET Framework. The alternative is .NET Core/.NET 5.0 and I'm not sure that that works with Office right now. You may also have to target the x86 platform if you want compatibility with 32-bit Office.
-
Jun 1st, 2021, 01:17 AM
#5
Thread Starter
Addicted Member
Re: Creating a New Project in Vb.net 2019 for Excel
You say that you don't want a GUI but then you talk about "Userforms", so you appear to be contradicting yourself. If you want forms then that is, by definition, a GUI. It sounds like what you want is a Windows Forms Application project. That is how you design and display your own forms in VB.NET. What you do in the code contained in those forms is up to you. Pulling data from and pushing data to Excel worksheets is just one option.
To interact with Excel, you will probably want to use Office Automation. That means referencing the appropriate Office library and then effectively remote-controlling an instance of the Excel application in your app. The code you write will be VB.NET, but it will use the same functionality as VBA does, so will look similar.
I'm not sure whether it is absolutely necessary but I strongly recommend that you select the project template that explicitly states that it targets .NET Framework. The alternative is .NET Core/.NET 5.0 and I'm not sure that that works with Office right now. You may also have to target the x86 platform if you want compatibility with 32-bit Office.
I am getting 2 options for
option 1
Windows Forms App
A project Template for creating .NET WindowsForms(winforms) App
Visual Basic Windows Desktop
option 2
Windows Forms App(.Net Framework)
A project Template for creating an Application with Windows Form .NET windowsForm(winforms) UserInterface
Visual Basic Windows Desktop
I really dont know which would be the perfect option for my current requirements
Will the above options have to Select MS-Office 15 or 16 Object library Via Tools>References>.......
SamD
-
Jun 1st, 2021, 01:22 AM
#6
Re: Creating a New Project in Vb.net 2019 for Excel
Hi,
here a start, this sample will create a Excelfile,add Sheet(s), Name the Sheet(s), and add
some Data to Cells
Code:
Option Strict On
'add Ref.
Imports Microsoft.Office.Interop.Excel
Imports Microsoft.Office.Interop
Public Class Form1
Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
Dim Path As String = "E:\"
Dim strFilename As String = "E:\TestFolder\SamsFirstExcel2.xlsx"
Dim myExcel As New Microsoft.Office.Interop.Excel.Application()
Dim WorkBook As Workbook = myExcel.Workbooks.Add
Dim WorkSheets As Sheets = WorkBook.Sheets
For i = 1 To WorkSheets.Count 'how many sheets do you want to add
Dim xlWorkBook As Workbook
xlWorkBook = myExcel.Workbooks.Add
'##### sheet1
Dim WorkSheet1 As Worksheet = CType(CType(WorkSheets(1), Excel._Worksheet), Microsoft.Office.Interop.Excel.Worksheet)
'name Sheet1
WorkSheet1.Name = "SamDsouza1"
'add values to sheet
WorkSheet1.Range("A1").Value = "Header 1"
WorkSheet1.Range("B1").Value = "Header 2"
WorkSheet1.Range("C1").Value = "Nr."
WorkSheet1.Range("A2").Value = TextBox1.Text
WorkSheet1.Range("A3").Value = TextBox2.Text
'etc
'....
'...
'###### sheet2
Dim WorkSheet2 As Worksheet = CType(CType(WorkSheets(2), Excel._Worksheet), Microsoft.Office.Interop.Excel.Worksheet)
'add values to sheet
WorkSheet2.Name = "SamDsouza2"
WorkSheet2.Range("A1").Value = "Test"
WorkSheet2.Range("B1").Value = "some stuff"
WorkSheet2.Range("C1").Value = "etc.."
'etc..
'....
'...
Next
Dim vRes As DialogResult = DialogResult.Yes
If System.IO.File.Exists(IO.Path.Combine(Path, strFilename)) Then
vRes = MessageBox.Show(String.Format("the ExcelFile :'{0}' exsits, do you want to replace it?", strFilename), _
"Microsoft Excel", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button2)
myExcel.DisplayAlerts = False
End If
If vRes = DialogResult.Yes Then
WorkBook.SaveAs(strFilename)
End If
WorkBook.Close()
myExcel.Quit()
End Sub
End Class
I also don't understand what you mean with "UserForms", you either work with Excel VBA
or .NET. I mean you can combine both, but I sounds like your Level of experience is a
beginner.
good luck
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
-
Jun 1st, 2021, 01:22 AM
#7
Re: Creating a New Project in Vb.net 2019 for Excel
Originally Posted by SamDsouza
I really dont know which would be the perfect option for my current requirements
Then you should go back and read what I posted again.
-
Jun 1st, 2021, 01:35 AM
#8
Re: Creating a New Project in Vb.net 2019 for Excel
In case you missed it, option 2 is what JM recommended
- Coding Examples:
- Features:
- Online Games:
- Compiled Games:
-
Jun 1st, 2021, 02:07 AM
#9
Thread Starter
Addicted Member
Re: Creating a New Project in Vb.net 2019 for Excel
ChrisE
Indeed it was wonderful and sweet of you to move me on further. But i got the following Error
System.Runtime.InteropServices.COMException: 'Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))'
at the following line
Code:
Dim WorkSheet2 As Worksheet = CType(CType(WorkSheets(2), Excel._Worksheet), Microsoft.Office.Interop.Excel.Worksheet)
ing line
-
Jun 1st, 2021, 02:09 AM
#10
Thread Starter
Addicted Member
Re: Creating a New Project in Vb.net 2019 for Excel
.Paul
In case you missed it, option 2 is what JM recommended
I got it Thanks for pointing it out
-
Jun 1st, 2021, 02:28 AM
#11
Re: Creating a New Project in Vb.net 2019 for Excel
Looks like you didn't add the reference...
Originally Posted by ChrisE
- Coding Examples:
- Features:
- Online Games:
- Compiled Games:
-
Jun 1st, 2021, 02:39 AM
#12
Thread Starter
Addicted Member
Re: Creating a New Project in Vb.net 2019 for Excel
.Paul
Looks like you didn't add the reference...
I just copied Chris Code as is and It worked for Sheet 1 and does not work for Sheet 2 To check I had used REM marks from where Dim Worksheet2 uptil etc Everthing came correct in sheet1 which was renamed as SamDsouza1 as per the coding
By the way Solution explorer i selected MS.Office.Interop.Excel but did not have option of Microsoft.Office.Interop
By the Way MS-Office Excel object Library 15.0 was added Project>Add Reference
If Ref was to be added in module Than what kind of syntax required 'add Ref.
Last edited by SamDsouza; Jun 1st, 2021 at 02:59 AM.
-
Jun 1st, 2021, 06:05 AM
#13
Thread Starter
Addicted Member
Re: Creating a New Project in Vb.net 2019 for Excel
.Paul and ChrisE
It seems a big issue adding or rather DIM Worksheet2 as Worksheet as it mentioned Invalid Index and hiliting in the procedure or sub-routine Worksheet2
Code:
Dim WorkSheet2 As Worksheet = CType(CType(WorkSheets(2), Excel._Worksheet), Microsoft.Office.Interop.Excel.Worksheet)
I am not able to get rid of this error. Bit surprised if more sheets were added then what to do
if not mistaken this code is only taking one sheet with value as Dim Worksheet1
SamD
Last edited by SamDsouza; Jun 1st, 2021 at 06:09 AM.
-
Jun 1st, 2021, 06:50 AM
#14
Re: Creating a New Project in Vb.net 2019 for Excel
Originally Posted by SamDsouza
.Paul and ChrisE
It seems a big issue adding or rather DIM Worksheet2 as Worksheet as it mentioned Invalid Index and hiliting in the procedure or sub-routine Worksheet2
Code:
Dim WorkSheet2 As Worksheet = CType(CType(WorkSheets(2), Excel._Worksheet), Microsoft.Office.Interop.Excel.Worksheet)
I am not able to get rid of this error. Bit surprised if more sheets were added then what to do
if not mistaken this code is only taking one sheet with value as Dim Worksheet1
SamD
You shouldn't just ask every question you have in this thread. This thread is about creating the project. You've done that. If you have new questions about writing code, create new thread that have titles that indicate the new topics and where each thread contains only the information relevant to that topic.
-
Jun 1st, 2021, 09:55 AM
#15
Thread Starter
Addicted Member
Re: Creating a New Project in Vb.net 2019 for Excel
jmcilhinney
You shouldn't just ask every question you have in this thread. This thread is about creating the project. You've done that. If you have new questions about writing code, create new thread that have titles that indicate the new topics and where each thread contains only the information relevant to that topic.
I understand and Completely Agree with you
But FYI as the error was observed and i kept on trying in order to get rid of the error. ie Why i kept every asking questions as # 12 and #13.
From VBA platform and jumping to VB.Net is completely new platform for me as i need to get use to the VB.Net Editor enviornment
If you still insist i will create a new thread on the error observed. I Thought that getting rid of the error could be resolved in this thread
Thanks
SamD
-
Jun 1st, 2021, 10:17 AM
#16
Re: Creating a New Project in Vb.net 2019 for Excel
Originally Posted by SamDsouza
jmcilhinney
I understand and Completely Agree with you
But FYI as the error was observed and i kept on trying in order to get rid of the error. ie Why i kept every asking questions as # 12 and #13.
From VBA platform and jumping to VB.Net is completely new platform for me as i need to get use to the VB.Net Editor enviornment
If you still insist i will create a new thread on the error observed. I Thought that getting rid of the error could be resolved in this thread
Thanks
SamD
IT could be... but we try to observe a one-question per thread treatment around here. Why? Visibility... people who can help with the issue may not ever see it because they think this thread is about selecting the right project type. It also happens sometimes that the ancillary quesitons will get burried in post after post after post in a singe thread, no one wants to read through 50+ posts to find out what the real question is any more. So it helps to keep the threads shorrt (when possible) and down to a single topic at a time.
-tg
-
Jun 1st, 2021, 10:48 AM
#17
Re: Creating a New Project in Vb.net 2019 for Excel
Originally Posted by SamDsouza
By the way Solution explorer i selected MS.Office.Interop.Excel but did not have option of Microsoft.Office.Interop
By the Way MS-Office Excel object Library 15.0 was added Project>Add Reference
If Ref was to be added in module Than what kind of syntax required 'add Ref.
what is Installed ?
only Excel or Office(Word,Excel etc...)
I tested the Code and it works, I have Office Pro 2010 Installed
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
-
Jun 1st, 2021, 03:08 PM
#18
Re: Creating a New Project in Vb.net 2019 for Excel
Originally Posted by SamDsouza
.Paul
I just copied Chris Code as is and It worked for Sheet 1 and does not work for Sheet 2 To check I had used REM marks from where Dim Worksheet2 uptil etc Everthing came correct in sheet1 which was renamed as SamDsouza1 as per the coding
By the way Solution explorer i selected MS.Office.Interop.Excel but did not have option of Microsoft.Office.Interop
By the Way MS-Office Excel object Library 15.0 was added Project>Add Reference
If Ref was to be added in module Than what kind of syntax required 'add Ref.
Did you check that you have a worksheet2 in you file ?
put a stop after
Code:
For i = 1 To WorkSheets.Count
and check the value of WorkSheets.Count
By the way I don't remember if WorkSheets() starts at 0 or 1 you could try WorkSheets(0) and WorkSheets(1) instead of WorkSheets(1) and WorkSheets(2)
Last edited by Delaney; Jun 1st, 2021 at 03:14 PM.
The best friend of any programmer is a search engine
"Don't wish it was easier, wish you were better. Don't wish for less problems, wish for more skills. Don't wish for less challenges, wish for more wisdom" (J. Rohn)
“They did not know it was impossible so they did it” (Mark Twain)
-
Jun 2nd, 2021, 03:19 AM
#19
Re: Creating a New Project in Vb.net 2019 for Excel
ChrisE, your code in post #6 really perturbed me: you have a loop based on the number of worksheets in you first workbook and at each iteration you create a new workbook (not used) and you deal with 2 worksheets.
it is obvious if you put just after the next
Code:
myExcel.Visible = True
Stop
It works (I tested it) but with the loop, I think you do WorkSheets.Count times the same job and create a lot of xlWorkBook not needed. I would understand the loop if you had something like :
Code:
Dim WorkSheet1 As Worksheet = CType(CType(WorkSheets(i), Excel._Worksheet), Microsoft.Office.Interop.Excel.Worksheet)
The best friend of any programmer is a search engine
"Don't wish it was easier, wish you were better. Don't wish for less problems, wish for more skills. Don't wish for less challenges, wish for more wisdom" (J. Rohn)
“They did not know it was impossible so they did it” (Mark Twain)
-
Jun 2nd, 2021, 03:51 AM
#20
Re: Creating a New Project in Vb.net 2019 for Excel
thanks Delaney,
the Loop isn't need at all, that's what happens when you copy and Paste without double checking
I would have "rep" you bit I get the message...you must spread around, which I find isn't correct
perhaps something for the Admin's to think about
sorry to SamDsouza for the confusion
remove the "For i = 1 To WorkSheets.Count"
and the "Next"
EDIT:
forgot to say,
the For i I use to add Sheets something like Jan to Dez or other Names
in a List for example. no Data is added only the Sheet names
Last edited by ChrisE; Jun 2nd, 2021 at 03:54 AM.
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
-
Jun 2nd, 2021, 04:36 AM
#21
Re: Creating a New Project in Vb.net 2019 for Excel
Code:
For i = 1 To WorkSheets.Count
Wouldn't a For Each loop be a better choice? And why isn't the iterator's data type declared?
-
Jun 2nd, 2021, 06:30 AM
#22
Thread Starter
Addicted Member
Re: Creating a New Project in Vb.net 2019 for Excel
ChrisE
what is Installed ?
only Excel or Office(Word,Excel etc...)
I tested the Code and it works, I have Office Pro 2010 Installed
Have Office 2013
By the way Sheet index no starts with 1 FYI
Guys it seems this can take only one woksheet at a time. Even if the other sheet or sheet2 is predefined in .xlsx file it overwrites and displays SamDsouza1 sheet only and no SamDsouza2.
No use of For Each loop also
Tried the code in the following manner
Code:
Option Strict On
Imports Microsoft.Office.Interop.Excel
Imports Microsoft.Office.Interop
Public Class ufTrialXLFirstProj
Private Sub cmdCreateXLSheets_Click(sender As System.Object, e As System.EventArgs) Handles cmdCreateXLSheets.Click
Dim Path As String = "C:\"
Dim strFilename As String = "C:\VBNET19PRO\SamsFirstExcel2.xlsx"
Dim myExcel As New Microsoft.Office.Interop.Excel.Application()
Dim WorkBook As Workbook = myExcel.Workbooks.Add
Dim WorkSheets As Sheets = WorkBook.Sheets
Dim xlWorkBook As Workbook
xlWorkBook = myExcel.Workbooks.Add
Dim wks1 As Worksheet = CType(CType(WorkSheets(1), Excel._Worksheet), Microsoft.Office.Interop.Excel.Worksheet)
wks1.Name = "SamDsouza1"
wks1.Range("A1").Value = "Header 1"
wks1.Range("B1").Value = "Header 2"
wks1.Range("C1").Value = "Nr."
wks1.Range("A2").Value = TextBox1.Text
wks1.Range("A3").Value = TextBox2.Text
Sheets.Add(After:=wks1.Name = "SamDsouza2"
Dim Wks2 As Worksheet = CType(CType(WorkSheets(1), Excel._Worksheet), Microsoft.Office.Interop.Excel.Worksheet)
'add values to sheet
''''''''Wks2.Name = "SamDsouza2"
Wks2.Range("A1").Value = "Test"
Wks2.Range("B1").Value = "some stuff"
Wks2.Range("C1").Value = "etc.."
Dim vRes As DialogResult = DialogResult.Yes
If System.IO.File.Exists(IO.Path.Combine(Path, strFilename)) Then
vRes = MessageBox.Show(String.Format("the ExcelFile :'{0}' exsits, do you want to replace it?", strFilename),
"Microsoft Excel", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button2)
myExcel.DisplayAlerts = False
End If
If vRes = DialogResult.Yes Then
WorkBook.SaveAs(strFilename)
End If
WorkBook.Close()
myExcel.Quit()
End Sub
End Class
SamD
Last edited by SamDsouza; Jun 2nd, 2021 at 06:35 AM.
-
Jun 2nd, 2021, 06:56 AM
#23
Re: Creating a New Project in Vb.net 2019 for Excel
Originally Posted by SamDsouza
ChrisE
Sheets.Add(After:=wks1.Name = "SamDsouza2"
Dim Wks2 As Worksheet = CType(CType(WorkSheets(1), Excel._Worksheet), Microsoft.Office.Interop.Excel.Worksheet)
'add values to sheet
[/CODE]
SamD
the (1) has to be a (2)
as it is the second sheet
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
-
Jun 2nd, 2021, 07:05 AM
#24
Re: Creating a New Project in Vb.net 2019 for Excel
There is no need for that also, you can remove it :
Code:
Dim xlWorkBook As Workbook
xlWorkBook = myExcel.Workbooks.Add
and you shouldn't need that
Code:
Sheets.Add(After:=wks1.Name = "SamDsouza2"
any workbook created has, by default, 3 sheets
Last edited by Delaney; Jun 2nd, 2021 at 07:08 AM.
The best friend of any programmer is a search engine
"Don't wish it was easier, wish you were better. Don't wish for less problems, wish for more skills. Don't wish for less challenges, wish for more wisdom" (J. Rohn)
“They did not know it was impossible so they did it” (Mark Twain)
-
Jun 2nd, 2021, 01:00 PM
#25
Thread Starter
Addicted Member
Re: Creating a New Project in Vb.net 2019 for Excel
Guys Error Resolved
I had to predefine the Sheetnames in the xlsx File ie "SamDsouza1" and "SamDsouza2" then run the Below Code.
and Below is the Modified version of the Code as per #6 by ChrisE
Code:
Option Strict On
'add Ref.
Imports Microsoft.Office.Interop.Excel
Imports Microsoft.Office.Interop
Public Class Form1
Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
Dim Path As String = "C:\"
Dim strFilename As String = "C:\VS19-VB-NET19\VBXL\SamsFirstExcel2.xlsx"
Dim myExcel As New Excel.Application
Dim wbk As Excel.Workbook
Dim wks1 As Excel.Worksheet
Dim wks2 As Excel.Worksheet
wbk = myExcel.Workbooks.Open(strFilename)
wks1 = CType(wbk.Worksheets("SamDsouza1"), Excel.Worksheet)
wks1.Range("A1").Value = "Header 1"
wks1.Range("B1").Value = "Header 2"
wks1.Range("C1").Value = "Nr."
wks1.Range("A2").Value = TextBox1.Text
wks1.Range("A3").Value = TextBox2.Text
wks2 = CType(wbk.Worksheets("SamDsouza2"), Excel.Worksheet)
wks2.Range("A1").Value = "Test"
wks2.Range("B1").Value = "some stuff"
wks2.Range("C1").Value = "etc.."
Dim vRes As DialogResult = DialogResult.Yes
If System.IO.File.Exists(IO.Path.Combine(Path, strFilename)) Then
vRes = MessageBox.Show(String.Format("the ExcelFile :'{0}' exsits, do you want to replace it?", strFilename),
"Microsoft Excel", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button2)
myExcel.DisplayAlerts = False
End If
If vRes = DialogResult.Yes Then
'Workbook.SaveAs(strFilename)
wbk.SaveAs(strFilename)
End If
wbk.Close()
myExcel.Quit()
End Sub
These following below statements may not be required but then i've retained them
Imports Microsoft.Office.Interop.Excel
Imports Microsoft.Office.Interop
Delaney
any workbook created has, by default, 3 sheets
FYI
File>options>General>
in Section: "When Creating New Workbooks" include this many sheets Enter 3 so you will have 3 sheets by default
Mine has 1 so the workbook opens only Sheet1 by-default
Real Fun to work with VB.Net 19
SamD
Last edited by SamDsouza; Jun 2nd, 2021 at 01:08 PM.
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
|