dcsimg
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 15 of 15

Thread: [RESOLVED] Dim statement not working in vb.net button code

  1. #1
    Join Date
    Oct 2018
    Posts
    6

    [RESOLVED] Dim statement not working in vb.net button code

    Hi. I have two buttons on my vb.net form. One opens a dialog box for user to chose excel file that needs to be worked on. The second button is where I want to be able to open the Excel file to manipulate, this button has errors on the Dim statements, and I am assuming this is why the code below will not work as well. Here is the form code so far. Is there something wrong with Public vs Private sub routines? I am at a loss here since using a Dim statement should be a very easy and basic thing ...

    Code:
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    
    namespace P01ExcelCalculationApp
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
    
     
            public void button1_Click(object sender, EventArgs e)
            {
                //Create title for Open File Dialog.
                openFileDialog1.Title = "Please Choose File to Open And Calculate";
    
                //Make initial filename in the Open File Dialog to empty.
                openFileDialog1.FileName = "";
    
               //Show only excel files in list to open.
                openFileDialog1.Filter = "Excel Worksheets|*.xls";
               
                //Show Open File Dialog form.
                openFileDialog1.ShowDialog();
    
                //Place chosen filename and path in textbox on form.
                txtFileName.Text = openFileDialog1.FileName;
                
    
                
            }
    
            public void btnCalculateLot_Click(object sender, EventArgs e)
            {
    
    
    //these are the Dims that are not working, niether will anything below it.
    
    
                //Code to open chosen Excel file and do Lot calculations
    
                Dim strTest As string
    
                Dim xlApp As Excel.Application
                Dim xlWorkBook As Excel.Workbook
                Dim xlWorkSheet As Excel.Worksheet
    
                xlApp = New Excel.ApplicationClass;
                xlWorkBook = xlApp.Workbooks.Open(strFilename);
                xlWorkSheet = xlWorkBook.Worksheets("sheet1");
    
                //display the cells value N20
                MsgBox(xlWorkSheet.Cells(19, 20).value);
    
                //edit the cell with new value
                //xlWorkSheet.Cells(2, 2) = "";
                xlWorkBook.Close();
                xlApp.Quit();
    
            }
        }
    }
    Last edited by 2kaud; October 21st, 2018 at 11:06 AM. Reason: Added code tags

  2. #2
    Join Date
    Sep 2018
    Posts
    38

    Re: Dim statement not working in vb.net button code

    I don't know any C#, Valeriegn, but don't you have to instantiate your "xlWorkBook" and "xlWorksheet" as "New xlWorkBook", etc. first? Have you tried to assign a value to strTest and then do something like display it in a message?

  3. #3
    Arjay's Avatar
    Arjay is offline Moderator / MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    12,808

    Re: Dim statement not working in vb.net button code

    As KGCole stated you need to initialed the variable with New. Think it this way... when you 'Dim' a variable, it sets aside space for the variable, but the variable is 'empty' - it doesn't hold anything. When you initialize the variable with New it instanciates an object and assigns it to the variable - now the variable 'holds' the object.

  4. #4
    Join Date
    Oct 2018
    Posts
    6

    Re: Dim statement not working in vb.net button code

    Thanks for your quick replies.

    I have tried the following but I still get errors on every line.

    *********
    Dim strFileName As string
    strFileName = txtFileName.Text

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

    xlApp = New Excel.ApplicationClass;
    xlWorkBook = xlApp.Workbooks.Open(strFileName);
    xlWorkSheet = xlWorkBook.Worksheets("sheet1");

    //display the cells value N20
    MsgBox(xlWorkSheet.Cells(19, 20).value);

    //edit the cell with new value
    //xlWorkSheet.Cells(2, 2) = "";
    xlWorkBook.Close();
    xlApp.Quit();

    **********

    I have the following references:

    ExcelAddin_PHLib (just a shot in the dark)
    Microsoft Excel 16.0 Object Library
    Microsoft Office 15.0 Object Library
    Microsoft Office 16.0 Object Library

    Not even the string declaration works!


    Thanks again for any help you can give me.

  5. #5
    DataMiser is offline Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,291

    Re: Dim statement not working in vb.net button code

    Are you really using VB.Net as the thread title indicates or are you using C#?
    If you are really using VB.Net then you have lots of issues going on with that code.

    Edit to add:
    If you are using C# then your dim statements are all wrong as they look like VB Dim statements and not C#
    If you are using VB.Net then everything but your dim statements are wrong as the code all looks like C# and not VB

    It looks like you have pasted C# code and are trying to write VB code within and of course that will not work in VB or C#
    Last edited by DataMiser; October 22nd, 2018 at 05:07 PM.
    Always use [code][/code] tags when posting code.

  6. #6
    Join Date
    Oct 2018
    Posts
    6

    Re: Dim statement not working in vb.net button code

    Thanks for your input.
    I have never used C# so I have no idea what in my code looks like C#. I opened a vb.net forms project. I know all of the open dialog button's code works fine ... is this what looks like C#? In the process of trying to figure this app out I have been googling vb.net info. I come from old school Visual Basic so this is all a big learning curve for me. Any idea how I ended up with such a mess? Any idea how I can fix it? Why would vb code not work in a vb forms project? And how on earth would C# work? Sample code fixes would really help.

    Thanks again for your time and help.

  7. #7
    Join Date
    Sep 2018
    Posts
    38

    Re: Dim statement not working in vb.net button code

    Quote Originally Posted by Valeriegn View Post
    Thanks for your quick replies.

    I have tried the following but I still get errors on every line.

    *********
    Dim strFileName As string
    strFileName = txtFileName.Text

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

    xlApp = New Excel.ApplicationClass;
    xlWorkBook = xlApp.Workbooks.Open(strFileName);
    xlWorkSheet = xlWorkBook.Worksheets("sheet1");

    //display the cells value N20
    MsgBox(xlWorkSheet.Cells(19, 20).value);

    //edit the cell with new value
    //xlWorkSheet.Cells(2, 2) = "";
    xlWorkBook.Close();
    xlApp.Quit();

    **********

    I have the following references:

    ExcelAddin_PHLib (just a shot in the dark)
    Microsoft Excel 16.0 Object Library
    Microsoft Office 15.0 Object Library
    Microsoft Office 16.0 Object Library

    Not even the string declaration works!


    Thanks again for any help you can give me.
    Your MsgBox() line is C# Valeriegn, as well as your use of the semi-colons in your Dim statements. Your xlWorkSheet.Cells(2, 2) may need to be xlWorkSheet.Cells(2, 2).value. I'm not familiar with how to implement libraries, but I'd be sure those are included correctly, as well as any necessary imported namespaces. The only other suggestion I might make is to change your Dim declaration if you're using VB.Net:

    Code:
    Dim xlWorkBook As Excel.Workbook = New Excel.Workbook(strFileName)
    Dim xlWorkSheet As Excel.Worksheet = New Excel.Worksheet("sheet1")
    
    'then you'd use...
    
    xlWorkBook.Open()
    xlWorkBook.Close()
    Don't forget you're combining the end statement semi-colons which isn't VB.Net. It really is a mix you've got there Valeriegn. Just needs some cleaning up. Those are my humble suggestions The other moderators are much more schooled in library use than I am.

  8. #8
    DataMiser is offline Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,291

    Re: Dim statement not working in vb.net button code

    Quote Originally Posted by Valeriegn View Post
    Thanks for your input.
    I have never used C# so I have no idea what in my code looks like C#. I opened a vb.net forms project. I know all of the open dialog button's code works fine ... is this what looks like C#? In the process of trying to figure this app out I have been googling vb.net info. I come from old school Visual Basic so this is all a big learning curve for me. Any idea how I ended up with such a mess? Any idea how I can fix it? Why would vb code not work in a vb forms project? And how on earth would C# work? Sample code fixes would really help.

    Thanks again for your time and help.
    Everything in your original post looks like C# with the exception of the Dim statements you have added to it.
    My guess is that when you open the project it is opening in C# not in VB and is therefore telling you all those Dim statements are in error. If you actually open it in VB it would be complaining about everything else.

    For example C#
    Code:
    using System;
    Same but in VB
    Code:
    Imports System
    Your event in C#
    Code:
     public void button1_Click(object sender, EventArgs e)
            {           
                openFileDialog1.Title = "Please Choose File to Open And Calculate";
                openFileDialog1.FileName = "";
                openFileDialog1.Filter = "Excel Worksheets|*.xls";
                openFileDialog1.ShowDialog();
                txtFileName.Text = openFileDialog1.FileName;
            }
    Same event but in VB
    Code:
    Public Sub button1_Click(ByVal sender As Object, ByVal e As EventArgs)
        openFileDialog1.Title = "Please Choose File to Open And Calculate"
        openFileDialog1.FileName = ""
        openFileDialog1.Filter = "Excel Worksheets|*.xls"
        openFileDialog1.ShowDialog()
        txtFileName.Text = openFileDialog1.FileName
    End Sub
    Below is a link to a C# to VB convertor, you may find useful

    http://converter.telerik.com/
    Last edited by DataMiser; October 23rd, 2018 at 09:58 AM.
    Always use [code][/code] tags when posting code.

  9. #9
    DataMiser is offline Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,291

    Re: Dim statement not working in vb.net button code

    btw to put the dim statements into that C# code that you already have they would look more like this
    Code:
        string strFileName;
        strFileName = txtFileName.Text;
    
        Excel.Application xlApp = new Excel.Application();
        Excel.Workbook xlWorkBook = new Excel.Workbook();
        Excel.Worksheet xlWorkSheet = new Excel.Worksheet();
    Always use [code][/code] tags when posting code.

  10. #10
    Arjay's Avatar
    Arjay is offline Moderator / MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    12,808

    Re: Dim statement not working in vb.net button code

    Oops, I totally missed the combination of C# and VB.Net code in the original post. Clearly adding VB.Net to a C# project isn't going to work at all.

  11. #11
    Join Date
    Oct 2018
    Posts
    6

    Re: Dim statement not working in vb.net button code

    Thanks! I figured out that I was infact using C# ... Now I have actually opened a vb.net forms app. I fixed the OpenDialog code and it works fine. However the code for opening the excel file is still not working (I did get rid of all the semi-colons.

    The only code that is giving me errors is the following:

    Code:
    Dim xlApp As New Excel.Application
            Dim xlWorkBook As New Excel.Workbook
            Dim xlWorkSheet As New Excel.Worksheet
    
            xlApp = New Excel.ApplicationClass
    I did add the excel reference.

    Any ideas?

  12. #12
    DataMiser is offline Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,291

    Re: Dim statement not working in vb.net button code

    I don't work with Excel so can't be of much help there.
    Maybe someone else will chime in.
    Always use [code][/code] tags when posting code.

  13. #13
    2kaud's Avatar
    2kaud is offline Super Moderator Power Poster
    Join Date
    Dec 2012
    Location
    England
    Posts
    6,487

    Re: Dim statement not working in vb.net button code

    Try asking on the the associated VBforums (next item down from the vb.net forum). They are much more active for VB issues.
    All advice is offered in good faith only. All my code is tested (unless stated explicitly otherwise) with the latest version of Microsoft Visual Studio (using the supported features of the latest standard) and is offered as examples only - not as production quality. I cannot offer advice regarding any other c/c++ compiler/IDE or incompatibilities with VS. You are ultimately responsible for the effects of your programs and the integrity of the machines they run on. Anything I post, code snippets, advice, etc is licensed as Public Domain https://creativecommons.org/publicdomain/zero/1.0/ and can be used without reference or acknowledgement. Also note that I only provide advice and guidance via the forums - and not via private messages!

    C++17 Compiler: Microsoft VS2017 (15.8.9)

  14. #14
    Arjay's Avatar
    Arjay is offline Moderator / MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    12,808

    Re: Dim statement not working in vb.net button code

    Quote Originally Posted by Valeriegn View Post
    Thanks! I figured out that I was infact using C# ... Now I have actually opened a vb.net forms app. I fixed the OpenDialog code and it works fine. However the code for opening the excel file is still not working (I did get rid of all the semi-colons.

    The only code that is giving me errors is the following:

    Code:
    Dim xlApp As New Excel.Application
            Dim xlWorkBook As New Excel.Workbook
            Dim xlWorkSheet As New Excel.Worksheet
    
            xlApp = New Excel.ApplicationClass
    I did add the excel reference.

    Any ideas?
    This is a pretty common problem. Have you tried searching google for "Excel sample vb.net"? The first response shows you how to do it. One difference in your code is you are newing up an Excel.ApplicationClass whereas the sample code news up Excel.Application.

    https://www.tutorialspoint.com/vb.ne...xcel_sheet.htm

  15. #15
    DataMiser is offline Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,291

    Re: Dim statement not working in vb.net button code

    I did not notice the mis match in the dim and the assignment but yes that would likely pose a problem. I would expect to see something like a type mismatch error.
    Always use [code][/code] tags when posting code.

Posting Permissions

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


Windows Mobile Development Center


Click Here to Expand Forum to Full Width




On-Demand Webinars (sponsored)