Beginner stuck write to excel

Fran24

New member
Joined
Nov 15, 2019
Messages
3
Programming Experience
Beginner
Hi everybody I’m new to the forum iv been on many forums throughout my life but never a programming one. I have went back to college to become an engineer and I am stuck on an assignment I have to hand in. I am trying to read data from an excel file and write/ delete data in the file also I am caught in writing part and don’t know what to do would really appreciate some help. This is what I have so far it just won’t write to the file. Thanks in advance
VB.NET:
Imports Microsoft.Office.Interop
Public Class frmThermistors
   Dim xl As Excel.Application
   Dim wb As Excel.Workbook
   Dim ws As Excel.Worksheet
   Dim i As Integer, j As Integer
   Dim Sensor = New Object()
   Dim NumRows As Integer
   Dim numcolumns As Integer
   Dim Firstline As String
   Dim FirstLine_new As String
   Dim firstLine2 As String
   Dim Password As String

   Private Sub btnOpen_Click(sender As Object, e AsEventArgs) Handles btnOpen.Click
       If ofd1.ShowDialog() <> Windows.Forms.DialogResult.Cancel Then
           xl = New Excel.Application
           wb = xl.Workbooks.Open(ofd1.FileName)
           ws = wb.Worksheets(1)
           ws.Activate()
       End If
       NumRows = ws.UsedRange.Rows.Count
       For i = 3 To NumRows
           NumRows = i + 1
           Firstline = ws.Cells(i, 1).value
           lsboxSensors.Items.Add(Firstline & vbCrLf)
           'opens file and write data into the listbox
       Next
   End Sub

   Private Sub btnQuit_Click(sender As Object, e As EventArgs) Handles btnQuit.Click
       If MsgBox("Do you want to save changes and exit?", MsgBoxStyle.YesNo, MsgBoxStyle.DefaultButton2) = MsgBoxResult.Ok Then
           wb.Save()
           wb.Close(False)
           xl = Nothing

           Me.Dispose()
       End If
       'does not dispose form for some reason
   End Sub

   Private Sub btnTag_Click(sender As Object, e As EventArgs) Handles btnTag.Click
       lblK0.Text = ws.Cells((lsboxSensors.SelectedIndex + 3), 2).value
       lblK1.Text = ws.Cells(lsboxSensors.SelectedIndex + 3, 3).value
       lblK2.Text = ws.Cells(lsboxSensors.SelectedIndex + 3, 4).value
       lblDate.Text = ws.Cells(lsboxSensors.SelectedIndex + 3, 5).value
       'writes valuse of K0,K1,K2 and a date into labels
   End Sub

   Private Sub btnWrite_Click(sender As Object, e AsEventArgs) Handles btnWrite.Click
       'all this code just doesn't work- doesn't write new data into the file...it  _
       'should be implementet also if tag number Is already in the file you cant add it
       'but if you want to change tag that is already in the file, you should be able to do it
       Try
           Password = InputBox("Please enter the password(Excel)")
           numNewTag.Value = Format(numNewTag.Value, "000")
           NumRows = ws.UsedRange.Rows.Count

           For j = 3 To NumRows
               FirstLine_new = ws.Cells(lstNewTag.Text & j, 1).value
               lsboxSensors.Items.Add(FirstLine_new & vbCrLf)
               ' firstLine2 = FirstLine_new.Substring(7, 3)
           Next
           If Password = "Excel" Then
               ws.Cells(NumRows + 1, 1).value = lstNewTag.Text & numNewTag.Value
               ws.Cells(NumRows + 1, 2).value = numK0New.Value
               ws.Cells(NumRows + 1, 3).value = numK1New.Value
               ws.Cells(NumRows + 1, 4).value = numK2New.Value
               ws.Cells(NumRows + 1, 5).value = numDay.Value & "/" & numMonth.Value & "/" & numYear.Value
           Else
               MsgBox("Please,enter correct password!!!", MsgBoxStyle.OkOnly)
           End If
           'End If
       Catch ex As Exception
           MsgBox("The following error occured: 'Specified argument was out of the range of valid values.", MsgBoxStyle.OkOnly)
       End Try
   End Sub

   Private Sub btnDelete_Click(sender As Object, e AsEventArgs) Handles btnDelete.Click
       Password = InputBox("Please enter the password(Excel)")
       If Password = "Excel" And MsgBox("Are you sure you want to delete data?", MsgBoxStyle.YesNoCancel,
                                        MsgBoxStyle.DefaultButton3) = MsgBoxResult.Ok Then
           ws.Cells(lsboxSensors.SelectedIndex + 3, 1).delete()
           ws.Cells(lsboxSensors.SelectedIndex + 3, 2).delete()
           ws.Cells(lsboxSensors.SelectedIndex + 3, 3).delete()
           ws.Cells(lsboxSensors.SelectedIndex + 3, 4).delete()
           ws.Cells(lsboxSensors.SelectedIndex + 3, 5).delete()
       End If
       'does not delete dont know why
   End Sub

   Private Sub ColorToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles ColorToolStripMenuItem.Click
       ColorDialog1.ShowDialog()
       Me.BackColor = ColorDialog1.Color
   End Sub
   'changes backcolor
