-
Nov 5th, 2012, 06:54 AM
#1
Thread Starter
New Member
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.
Thanks in advance
Zoran
-
Nov 5th, 2012, 07:10 AM
#2
Hyperactive Member
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.
-
Nov 5th, 2012, 07:24 AM
#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
-
Nov 5th, 2012, 09:40 AM
#4
Thread Starter
New Member
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?
Thanks in advance
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
Last edited by zoomtronic; Nov 5th, 2012 at 11:49 AM.
-
Nov 5th, 2012, 09:46 AM
#5
Thread Starter
New Member
Re: entering data in excel sheet trough Vb application form
Last edited by zoomtronic; Nov 5th, 2012 at 11:11 AM.
-
Nov 5th, 2012, 11:34 AM
#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?
Thanks in advance
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.
-
Nov 5th, 2012, 12:13 PM
#7
Thread Starter
New Member
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
-
Nov 5th, 2012, 12:40 PM
#8
Thread Starter
New Member
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
"
-
Nov 5th, 2012, 01:16 PM
#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
-
Nov 5th, 2012, 01:59 PM
#10
Thread Starter
New Member
Re: entering data in excel sheet trough Vb application form
i did all that, here is screen shoot of references window
-
Nov 5th, 2012, 02:06 PM
#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.
-
Nov 5th, 2012, 02:16 PM
#12
Thread Starter
New Member
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
-
Nov 5th, 2012, 02:20 PM
#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.
-
Nov 5th, 2012, 02:21 PM
#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.
-
Nov 5th, 2012, 03:44 PM
#15
Thread Starter
New Member
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
'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
Last edited by zoomtronic; Nov 5th, 2012 at 04:22 PM.
-
Nov 6th, 2012, 08:46 AM
#16
Thread Starter
New Member
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?
-
Nov 6th, 2012, 12:47 PM
#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")
-
Nov 9th, 2012, 06:39 AM
#18
Thread Starter
New Member
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
'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
-
Nov 9th, 2012, 10:43 AM
#19
Re: entering data in excel sheet trough Vb application form
Good to hear you have finalized the project.
-
Sep 2nd, 2013, 04:49 AM
#20
New Member
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.
-
Sep 3rd, 2013, 09:00 AM
#21
Thread Starter
New Member
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.
-
Sep 3rd, 2013, 09:15 AM
#22
New Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|