Issues Writing to *.xlsx from VB.Net Forms Application

Class Project and I think I may have gotten in a little over my head w

Page 1 of 1

10 Replies - 5414 Views - Last Post: 16 January 2010 - 11:12 AM Rate Topic: -----

#1 YoungPup  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 11-December 09

Issues Writing to *.xlsx from VB.Net Forms Application

Post icon  Posted 17 December 2009 - 07:38 PM

Evening ladies and gents,
I'm working on a class project for a distance learning course in VB.NET and have run into an issue with my idea for a program. My plan is to use a 4 form application to add, search, and delete entries corresponding to a movie inventory without touching the data repository (excel spreadsheet) at all. I've gotten to the point where I can open the program and it will open the already created spreadsheet, but even after searching for what seems like hours I can not find a code to use as a jumping off point for getting the text boxes within frmAdd, frmSearch, and frmDelete to write to the spreadsheet, much less be able to find the next open row and write to that. Below is what I have thus far:

Public Class frmMain

	Dim frmAdd As New frmAdd()
	Dim frmSearch As New frmSearch()
	Dim frmDelete As New frmDelete()
	Dim ApExcel As Object
	Dim ApBook As Object
	Dim ApSheet As Object

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

		'Initializes Excel Application
		ApExcel = CreateObject("Excel.Application")

		'Keeps spreadsheet invisible to user
		ApExcel.Visible = False

		'Opens specified workbook
		ApExcel.Workbooks.Open("C:\movies.xlsx")

	End Sub
	Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click

		frmAdd.Show()

	End Sub

	Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click

		frmSearch.Show()

	End Sub

	Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click

		frmDelete.Show()

	End Sub
End Class

Public Class frmAdd

	'Hides Add form
	Private Sub btnFinish_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFinish.Click

		Me.Hide()

	End Sub

	'Clears form
	Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClear.Click

		If txtTitle.Text IsNot Nothing Then
			txtTitle.Text = Nothing
		End If

		If txtGenre.Text IsNot Nothing Then
			txtGenre.Text = Nothing
		End If

		If txtRelease.Text IsNot Nothing Then
			txtRelease.Text = Nothing
		End If

	End Sub
End Class

Public Class frmSearch

	'Hides Search form
	Private Sub btnFinish_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFinish.Click

		Me.Hide()

	End Sub

	'Clears form
	Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClear.Click

		If txtTitle.Text IsNot Nothing Then
			txtTitle.Text = Nothing
		End If

		If txtGenre.Text IsNot Nothing Then
			txtGenre.Text = Nothing
		End If

		If txtRelease.Text IsNot Nothing Then
			txtRelease.Text = Nothing
		End If

	End Sub
End Class

Public Class frmDelete

	'Hides Delete form
	Private Sub btnFinish_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFinish.Click

		Me.Hide()

	End Sub

	'Clears form
	Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClear.Click

		If txtTitle.Text IsNot Nothing Then
			txtTitle.Text = Nothing
		End If

		If txtGenre.Text IsNot Nothing Then
			txtGenre.Text = Nothing
		End If

		If txtRelease.Text IsNot Nothing Then
			txtRelease.Text = Nothing
		End If

	End Sub
End Class


Any help would be greatly appreciated and, again, I'm not looking for anyone to do this for me. Just need a good jumping off point. Thanks in advance!

This post has been edited by YoungPup: 17 December 2009 - 07:39 PM


Is This A Good Question/Topic? 0
  • +

Replies To: Issues Writing to *.xlsx from VB.Net Forms Application

#2 Sethro117  Icon User is offline

  • Still the sexiest mofo.
  • member icon

Reputation: 236
  • View blog
  • Posts: 2,378
  • Joined: 14-January 09

Re: Issues Writing to *.xlsx from VB.Net Forms Application

Posted 17 December 2009 - 08:05 PM

Why are you using Excel? If you still have time, switch to Access.
Was This Post Helpful? 0
  • +
  • -

#3 YoungPup  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 11-December 09

Re: Issues Writing to *.xlsx from VB.Net Forms Application

Posted 17 December 2009 - 08:08 PM

View PostSethro117, on 17 Dec, 2009 - 07:05 PM, said:

Why are you using Excel? If you still have time, switch to Access.


I considered access, but I've done work with vb and access before so I'm wanting to see how easily vb.net will work with other programs within the office suite. That and every program I've seen to manipulate data has done so with a database. It's more a curiosity thing than anything else.
Was This Post Helpful? 0
  • +
  • -

#4 Bort  Icon User is offline

  • Ill-informed Mongoloid
  • member icon

Reputation: 396
  • View blog
  • Posts: 2,919
  • Joined: 18-September 06

Re: Issues Writing to *.xlsx from VB.Net Forms Application

