Appending data from VB to Excel

[frank-in-bahia]

I know this isn't an unusual question, but in the dozens of posts I've read on the subject, I haven't found a solution.

My goal is simple: to paste some data from the Clipboard in VB to an existing Excel spreadsheet . . . appending to data already in the sheet. It's that last part that's giving me problems.

As per the code below (which works perfectly), I can open and copy to the worksheet with no problem, but I don't know how to essentially append, rather than overwrite the data already in the sheet.

Dim sData As String
sData = ("test1" & vbTab & "test2" & vbTab & "test3")
  Clipboard.Clear()
  Clipboard.SetText(sData)
        Dim xl = CreateObject("Excel.Application")
          xl.workbooks.open("C:\test\Book2.xls")  'here's where I need to find the last row and replace Range("A1") in next line
          xl.workbooks(1).Sheets(1).Range("A1").Select() 
          xl.workbooks(1).Sheets(1).Paste()
          xl.application.displayalerts = False
          xl.workbooks(1).Save()
          xl.application.displayalerts = True
          xl.quit()
          xl = Nothing

I think the solution has something to do with finding the last row of existing data and then replacing Range("A1") with "A-whatever the next row is". But I haven't been able to find or craft the code to do it.
I'm using Visual Studio Express 2012, in a Windows Forms Application.
Many thanks!
Frank

 

[jmcilhinney]

I'm curious as to why you're using the Clipboard at all when you can simply set a range of values directly.

 

[frank-in-bahia]

I'm curious as to why you're using the Clipboard at all when you can simply set a range of values directly.

Jim -
I found the Clipboard method in Microsoft KB247412 and it seemed to be the easiest/simplest for my purpose. (Also, the method works fine, and at my novice stage of learning, my motto is "If it ain't broke, don't fix it".)

I've since found an answer for the question posed in my post. The code below is the revised version of the original, with new lines in red and an amended line in blue. Works perfectly.

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim sData As String
        sData = ("test1" & vbTab & "test2" & vbTab & "test3")
        Clipboard.Clear()
        Clipboard.SetText(sData)
        Dim xl = CreateObject("Excel.Application")
        [COLOR=#ff0000]Dim LastRow
        Dim PasteToRow[/COLOR]
        xl.workbooks.open("C:\test\Book3.xlsx")
        [COLOR=#ff0000]lastRow = xl.workbooks(1).Sheets(1).UsedRange.Rows.Count
        PasteToRow = LastRow + 1[/COLOR]
       [COLOR=#0000ff] xl.workbooks(1).Sheets(1).Range("A" & PasteToRow).Select()[/COLOR]
        xl.workbooks(1).Sheets(1).Paste()
        xl.application.displayalerts = False
        xl.workbooks(1).Save()
        xl.application.displayalerts = True
        xl.quit()
        xl = Nothing
    End Sub

 

[frank-in-bahia]

I've also discovered a potentially important variation to the solution I posted :
"UsedRange" will include cells that once contained any data or formatting, even after you've done a "Clear Contents" on them and now consider them 'un-used'.
To avoid that, use this instead: UsedRange.CurrentRegion.<etc>

 

[Herman]

(Also, the method works fine, and at my novice stage of learning, my motto is "If it ain't broke, don't fix it".)

That might be an appropriate motto for someone who has invested money in a solution and does not wish to part with his investment, but it certainly isn't for someone learning programming. If it ain't broke, you should find as many different ways of breaking it as you can, and understand why it is breaking in the first place. Programming is a science, most of the learning is made through experimenting and failing (many, many times over). If you limit yourself to only what you know, how will you ever learn to be better?