VS 2010 entering data in excel sheet trough Vb application form-VBForums

# Thread: entering data in excel sheet trough Vb application form

1. ## entering data in excel sheet trough Vb application form

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.

Zoran

2. ## Re: entering data in excel sheet trough Vb application form

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.

3. ## Re: entering data in excel sheet trough Vb application form

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

4. ## entering data in excel sheet trough Vb application form

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?

Code:
  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

5. ## Re: entering data in excel sheet trough Vb application form

I doubled post...

6. ## Re: entering data in excel sheet trough Vb application form

Originally Posted by 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.
any idea how to read that column and display next empty ID row?

Code:
  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.

7. ## Re: entering data in excel sheet trough Vb application form

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

8. ## Re: entering data in excel sheet trough Vb application form

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
"

9. ## Re: entering data in excel sheet trough Vb application form

Originally Posted by zoomtronic
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

10. ## Re: entering data in excel sheet trough Vb application form

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

11. ## Re: entering data in excel sheet trough Vb application form

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.

12. ## Re: entering data in excel sheet trough Vb application form

Originally Posted by 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.
I use visual studio 2010 Ultimate

13. ## Re: entering data in excel sheet trough Vb application form

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.

14. ## Re: entering data in excel sheet trough Vb application form

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

15. ## Re: entering data in excel sheet trough Vb application form

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

Code:
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

Dim a As String = My.Computer.FileSystem.ReadAllText("\\DVR-ZOKI\Full shared\text1.txt")
Dim b As String() = a.Split(vbNewLine)
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

16. ## Re: entering data in excel sheet trough Vb application form

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

17. ## Re: entering data in excel sheet trough Vb application form

Originally Posted by zoomtronic
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.
Code:
xlRange1.Value = Now.ToString("d")

18. ## Re: entering data in excel sheet trough Vb application form

Originally Posted by kevininstructor
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.
Code:
xlRange1.Value = Now.ToString("d")
Thanks Kevin,
i managed to finalize application, now i ' m posting final code

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
Dim a As String = My.Computer.FileSystem.ReadAllText("C:\text1.txt")
Dim b As String() = a.Split(vbNewLine)
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

19. ## Re: entering data in excel sheet trough Vb application form

Good to hear you have finalized the project.

20. ## Re: entering data in excel sheet trough Vb application form

Dear zoomtronic,

I am attaching here the picture of my your project type, a newly created assumption.

I have fixed the errors which i got at runtime on my behalf, But after all that fixing, I am still not able to understand the use of List Box1.Text that is connected with Label7.Text

So please clear my doubt if possible...

Regards & Thanks.

21. ## Re: entering data in excel sheet trough Vb application form

Originally Posted by shivps

Dear zoomtronic,

I am attaching here the picture of my your project type, a newly created assumption.

I have fixed the errors which i got at runtime on my behalf, But after all that fixing, I am still not able to understand the use of List Box1.Text that is connected with Label7.Text

So please clear my doubt if possible...

Regards & Thanks.
I used Label7.text to read which is the last entered record number, and this number added to one gives me NEXT record number to be written.
My users were writing that number manually, so i had to have hint what comes next, so i did that with label7 reading and displaying suggested number to textbox1.text

i hope that this help.

22. ## Re: entering data in excel sheet trough Vb application form

Originally Posted by zoomtronic
I used Label7.text to read which is the last entered record number, and this number added to one gives me NEXT record number to be written.
My users were writing that number manually, so i had to have hint what comes next, so i did that with label7 reading and displaying suggested number to textbox1.text

i hope that this help.

Actually after posting this post,...When I again researched on your program I got it fixed by thinking somehow,...But anyways....

..........I have an idea, you can use combobox in place of textboxes to make your task more easy & convinient as I did from which you can select or write.

#### Posting Permissions

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

Featured