Posted 18 December 2009 - 03:22 AM

Hi YoungPup,

Welcome to Dic :)

Take a look at my Office Automation Tutorial. That should help you get started inporting data to Excel.

HTH,
Bort
Was This Post Helpful? 0
  • +
  • -

#5 YoungPup  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 11-December 09

Re: Issues Writing to *.xlsx from VB.Net Forms Application

Posted 18 December 2009 - 04:56 PM

View PostBort, on 18 Dec, 2009 - 02:22 AM, said:

Hi YoungPup,

Welcome to Dic :)

Take a look at my Office Automation Tutorial. That should help you get started inporting data to Excel.

HTH,
Bort


Your tutorial helped alot with the interface between my vb front end and excel, however, now I'm running into an issue where I'm trying to get the front end to find the next empty cell so that when my users input a new list item it won't override anything that is currently in the spreadsheet. Any ideas?
Was This Post Helpful? 0
  • +
  • -

#6 T3hC13h  Icon User is offline

  • D.I.C Regular

Reputation: 65
  • View blog
  • Posts: 337
  • Joined: 05-February 08

Re: Issues Writing to *.xlsx from VB.Net Forms Application

Posted 19 December 2009 - 03:39 PM

Just loop through the Range of cells and check the Value property to see if its empty.
Was This Post Helpful? 0
  • +
  • -

#7 detlion1643  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 37
  • Joined: 04-November 08

Re: Issues Writing to *.xlsx from VB.Net Forms Application

Posted 20 December 2009 - 09:20 AM

i've done a ton of programming in VBA for excel, taking a break from my normal .net programming. you can always just find the lastrow that has data in, say, column A, and then to add data just use the lastrow + 1.

I'm not sure how to use the code from within access, but it may help as a starting point.
Was This Post Helpful? 0
  • +
  • -

#8 YoungPup  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 11-December 09

Re: Issues Writing to *.xlsx from VB.Net Forms Application

Posted 08 January 2010 - 06:48 AM

View Postdetlion1643, on 20 Dec, 2009 - 08:20 AM, said:

i've done a ton of programming in VBA for excel, taking a break from my normal .net programming. you can always just find the lastrow that has data in, say, column A, and then to add data just use the lastrow + 1.

I'm not sure how to use the code from within access, but it may help as a starting point.


In order to loop a search for Value = Nothing for the next row, would I need to set each cell that I want to write to as a variable? (i.e. columnA as String) or is VB.NET compatible enough with excel that there is a family with excel functions already existing? The loop structure I understand, I just still have problems with inputting the correct functions to get my program to look into the spreadsheet as it's data source. I'll post my new code this afternoon when I get home from work so you guys can see how it's going.
Was This Post Helpful? 0
  • +
  • -

#9 YoungPup  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 11-December 09

Re: Issues Writing to *.xlsx from VB.Net Forms Application

Posted 15 January 2010 - 07:22 PM

Alright, as I stated before, and as you can see in my original code I have gotten my forms application to open excel without any issues. However, I am still unable to get excel to select the next empty cell in column a in order to populate a#, b#, or c# with the values of txtTitle, txtGenre, and txtRelease. I'm starting to get very frustrated and the deadline for this is coming up quickly. I've search the net and have found several examples of code, but unfortunately I have not been able to get any of them to work appropriately and, to be honest, most of the code I have found has been in VBA so the functions and interoperability with Excel is vastly different in my eyes. Below is the code I have added to frmAdd in order to get my program to run this function and select the next available cell.

Imports Excel

Public Class frmAdd

	Dim xRow As Integer
	Dim xCol As Integer
	Dim cell As Excel.Range
	

	'Hides Add form
	Private Sub btnFinish_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFinish.Click

		Me.Hide()

	End Sub

	'Clears form
	Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClear.Click

		If txtTitle.Text IsNot Nothing Then
			txtTitle.Text = Nothing
		End If

		If txtGenre.Text IsNot Nothing Then
			txtGenre.Text = Nothing
		End If

		If txtRelease.Text IsNot Nothing Then
			txtRelease.Text = Nothing
		End If

	End Sub

	Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click

		xRow = 1
		xCol = 1
		Do While cell(xRow, xCol) <> ""
			xRow = xRow + 1
		Loop

		cell(xRow, xCol).Value = txtTitle.Text
		cell(xRow, xCol + 1).value = txtGenre.Text
		cell(xRow, xCol + 2).Value = txtRelease.Text
		MsgBox("Information Saved")

	End Sub

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

	End Sub
End Class

Was This Post Helpful? 0
  • +
  • -

#10 YoungPup  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 11-December 09

Re: Issues Writing to *.xlsx from VB.Net Forms Application

Posted 16 January 2010 - 10:39 AM

So I've got rid of my unhandled exception error during initial program start by removing the ApSheet.Activate() line, however now within my loop I received an error at line 41 "xRow = rCount + 1." I'm not sure if this is caused by the loop not running at all or if my designation of cells within excel is incorrect. All I know is I've got until tomorrow night to finish this thing and it is frustrating as all hell. Any help you guys could provide would be greatly appreciated. I think once I get this working the rest of my code will come together easily. It's just this one Excel interop issue that I'm having problems with right now.

	'Loops through all data populated cells within *\movies.xlsx and selects first empty cell for new data entry
	Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click

		xRow = 1
		xCol = 1
		Do While cell(xRow, xCol) <> ""
			xRow = rCount + 1
		Loop

		cell(rCount, xCol).Value = txtTitle.Text
		cell(rCount, xCol + 1).value = txtGenre.Text
		cell(rCount, xCol + 2).Value = txtRelease.Text
		MsgBox("Information Saved")


	End Sub


The error message I get is as follows:

The index is outside the bounds of the array

The first line within the debug is this:

at WindowsApplication1.frmAdd.btnAdd_Click(Object sender, EventArgs e) in C:\Users\Chris\Documents\Visual Studio 2008\Projects\InventoryManagerv1.0\InventoryManagerv1.0\frmAdd.vb:line 41

This points directly at xRow = rCount + 1 as the stopping point of the program. The issue I'm having is that I can't figure out if this is attempting to locate a cell within a vb.net array or if it is actually looking at Excel......gonna go pull the rest of my hair out now.
Was This Post Helpful? 0
  • +
  • -

#11 YoungPup  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 11-December 09

Re: Issues Writing to *.xlsx from VB.Net Forms Application

Posted 16 January 2010 - 11:12 AM

View PostYoungPup, on 16 Jan, 2010 - 09:39 AM, said:

So I've got rid of my unhandled exception error during initial program start by removing the ApSheet.Activate() line, however now within my loop I received an error at line 41 "xRow = rCount + 1." I'm not sure if this is caused by the loop not running at all or if my designation of cells within excel is incorrect. All I know is I've got until tomorrow night to finish this thing and it is frustrating as all hell. Any help you guys could provide would be greatly appreciated. I think once I get this working the rest of my code will come together easily. It's just this one Excel interop issue that I'm having problems with right now.

	'Loops through all data populated cells within *\movies.xlsx and selects first empty cell for new data entry
	Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click

		xRow = 1
		xCol = 1
		Do While cell(xRow, xCol) <> ""
			xRow = rCount + 1
		Loop

		cell(rCount, xCol).Value = txtTitle.Text
		cell(rCount, xCol + 1).value = txtGenre.Text
		cell(rCount, xCol + 2).Value = txtRelease.Text
		MsgBox("Information Saved")


	End Sub


The error message I get is as follows:

The index is outside the bounds of the array

The first line within the debug is this:

at WindowsApplication1.frmAdd.btnAdd_Click(Object sender, EventArgs e) in C:\Users\Chris\Documents\Visual Studio 2008\Projects\InventoryManagerv1.0\InventoryManagerv1.0\frmAdd.vb:line 41

This points directly at xRow = rCount + 1 as the stopping point of the program. The issue I'm having is that I can't figure out if this is attempting to locate a cell within a vb.net array or if it is actually looking at Excel......gonna go pull the rest of my hair out now.


As you can see in my post above, I had xRow and xCol = 1. After reading some in regards to the way arrays work in VB.NET I found that the lowerbound of all arrays is 0 and have since changed those values to match. However, my stack now looks like this

System.NullReferenceException: Object variable or With block variable not set.
at Microsoft.VisualBasic.CompilerServices.Symbols.Container..ctor(Object Instance)
at Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateSet(Object Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean OptimisticSet, Boolean RValueBase, CallType CallType)
at Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateSet(Object Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments)
at WindowsApplication1.frmAdd.btnAdd_Click(Object sender, EventArgs e) in C:\Users\Chris\Documents\Visual Studio 2008\Projects\InventoryManagerv1.0\InventoryManagerv1.0\frmAdd.vb:line 45
at System.Windows.Forms.Control.onclick(EventArgs e)
at System.Windows.Forms.Button.onmouseup(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativewindow.WndProc(Message& m)
at System.Windows.Forms.Nativewindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

Now, Line 45 within my code is set to cell(xRow, xCol+1).Value = txtGenre.Text

Looking at it, it appears to me that this code says exactly what it's supposed to do. Fill next empty cell with offset (0 , 1) with the populated data in txtGenre.Text, but it's not working. It also doesn't fill the data in txtTitle.Text with is line 40 in the code. Completely lost as to where my actual problem is or how to fix it. Help please.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1