entering data in excel sheet trough Vb application form

[zoomtronic]

Hi there,
I need to make windows application which has simple form with couple of text boxes and
button save. Application has to enter into excel data cell by cell as (when i fill the
form, and clicking a save button). Excel file has to be stored on the local machine and
problem i encountered is, how to determine which cell is already populated with certain
content, content it self has to be protected when you once enter it trough form. So it
means that You fill up complete form and save it , application saves content and restores
text fields to blank for next data entry. I need a way to detect that cell in excel sheet is
empty, and if is, then to enter new data. In other words i need to prevent previously entered data
not to be overwritten.

Thanks in advance

Zoran

[Vladamir]

There are several different ways of doing this. I don't happen to have the code with me at the moment but just do a search for things like "vb.net next empty cell Excel". There are plenty of examples out there. Another way would be to pick a location in the Excel file you know will never be written to and just keep an key in that field which would tell you where you left off last time you saved data. You will need to increment this field by one each time but that's an easy task. When you start to save new data just read this cell to know where you can start writing new data. But there are loops and other methods for determining when a row is empty thus available for new data.

[kevininstructor]

Hello,

Take a look at my sample at the link below. It does show how to read a single cell as a string (you can easily convert the string to another type i.e. Integer for instance) and check to see if the value is empty. There is a good deal of code that in many cases shows the same thing over and over again showing it works in regards to reading data and a few examples of saving single cell data. If you take time to study the code than you will have a path to get the results you want. If on the other hand you don't take time to study the code than there will be no solution.

Note that most examples use Office automation although there are a few that use OleDb.

http://code.msdn.microsoft.com/Basic...Excel-4453945d

[zoomtronic]

I did following code, ad this is pretty what i need, but i need more control over it,
actually i need ID, i meant to put another text box and to link it with column F for instance,
this will provide me back info about row which application is writing to. Because i don't know which row
is already taken, back info regarding ID is required.
any idea how to read that column and display next empty ID row?

Thanks in advance

  Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorksheet As Excel.Worksheet
        Dim lastRow As Long

        xlApp = New Excel.Application
        xlWorkBook = xlApp.Workbooks.Open("C:\file.xlsx")
        xlWorksheet = xlWorkBook.Worksheets(1)
        xlApp.Visible = False

        lastRow = xlWorksheet.Range("A" & xlApp.Rows.CountLarge).End(Excel.XlDirection.xlUp).Row + 1

        With xlWorksheet
            .Range("A" & lastRow).Value = Me.TextBox1.Text
            .Range("B" & lastRow).Value = Me.TextBox2.Text
            .Range("C" & lastRow).Value = Me.TextBox3.Text
            .Range("D" & lastRow).Value = Me.TextBox4.Text
            .Range("E" & lastRow).Value = Me.TextBox5.Text
            .Range("F" & lastRow).Value = Me.TextBox6.Text
        End With

        xlWorkBook.Save()
        xlWorkBook.Close()

        releaseObject(xlWorkBook)
        releaseObject(xlWorksheet)
        xlApp.Quit()
        releaseObject(xlApp)
    End Sub
    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try

    End Sub



    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

        'clears text boxes
        TextBox1.Text = ""
        TextBox2.Text = ""
        TextBox3.Text = ""
        TextBox4.Text = ""
        TextBox5.Text = ""
        TextBox6.Text = ""
    End Sub

[zoomtronic]

I doubled post...

[kevininstructor]

I did following code, ad this is pretty what i need, but i need more control over it,
actually i need ID, i meant to put another text box and to link it with column F for instance,
this will provide me back info about row which application is writing to.
any idea how to read that column and display next empty ID row?

Thanks in advance

  Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorksheet As Excel.Worksheet
        Dim lastRow As Long

        xlApp = New Excel.Application
        xlWorkBook = xlApp.Workbooks.Open("C:\file.xlsx")
        xlWorksheet = xlWorkBook.Worksheets(1)
        xlApp.Visible = True

        lastRow = xlWorksheet.Range("A" & xlApp.Rows.CountLarge).End(Excel.XlDirection.xlUp).Row + 1

        With xlWorksheet
            .Range("A" & lastRow).Value = Me.Textbox1.Text
            .Range("B" & lastRow).Value = Me.TextBox2.Text
            .Range("C" & lastRow).Value = Me.TextBox3.Text
            .Range("D" & lastRow).Value = Me.TextBox4.Text
        End With

        xlWorkBook.Save()
        xlWorkBook.Close()

        releaseObject(xlWorkBook)
        releaseObject(xlWorksheet)
        xlApp.Quit()
        releaseObject(xlApp)
    End Sub
    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try

