Results 1 to 25 of 25

Thread: Creating a New Project in Vb.net 2019 for Excel

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    May 2021
    Posts
    172

    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

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    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.

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    May 2021
    Posts
    172

    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

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    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.

  5. #5

    Thread Starter
    Addicted Member
    Join Date
    May 2021
    Posts
    172

    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

  6. #6
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,040

    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.

  7. #7
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Creating a New Project in Vb.net 2019 for Excel

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

  8. #8
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,464

    Re: Creating a New Project in Vb.net 2019 for Excel

    In case you missed it, option 2 is what JM recommended

  9. #9

    Thread Starter
    Addicted Member
    Join Date
    May 2021
    Posts
    172

    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

  10. #10

    Thread Starter
    Addicted Member
    Join Date
    May 2021
    Posts
    172

    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

  11. #11
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,464

    Re: Creating a New Project in Vb.net 2019 for Excel

    Looks like you didn't add the reference...

    Quote Originally Posted by ChrisE View Post
    Code:
    'add Ref.

  12. #12

    Thread Starter
    Addicted Member
    Join Date
    May 2021
    Posts
    172

    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.

  13. #13

    Thread Starter
    Addicted Member
    Join Date
    May 2021
    Posts
    172

    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.

  14. #14
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Creating a New Project in Vb.net 2019 for Excel

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

  15. #15

    Thread Starter
    Addicted Member
    Join Date
    May 2021
    Posts
    172

    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

  16. #16
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Creating a New Project in Vb.net 2019 for Excel

    Quote Originally Posted by SamDsouza View Post
    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  17. #17
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,040

    Re: Creating a New Project in Vb.net 2019 for Excel

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

  18. #18
    Fanatic Member Delaney's Avatar
    Join Date
    Nov 2019
    Location
    Paris, France
    Posts
    845

    Re: Creating a New Project in Vb.net 2019 for Excel

    Quote Originally Posted by SamDsouza View Post
    .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)

  19. #19
    Fanatic Member Delaney's Avatar
    Join Date
    Nov 2019
    Location
    Paris, France
    Posts
    845

    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)

  20. #20
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,040

    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.

  21. #21
    Frenzied Member
    Join Date
    Feb 2003
    Posts
    1,807

    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?

  22. #22

    Thread Starter
    Addicted Member
    Join Date
    May 2021
    Posts
    172

    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.

  23. #23
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,040

    Re: Creating a New Project in Vb.net 2019 for Excel

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

  24. #24
    Fanatic Member Delaney's Avatar
    Join Date
    Nov 2019
    Location
    Paris, France
    Posts
    845

    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)

  25. #25

    Thread Starter
    Addicted Member
    Join Date
    May 2021
    Posts
    172

    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
  •  



Click Here to Expand Forum to Full Width