de-select range in other workbook?

[Ron]

Greetings,

I am copying data from one workbook (book1) to another
(book2) like this:

Dim sht As Worksheet
Set sht = Workbooks("Book2").Sheets("Sheet1")
sht.Range("B10:H20").PasteSpecial


So when I go to Book2 I see the data but it is all
highlighted. I tried de-selecting it by saying

sht.Range("A1").Select

after the PastSpecial statement, but that gave me an error
message that the selection failed. Is there a way to do
this in code to de-select a range in a workbook from
another workbook?

Thanks,
Ron

 

[Norman Jones]

Hi Ron,

After your paste operation add the line:

Application.CutCopyMode = False

 

[Ron]

Thanks. Well, I tried it but the copied data was still
highlighted. I think

Application.CutCopyMode = False

only works if it is called in the same workbook as the
highlighted data. I tried running that statement from
another workbook. My alternative would be to write the
data to the sheets in the 2nd workbook (like 1500 rows)
like with a range object. But that would take way longer
than just copy and paste. I may just have to live with
this. My problem is that I am using Excel97 and pulling
data from Sql Server 2k and so can't use CopyFromRecordset
with the ADO recordset. Wait, I just had an idea, I will
pull the data to my ADO recordset and then copy that data
to a DAO recordset and then I can use copyfromrecordset
with the dao recordset. Hope that's as fast as copy and
paste with the DataObject thing I've been using.

 

[Jamie Collins]

I am using Excel97 and pulling
data from Sql Server 2k and so can't use CopyFromRecordset
with the ADO recordset. Wait, I just had an idea, I will
pull the data to my ADO recordset and then copy that data
to a DAO recordset and then I can use copyfromrecordset
with the dao recordset.

Or you could use the ADO recordset's GetRows method with Excel's
Transpose function to read the data as an array into a Range object.
Here's something from my Excel97 days:

Private Function CopyFromRecordset( _
ByVal ExcelRange As Excel.Range, _
ByVal rs As ADODB.Recordset) As Boolean
Dim intFieldCount As Long
Dim intLoopA As Long
Dim intLoopB As Long
Dim vntRsToArray As Variant
Dim rngArrayToRange As Excel.Range

' ---------------------------------------------------------------------
' Limitations: assigning array to Excel Range object:
' o cannot contain OLE object fields or array data;
' o cannot contain Date fields that have a date prior to the year
1900

' Limitations: Excel's Transpose method:
' o array cannot contain an element greater than 255 characters;
' o array cannot contain Null values;
' o number of elements cannot exceed 5461.
' ---------------------------------------------------------------------

If rs Is Nothing Then
CopyFromRecordset = False
Exit Function
End If

With Excel.Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

' Insert column headings
intFieldCount = rs.Fields.Count
For intLoopA = 0 To intFieldCount - 1
ExcelRange.Offset(0, intLoopA).Value = rs.Fields(intLoopA).Name
Next intLoopA

If rs.EOF Then
GoTo Clean_Up
End If

rs.MoveFirst

' Insert data
vntRsToArray = rs.GetRows
Set rngArrayToRange = ExcelRange.Offset(1,
0).Resize(UBound(vntRsToArray, 2) + 1, UBound(vntRsToArray, 1) + 1)

On Error Resume Next
rngArrayToRange.Value =
Excel.Application.WorksheetFunction.Transpose(vntRsToArray)
If Err.Number <> 0 Then

' Excel limitation encountered - do it the hard way!
On Error GoTo 0
For intLoopA = 0 To UBound(vntRsToArray, 2)
For intLoopB = 0 To intFieldCount - 1
ExcelRange.Offset(intLoopA + 1, intLoopB).Value =
vntRsToArray(intLoopB, intLoopA)
Next intLoopB
Next intLoopA
End If
On Error GoTo 0

' Autofit columns including headings
rngArrayToRange.Resize(rngArrayToRange.Rows.Count + 1,
rngArrayToRange.Columns.Count).Offset(-1, 0).Columns.AutoFit

CopyFromRecordset = True

Clean_Up:

With Excel.Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With

End Function


Jamie.

--