0

I'm trying to get my data import to find the last row of my dataset and paste the row of data from SQL Server into the blank row at the end of the data set.

This is my code I have, but when running the code it will import the data but always on the last row, not the next blank row after the last row

Sub RunImport()
On Error GoTo Err:
cnnstr = "Provider=SQLOLEDB; " & _
            "Data Source=MyServer; " & _
            "Initial Catalog=Mydb;" & _
            "User ID=User;" & _
            "Password=Pwd;" & _
            "Trusted_Connection=No"
Set cnn = New ADODB.Connection
Application.ScreenUpdating = False
cnn.Open cnnstr
Set rs = New ADODB.Recordset
sQRY = "SELECT * FROM MyTable"
rs.CursorLocation = adUseClient
rs.Open sQRY, cnn, adOpenDynamic, adLockOptimistic, adCmdText
Application.ScreenUpdating = False
Sheet1.Range("B5").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveCell.Offset(1, 0).CopyFromRecordset rs
rs.Close
Set rs = Nothing
cnn.Close
Set cnn = Nothing
Exit Sub
Err:
MsgBox "The following error has occured-" & vbCrLf & vbCrLf & VBA.Error, vbCritical, "MySpreadsheet"
MsgBox VBA.Err
End Sub

I imagine that I have put something in wrong around 

Sheet1.Range("B5").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveCell.Offset(1, 0).CopyFromRecordset rs

What do I need to do to put it right?

Community
  • 1
  • 1
Jez
  • 159
  • 1
  • 4
  • 14
  • 1
    read this: [**How to determine last used row/column**](http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba/11169920#11169920) – Dmitry Pavliv Mar 06 '14 at 11:12
  • 1
    use the debugger, put a breakpoint on this line: `Range(Selection, Selection.End(xlDown)).Select` and then press step into and see what happens in excel. maybe it only selects the cell and doesnt activate it, maybe it selects the second from the bottom row, in which case if it consistently does that you can say `ActiveCell.Offset(2, 0).CopyFromRecordset rs` and yes, read simco's link about finding last row – user1759942 Mar 06 '14 at 14:26

1 Answers1

0

I found this as a much simpler answer to what I was looking for.

Range("A1").End(xlDown).Offset(1, 0).Select
Jez
  • 159
  • 1
  • 4
  • 14