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
LVL 2
lkingpinlAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NievergeltSenior SW DevCommented:
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.
0
lkingpinlAuthor Commented:
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)
0
NievergeltSenior SW DevCommented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

lkingpinlAuthor Commented:
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
0
lkingpinlAuthor Commented:
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
0
NievergeltSenior SW DevCommented:
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
0
abbdanCommented:
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.
0
NievergeltSenior SW DevCommented:
You get ########, if the cell cannot display the value.
Set a date format for column 1 and make it wide enough.
0
lkingpinlAuthor Commented:
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.  
0
NievergeltSenior SW DevCommented:
You can either set a format for the whole column; that's the easy way.

If you want to make sure of the format in the macro, you can set it there like:
  objBook.Sheets("RECEIVED FILES").Cells(newRow, 1).NumberFormat = "mm/dd/yyyy"

To get these command easiest this way:
- Record a macro
- Apply the format you want.
- Stop recording.
- Copy the commands from the produced macro.
- Replace 'Selection' with 'objBook.Sheets("RECEIVED FILES").Cells(newRow, 1)' or whatever.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.