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
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