I think in this case a combination of Office automation and OleDb would work then. Read the sheet data into a DataTable and bind the Column F to a TextBox. This goes back to my example project on MSDN.

Also I would like to tell you that the code current shown may not release memory properly, best to have Task Manager open to see if memory is released at the end of the sub.

[zoomtronic]

I checked, it releases memory, checked on task manager, I need to make this as simple as possible

[zoomtronic]

regarding Your project i downloaded, i did all in building the sample section, but i have few errors and can not start it

"Error 1 'DataTable' is ambiguous, imported from the namespaces or types 'System.Data, Microsoft.Office.Interop.Excel'. E:\Visual Studio\Basics of using Excel automation in VB.NET with emphasis on creating and destroy\VB\Basics_1\Excel_Modules\OleDbDemos.vb 11 27 Basics_1
Error 2 'Application' is ambiguous, imported from the namespaces or types 'System.Windows.Forms, Microsoft.Office.Interop.Excel'. E:\Visual Studio\Basics of using Excel automation in VB.NET with emphasis on creating and destroy\VB\Basics_1\Excel_Modules\UtilityRoutines.vb 2 54 Basics_1
Error 3 'Application' is ambiguous, imported from the namespaces or types 'System.Windows.Forms, Microsoft.Office.Interop.Excel'. E:\Visual Studio\Basics of using Excel automation in VB.NET with emphasis on creating and destroy\VB\Basics_1\Forms\MainForm.vb 3 54 Basics_1
Error 4 'Application' is ambiguous, imported from the namespaces or types 'System.Windows.Forms, Microsoft.Office.Interop.Excel'. E:\Visual Studio\Basics of using Excel automation in VB.NET with emphasis on creating and destroy\VB\Basics_1\Forms\MainForm.vb 9 9 Basics_1
Error 5 'Rectangle' is ambiguous, imported from the namespaces or types 'System.Drawing, Microsoft.Office.Interop.Excel'. E:\Visual Studio\Basics of using Excel automation in VB.NET with emphasis on creating and destroy\VB\Basics_1\Forms\MainForm.vb 91 21 Basics_1
Error 6 Type 'Drawing.Drawing2D.LinearGradientBrush' is not defined. E:\Visual Studio\Basics of using Excel automation in VB.NET with emphasis on creating and destroy\VB\Basics_1\Forms\MainForm.vb 92 43 Basics_1
Error 7 'Application' is ambiguous, imported from the namespaces or types 'System.Windows.Forms, Microsoft.Office.Interop.Excel'. E:\Visual Studio\Basics of using Excel automation in VB.NET with emphasis on creating and destroy\VB\Basics_1\Forms\Partial\MainFormTimerCode.vb 19 9 Basics_1
"

[kevininstructor]

regarding Your project i downloaded, i did all in building the sample section, but i have few errors and can not start it

Go under project properties, references, the office reference for Excel needs to be removed as it is the incorrect version for your computer. Next add a new reference for Office Excel which will appear as Microsoft.Office.Interop.Excel

[zoomtronic]

i did all that, here is screen shoot of references window

[kevininstructor]

What version of Visual Studio 2010 are you using i.e. Express, Premium etc?
I ask because the project was created with Premium on my Vista box and tested also on an XP box.

[zoomtronic]

What version of Visual Studio 2010 are you using i.e. Express, Premium etc?
I ask because the project was created with Premium on my Vista box and tested also on an XP box.

I use visual studio 2010 Ultimate

[kevininstructor]

Not sure what to say at this point as it should work. There have been several hundred downloads with no one indicating an issue like this.

[kevininstructor]

Are you running on a 64bit OS, if so try compiling the app as 32bit.

[zoomtronic]

