Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
de-select range in other workbook?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
de-select range in other workbook?
Hi Ron,
After your paste operation add the line: Application.CutCopyMode = False --- Regards, Norman "Ron" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
de-select range in other workbook?
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. -----Original Message----- Hi Ron, After your paste operation add the line: Application.CutCopyMode = False --- Regards, Norman "Ron" wrote in message ... 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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
de-select range in other workbook?
"Ron" wrote ...
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(vntR sToArray) 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. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
select and copy 100 random cells from a range in a source workbook | Excel Worksheet Functions | |||
Open Workbook - Select Range as table for vlookup | Excel Discussion (Misc queries) | |||
Select a range and copy to new workbook | Excel Programming | |||
Select and move range throughout workbook | Excel Programming | |||
RefEdit - cannot select range on another workbook | Excel Programming |