?
Solved

Appending existing excel worksheet through vb.net

Posted on 2004-11-29
10
Medium Priority
?
1,313 Views
Last Modified: 2011-09-20
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
0
Comment
Question by:lkingpinl
  • 5
  • 4
10 Comments
 
LVL 7

Expert Comment

by:Nievergelt
ID: 12698611
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
 
LVL 2

Author Comment

by:lkingpinl
ID: 12700780
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
 
LVL 7

Expert Comment

by:Nievergelt
ID: 12704169
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Author Comment

by:lkingpinl
ID: 12705452
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
 
LVL 2

Author Comment

by:lkingpinl
ID: 12707124
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
 
LVL 7

Expert Comment

by:Nievergelt
ID: 12707212
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
 
LVL 4

Expert Comment

by:abbdan
ID: 12707386
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
 
LVL 7

Expert Comment

by:Nievergelt
ID: 12707451
You get ########, if the cell cannot display the value.
Set a date format for column 1 and make it wide enough.
0
 
LVL 2

Author Comment

by:lkingpinl
ID: 12707532
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
 
LVL 7

Accepted Solution

by:
Nievergelt earned 500 total points
ID: 12707865
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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is about my first experience with programming Arduino.
This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
Progress
Introduction to Processes

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question