Will try that, Yes I am running on x64 OS , definitely will compile on x86 and try it.
Now latest update of my code for writing to a excel file.
I am using text1.text file as help file, in which i am storing last used ID number,
on load i am reading that number and displaying it under listbox1,
few more touches and it will be what i need, but i will try also with OleDb

Public Class Form1

    Private Property newinfo As Object

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        'reads text1.txt file and displays on listbox1 on form load event
        Dim a As String = My.Computer.FileSystem.ReadAllText("\\DVR-ZOKI\Full shared\text1.txt")
        Dim b As String() = a.Split(vbNewLine)
        ListBox1.Items.AddRange(b)
        MsgBox("Last ID Updated")
    End Sub



    



    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorksheet As Excel.Worksheet
        Dim lastRow As Long

        'writes value of textbox1 to a text file
        'Dim objWriter As New System.IO.StreamWriter("c:\text1.txt", False)
        'objWriter.WriteLine(TextBox1.Text)
        'objWriter.Close()
        
        



        'writes value to text1.txt
        Dim FILE_NAME As String = "\\DVR-ZOKI\Full shared\text1.txt"
        If System.IO.File.Exists(FILE_NAME) = True Then
            Dim objWriter As New System.IO.StreamWriter(FILE_NAME)
            objWriter.Write(TextBox1.Text)
            objWriter.Close()
            MsgBox("Upisano i memorisano")
        Else
            MsgBox("Fajl ne postoji")
        End If




        











        xlApp = New Excel.Application
        xlWorkBook = xlApp.Workbooks.Open("\\DVR-ZOKI\Full shared\file.xlsx")
        xlWorksheet = xlWorkBook.Worksheets(1)
        xlApp.Visible = False

        lastRow = xlWorksheet.Range("A" & xlApp.Rows.CountLarge).End(Excel.XlDirection.xlUp).Row + 1

        With xlWorksheet
            .Range("A" & lastRow).Value = Me.TextBox1.Text
            .Range("B" & lastRow).Value = Me.TextBox2.Text
            .Range("C" & lastRow).Value = Me.TextBox3.Text
            .Range("D" & lastRow).Value = Me.TextBox4.Text
            .Range("E" & lastRow).Value = Me.TextBox5.Text
            .Range("F" & lastRow).Value = Me.TextBox6.Text
        End With

        xlWorkBook.Save()
        xlWorkBook.Close()

        releaseObject(xlWorkBook)
        releaseObject(xlWorksheet)
        xlApp.Quit()
        releaseObject(xlApp)

        Label7.Text = TextBox1.Text


        


        
    End Sub
    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try

    End Sub


    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

        'clears text boxes
        TextBox1.Text = ""
        TextBox2.Text = ""
        TextBox3.Text = ""
        TextBox4.Text = ""
        TextBox5.Text = ""
        TextBox6.Text = ""
    End Sub

    
End Class

[zoomtronic]

Now i need to enter date automatically into cell on new data entry,
any idea?

[kevininstructor]

Now i need to enter date automatically into cell on new data entry,
any idea?

Refering to my MSDN code, OpenExcelWriteData in OpenWorkSheets.vb shows how to write data to a cell, in this case strings but you can so this with a Date too i.e.

xlRange1.Value = Now.ToString("d")

[zoomtronic]

Refering to my MSDN code, OpenExcelWriteData in OpenWorkSheets.vb shows how to write data to a cell, in this case strings but you can so this with a Date too i.e.

xlRange1.Value = Now.ToString("d")

Thanks Kevin,
i managed to finalize application, now i ' m posting final code

Imports System.Runtime.InteropServices



Public Class Form1

    Private Property newinfo As Object
    
   

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Label7.Text = ListBox1.Text
        'reads text1.txt file and displays on listbox1 on form load event
        Dim a As String = My.Computer.FileSystem.ReadAllText("C:\text1.txt")
        Dim b As String() = a.Split(vbNewLine)
        ListBox1.Items.AddRange(b)
        MsgBox("Last ID Updated")

        'reading date and wtiting it to textbox2
        Me.TextBox2.Text = Me.DateTimePicker1.Text


        'shows listbox item to a label7
        Dim j As Integer
        Dim i As Integer

        For i = 0 To ListBox1.Items.Count - 1
            j += CInt(ListBox1.Items.Item(i).ToString)
        Next
        Label7.Text = j
        ProgressBar1.Hide()

    End Sub



    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Timer1.Start()
        ProgressBar1.Show()

        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorksheet As Excel.Worksheet
        Dim lastRow As Long
        '-----------------------------------------------------------------------------------------------------------------------------
        'writes value to text1.txt
        Dim FILE_NAME As String = "C:\text1.txt"
        If System.IO.File.Exists(FILE_NAME) = True Then
            Dim objWriter As New System.IO.StreamWriter(FILE_NAME)
            objWriter.Write(TextBox1.Text)
            objWriter.Close()
            ' MsgBox("Upisano i memorisano")
        Else
            MsgBox("Fajl ne postoji")
        End If
        '----------------------------------------------------------------------------------------------------------------------------

        xlApp = New Excel.Application
        xlWorkBook = xlApp.Workbooks.Open("C:\Delovodnik.xlsx", , , , "admin")
        xlWorksheet = xlWorkBook.Worksheets(1)
        xlApp.Visible = False

        lastRow = xlWorksheet.Range("A" & xlApp.Rows.CountLarge).End(Excel.XlDirection.xlUp).Row + 1

        With xlWorksheet
            .Range("A" & lastRow).Value = Me.TextBox1.Text
            .Range("B" & lastRow).Value = Me.TextBox2.Text
            .Range("G" & lastRow).Value = Me.TextBox7.Text
            .Range("D" & lastRow).Value = Me.TextBox4.Text
            .Range("E" & lastRow).Value = Me.TextBox5.Text
            .Range("F" & lastRow).Value = Me.TextBox6.Text
            .Range("C" & lastRow).Value = Me.TextBox3.Text
        End With

        'writes value to the label from textbox1
        Label7.Text = TextBox1.Text
        Me.TextBox2.Text = Me.DateTimePicker1.Text

        xlWorkBook.Save()
        xlWorkBook.Close()

        releaseObject(xlWorkBook)
        releaseObject(xlWorksheet)
        xlApp.Quit()
        releaseObject(xlApp)

    End Sub

    'releases memory
    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try

        Dim MemClass As New clsMemory()
        MemClass = Nothing

    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

        'clears text boxes and writes last number of label7 plus 1
        'also in textbox2 displays date 
        TextBox1.Text = Label7.Text + 1
        TextBox3.Text = ""
        TextBox4.Text = ""
        TextBox5.Text = ""
        TextBox6.Text = ""
        TextBox7.Text = ""
        Me.TextBox2.Text = Me.DateTimePicker1.Text

    End Sub

    'extra memory release
    Public Class clsMemory

        <DllImport("KERNEL32.DLL", EntryPoint:="SetProcessWorkingSetSize", SetLastError:=True, CallingConvention:=CallingConvention.StdCall)> _
        Friend Shared Function SetProcessWorkingSetSize(ByVal pProcess As IntPtr, ByVal dwMinimumWorkingSetSize As Integer, ByVal dwMaximumWorkingSetSize As Integer) As Boolean
        End Function

        <DllImport("KERNEL32.DLL", EntryPoint:="GetCurrentProcess", SetLastError:=True, CallingConvention:=CallingConvention.StdCall)> _
        Friend Shared Function GetCurrentProcess() As IntPtr
        End Function
        Public Sub New()
            Dim pHandle As IntPtr = GetCurrentProcess()
            SetProcessWorkingSetSize(pHandle, -1, -1)
        End Sub
    End Class

    Private Sub DateTimePicker1_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DateTimePicker1.ValueChanged
    End Sub


    'restricts user entry in texbox2 -date
    Private Sub TextBox2_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TextBox2.KeyPress
        e.Handled = True
        If e.KeyChar Like "[]" Then e.Handled = False
    End Sub

    'while click on listbox1 displays value of last entry to label7
    Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged
        Label7.Text = ListBox1.Text
    End Sub

    'progressbar handler  
    Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick
        ProgressBar1.Increment(5)
        If ProgressBar1.Value = ProgressBar1.Maximum Then
            Timer1.Stop()
            ProgressBar1.Hide()
            ' Label8.Text = ProgressBar1.Value & (" %")
            MsgBox("Upisano i memorisano")


        End If

    End Sub
End Class

[kevininstructor]

Good to hear you have finalized the project.