Change Cursor Location

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have this code which appends data to an Excel worksheet. After the
appending is done the cursor location is at the end of the last record
appended in the worksheet. How can I programmatically move the cursor back
to let's say cell B2?

Here is my code:

Set Db = New ADODB.Connection
Db.CursorLocation = adUseClient
Db.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" &
Application.CurrentProject.FullName
Set xlApp = CreateObject("Excel.Application")
Set xlwbBook = xlApp.Workbooks.Open(strFile)
Set xlwsSheet1 = xlwbBook.worksheets(SourceName)
xlwsSheet1.Activate

SQL1 = "Select ProcessID, ColorCode1, Status, EWONo1, MCRNo1, ModYear1,
KitSeqNo, Zone1, Station1, KitIDNo1 " _
& "From tblEWO Order by [KitTrackNo], [ProcessID];"

y = xlApp.ActiveCell.Column - 1
xlApp.ActiveCell.Offset(0, -y).SELECT
x = xlwsSheet1.Application.ActiveCell.Cells.Address
rs.CursorLocation = adUseClient
If rs.state = adStateOpen Then
rs.Close
End If
rs.Open SQL1, Db
If rs.RecordCount > 0 Then
rs.MoveFirst
x = Replace(x, "$", "")
y = Mid(x, 2)
Set rng = xlwsSheet1.Range(x)
xlwsSheet1.Range(x).CopyFromRecordset rs
End If
rs.Close


Thanks,

Sarah
 
That worked great! Thanks you!

Ralph said:
xlwsSheet1.Range("B2").Select

Sarah said:
I have this code which appends data to an Excel worksheet. After the
appending is done the cursor location is at the end of the last record
appended in the worksheet. How can I programmatically move the cursor back
to let's say cell B2?

Here is my code:

Set Db = New ADODB.Connection
Db.CursorLocation = adUseClient
Db.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" &
Application.CurrentProject.FullName
Set xlApp = CreateObject("Excel.Application")
Set xlwbBook = xlApp.Workbooks.Open(strFile)
Set xlwsSheet1 = xlwbBook.worksheets(SourceName)
xlwsSheet1.Activate

SQL1 = "Select ProcessID, ColorCode1, Status, EWONo1, MCRNo1, ModYear1,
KitSeqNo, Zone1, Station1, KitIDNo1 " _
& "From tblEWO Order by [KitTrackNo], [ProcessID];"

y = xlApp.ActiveCell.Column - 1
xlApp.ActiveCell.Offset(0, -y).SELECT
x = xlwsSheet1.Application.ActiveCell.Cells.Address
rs.CursorLocation = adUseClient
If rs.state = adStateOpen Then
rs.Close
End If
rs.Open SQL1, Db
If rs.RecordCount > 0 Then
rs.MoveFirst
x = Replace(x, "$", "")
y = Mid(x, 2)
Set rng = xlwsSheet1.Range(x)
xlwsSheet1.Range(x).CopyFromRecordset rs
End If
rs.Close


Thanks,

Sarah
 
Back
Top