End Class
 
Last edited by a moderator:
Just dumping a wad of code and expecting us to trawl through it in the hope of finding some nebulous issue is not the best way to go about things. Some people will take the time but I am certainly not one of them. If you've written that code then you obviously have a pretty good idea of what it's supposed to do. If you're having issues then you should have debugged the code, i.e. set a breakpoint at an appropriate place and then stepped through the code while examining the state. If you have done that then you should know exactly where and how the actual behaviour of the code differs from your expectations, and you should be able to point that out to us. You should also be able to post just the section of the code that is relevant to problem.

One problem that a lot of people have when starting out is that they try to approach a problem as a monolith. They think that they have to write all the code for the whole thing in one go and they end up not being about to isolate an issue because they have multiple issues interacting in complex ways. Programming problems should be approached just like any complex problem: divide and conquer. You start by breaking the problem down into parts and steps and keep breaking it down until each part and step is as simple as it can be. You then tackle each part in isolation and one step at a time. That way, whenever your code behaves in an unexpected way, you know EXACTLY where the issue is because it is in the last step of the current part. You can then give us a precise description of the problem with the minimum code.
 
Sorry i wouldnt expect you to go trawling through it. i just thought to put it all up just incase you needed to see the full code to help with the problem. below is the piece of code that im having trouble with. i have stepped through but cant seem to figure it out.

VB.NET:
        Private Sub btnWrite_Click(sender As Object, e As EventArgs) Handles btnWrite.Click
            'all this code just doesn't work- doesn't write new data into the file...it  _
            'should be implementet also if tag number Is already in the file you cant add it
            'but if you want to change tag that is already in the file, you should be able to do it
            Try
                Password = InputBox("Please enter the password(Excel)")
            numEdTag.Value = Format(numEdTag.Value, "000")


            NumRows = ws.UsedRange.Rows.Count


                For j = 3 To NumRows
                FirstLine_new = ws.Cells(lstBox2.Text & j, 1).value
                lstBox1.Items.Add(FirstLine_new & vbCrLf)
                ' firstLine2 = FirstLine_new.Substring(7, 3)

            Next
                If Password = "Excel" Then
                ws.Cells(NumRows + 1, 1).value = lstBox2.Text & numEdTag.Value
                ws.Cells(NumRows + 1, 2).value = numEdK0.Value
                ws.Cells(NumRows + 1, 3).value = numEdK1.Value
                ws.Cells(NumRows + 1, 4).value = numEdK2.Value
                ws.Cells(NumRows + 1, 5).value = numEdDay.Value & "/" & numEdMonth.Value & "/" & numEdYear.Value
            Else
                    MsgBox("Please,enter correct password!!!", MsgBoxStyle.OkOnly)
                End If
                'End If
            Catch ex As Exception
                MsgBox("The following error occured: 'Specified argument was out of the range of valid values.", MsgBoxStyle.OkOnly)
            End Try
        End Sub
 
Last edited by a moderator:
insertcode.png
 
i have stepped through but cant seem to figure it out.
Figure what out? What line or lines don't do what you expect them to? What do you expect that line or lines to do and what do they actually do?
 
Back
Top