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:
