asked on

Appending existing excel worksheet through vb.net

Hey, I have an existing spreadsheet that we currently update manually. I'd like to write a program (VB.Net) that will update that spreadsheet automatically by a user selecting a few things from a list box and adding some text in textboxes.

My question is: How do I correlate certain objects to the next available columns/rows? Like right now we are on Row 185 and use through Column K for data. If someone inputs their stuff in my program, how can I be sure that it is going to the next available row and each object is going to the column it is supposed to. I'm new to all this....

Thanks

Nievergelt

Using VBA, you can query Excel:

The property ActiveSheet.UsedRange.Rows.Count will give you the number of rows currently used. You can then set the values of of the worksheet cells (e.g. ActiveSheet.Cells(lastRow+1, col).Value = newValue).

The only thing to be aware of is that Excel set UsedRange.Rows also if you have at any time used a cell (which may be empty now).
Use Ctrl-End to find right-bottom-most cell of UsedRange.
Select the empty rows at the bottom of the worksheet an Delete them.
Make sure your users are aware of the problem; it is best, if they always use your program.

lkingpinl

ASKER

Perhaps I should clarify. i need some source code.

in my form, I have a date/time picker, 3 listboxes and 4 textboxes. they correlate to:

Date/time picker to data in column A, row (next available row)

listbox 1 to column e, row (next available)
textbox 1 - col f, row (next)
textbox 2 - col g, row (next)
listbox 2 - col h, row (next)
textbox 3 - col i, row (next)
listbox 3 - col j, row (next)
textbox 4 - col k, row (next)

Nievergelt

Private Sub cmdOK_Click()
Dim newRow As Integer

newRow = ActiveSheet.UsedRange.Rows.Count + 1
Sheets("Sheet Name").Cells(newRow, 1) = Me.DatePicker
Sheets("Sheet Name").Cells(newRow, 5) = Me.ListBox1
Sheets("Sheet Name").Cells(newRow, 6) = Me.TextBox1
Sheets("Sheet Name").Cells(newRow, 7) = Me.TextBox2
Sheets("Sheet Name").Cells(newRow, 8) = Me.ListBox2
Sheets("Sheet Name").Cells(newRow, 9) = Me.TextBox3
Sheets("Sheet Name").Cells(newRow, 10) = Me.ListBox3
Sheets("Sheet Name").Cells(newRow, 11) = Me.TextBox4
Unload Me
End Sub

lkingpinl

ASKER

What code goes before that? I can open the excel workbook, but I'm getting "ActiveSheet" and "Sheets" are not delared. Here's a snip of my code:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
objApp = New Excel.Application
objApp.Visible = True
objBook = objApp.Workbooks.Open("K:\RT_DCX\Receivefiles2004.xls")

Dim newRow As Integer

newRow = ActiveSheet.UsedRange.Rows.Count + 1
Sheets("Sheet Name").Cells(newRow, 1) = Me.DateTimePicker1
Sheets("Sheet Name").Cells(newRow, 5) = Me.ListBox1
Sheets("Sheet Name").Cells(newRow, 6) = Me.TextBox1
Sheets("Sheet Name").Cells(newRow, 7) = Me.TextBox2
Sheets("Sheet Name").Cells(newRow, 8) = Me.ListBox2
Sheets("Sheet Name").Cells(newRow, 9) = Me.TextBox3
Sheets("Sheet Name").Cells(newRow, 10) = Me.ListBox3
Sheets("Sheet Name").Cells(newRow, 11) = Me.TextBox4
Unload(Me)

End Sub

lkingpinl

ASKER

Here's my updated code.

It works, sort of. It adds the data now, but not to the next available cells. My current data only goes to row 184, and it puts the data at Row 256. I get out and do it again and it puts in row 257. Why is that? Also the Date/Time Picker shows up as ######## in my cell. I want just the date to show.

Here's a snip of my code:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
objApp = New Excel.Application
objApp.Visible = False
objBook = objApp.Workbooks.Open("K:\RT_DCX\Receivefiles2004_1.xls", Readonly:=False)

Dim newRow As Integer

newRow = objBook.ActiveSheet.UsedRange.Rows.Count + 1
objBook.Sheets("RECEIVED FILES").Cells(newRow, 1) = DateTimePicker1.Value
objBook.Sheets("RECEIVED FILES").Cells(newRow, 5) = strMethod
objBook.Sheets("RECEIVED FILES").Cells(newRow, 6) = TextBox1.Text
objBook.Sheets("RECEIVED FILES").Cells(newRow, 7) = TextBox2.Text
objBook.Sheets("RECEIVED FILES").Cells(newRow, 8) = strYourName
objBook.Sheets("RECEIVED FILES").Cells(newRow, 9) = TextBox3.Text
objBook.Sheets("RECEIVED FILES").Cells(newRow, 10) = strLoc
objBook.Sheets("RECEIVED FILES").Cells(newRow, 11) = TextBox4.Text

objBook.SaveAs(Filename:="K:\RT_DCX\Receivefiles2004_1.xls")
objBook.Close()
objApp.Quit()

End Sub

Nievergelt

Please look again at my first comment.

Mark rows 184 to 255, use menu Edit/Delete, save and close the workbook.
Open it again. Press Ctrl-End.

After that, it should work as requested.

Hope that helps Christoph

abbdan

A simple low programatic solution would be to use an Access database to link to the Excel spreadsheet with a table containing named columns for the data you will be inputing into the Excel file. Use VB.net to append data to the Access database/Excell link. This should guarentee that the next line of data will go to the correct location.

Hope this helps.

Nievergelt

You get ########, if the cell cannot display the value.
Set a date format for column 1 and make it wide enough.

lkingpinl

ASKER

Yes, it works great now, but how can I change the formatting of the cells in the next available row to match that of the previous rows?

For example, right now my formatting is as such:

All Columns with full borders and white fill.

**Special Formatting**
-----------------------------
Column A : mm/dd/yyyy
Column H: Centered
Column J: Text Blue and Underlined

I need the new rows to be of that format.

ASKER CERTIFIED SOLUTION

Nievergelt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial