Question Problem involving Excel, data transfer & Positioning

[alex101fishing]

First of all hello to all the members of this forum, I have only just joined and I am hopeful somebody might be able to spend 2 minutes looking at this for me.

I am an electronic engineer, however I have been roped in by a family member to make a little piece of software for a company, that will allow them to enter client information into a form, and have it written into a spreadsheet (for storage and access purposes), this data will then be processed into various reports over time. Sadly my degree did not include any form of VB! And I thought to myself it can't be that hard, and to be honest so far it had gone well. I have managed to make a form with the appropriate drop down boxes, I am able to create a new spreadsheet, format the spread sheet and place values in appointed cells. I have got the program testing the directory where I want the spreadsheets be to be stored and it is even able to test the file names of the spreadsheet to find a date corresponding with a search parameter set by the user.

However here comes my problem, the program needs to be able to add rows of data into a spreadsheet that has already been created, this I have managed to do with the code I have posted below: however I need to find a way of identifying the last row that contains any data within the worksheet, and then dropping the new data into the row after that. Hopefully that makes sense to you! I have been scouring the internet and have found various methods of doing this however none of them seem to work very well.

the method I found that almost seems to work is shown below: However it’s a bit hit and miss, it puts the data in row 10 for example when there is nothing after row 2. And then if I run it again it just replaces the data that it put in row 10, rather than going to next empty row.

So you know I am using Visual Studio 2011 Prof, thanks for any help!


'----------------Open existing Spreadsheet ----------------------------------
Dim new_app AsNew Microsoft.Office.Interop.Excel.Application
new_app.Visible = False
Dim new_book = new_app.Workbooks.Open(file_path & file_name)
Dim new_sheet = new_book.Worksheets(1).activate

'---------------Identify which row is the last-------------------------------

Dim LastRow = new_app.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row

'--------------Assign the individual records to each cell------------------------

new_app.Range("A" & LastRow).select()
new_app.ActiveCell.FormulaR1C1 = Surename

"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""

 

[alex101fishing]

I did also try using the example code from the msdn library, shown below in a minute, however it does not like the "Microsoft.Office.Tools.Excel.NamedRange" it keeps saying it is not defined....yet according to the msdn I have all of the required refrences and includes? I'm on a bit of a deadline with this one, would really appreciate some help, thanks guys and girls

Private Sub SelectLastCell()
Dim namedRange1 As Microsoft.Office.Tools.Excel.NamedRange _
= Me.Controls.AddNamedRange(Me.Range("A1", "A20"), _
"namedRange1")
namedRange1.Value2 = 100

Dim lastCell As Excel.Range = _
namedRange1.SpecialCells( _
Excel.XlCellType.xlCellTypeLastCell, )
lastCell.Select()
End Sub

 

[Paszt]

I don't have Visual Studio in front of me, but what references do you have? (to use the example, I believe you need the interop and the tools reference). Please provide the link to the MSDN example you are using.

One important question is what version of excel will be used. If it may be more than one version then you will need to use late binding and only use function and methods available to all versions (this gets tricky; you can write the code using the binding of the oldest version, then remove the references and switch to late binding). If you know you will only by using Excel 2007 and greater, they have introduced new table objects that make it a little simpler.

Here's a VBA function that can be converted to VB.NET (sorry I can't help with that right now):

function LastRowIndex(byval w as worksheet, byval col as variant) as long
  dim r as range

  set r = application.intersect(w.usedrange, w.columns(col))
  if not r is nothing then
    set r = r.cells(r.cells.count)

    if isempty(r.value) then
      LastRowIndex = r.end(xlup).row
    else
      LastRowIndex = r.row
    end if
  end if
end function

 

[alex101fishing]

Sorry its taken me so long to get back to you, been a very busy few weeks with vb.net applications being created for my workplace and also for my own interests. I resolved the issue in the end below is the code that I got working, notice the "Type.missing" there seems to be various options available for scanning through cells. Missing looks for the last cell within a set range (either row or column) that contains any user formatting. But there are quite a few variants.

 [FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]  
[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] LastRow = sheet.Cells.SpecialCells(Excel.[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#2b91af][FONT=Consolas][SIZE=2][COLOR=#2b91af][FONT=Consolas][SIZE=2][COLOR=#2b91af]XlCellType[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2].xlCellTypeLastCell, [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#2b91af][FONT=Consolas][SIZE=2][COLOR=#2b91af][FONT=Consolas][SIZE=2][COLOR=#2b91af]Type[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2].Missing).row  [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#008000][FONT=Consolas][SIZE=2][COLOR=#008000][FONT=Consolas][SIZE=2][COLOR=#008000]'Identify which row is the last[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT]
[FONT=Consolas][SIZE=2][COLOR=#008000][FONT=Consolas][SIZE=2][COLOR=#008000][FONT=Consolas][SIZE=2][COLOR=#008000]
[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]        LastRow = LastRow - 1

[\code]

Thank you for trying to help though!

[/SIZE][/FONT][/SIZE][/FONT]