Copy and Paste Error between Application instances

Hey everyone,

I can't figure this one out. I'm getting "Copy method of Range class failed":

Code:

---

Public Sub ImportDataNow(ByVal xlWB As Excel.Workbook, _
ByVal xlSh As Excel.Worksheet, ByVal xlDataRange As Excel.Range)
' This will perform the actual importing process to a new workbook.
Dim wbData As Excel.Workbook
Dim shData As Excel.Worksheet
' Create new workbook to import data
Application.Workbooks.Add
Set wbData = ActiveWorkbook
Set shData = wbData.Sheets("Sheet1")
shData.Name = "Scheduling Import - " & MonthName(Month(Now()), False)
' Import
xlWB.Worksheets(xlSh.Name).Range(xlDataRange.Address(0, 0)).Copy shData.Range("A1")
End Sub

---

If need be I'll post the whole code.

If it helps the range address that's copied is: "A1:AG2,A3329:AG3575"
And the workbook I'm copying from is opened Read-Only, but not sheet/workbook protected.

:dunno I don't know what else to say...

I should also mention that the Procedure passing the variables to the above procedure uses a new instance of the xl Application object to open the workbook.

You know what, here's the main procedure:

Code:

---

Option Explicit
Option Compare Text
Public Const strSheetName As String = "ACTIVE"
Public Const strSchedulingDir As String = "MyServer"
Public Const strFileStandard As String = "ProductionC"

Public Sub ImportDataMain()
'\\ Author: [redacted]
'\\ Date Created: December 14, 2006
'\\ Purpose: To automate daily reports about what has shipped and what
'\\ has not shipped for the current month.
' This main procedure is the setup for the actual importing of data from the
' scheduling workbook.
Dim xlApp As New Excel.Application
Dim xlWB As Excel.Workbook
Dim xlSh As Excel.Worksheet
Dim xlDataRange As Excel.Range
Dim strWB As String
Dim curMonth As String
curMonth = MonthName(Month(Now()), False)
strWB = GetCurrentSchedule
If strWB = "" Then
    ' Error Occurred
    MsgBox "The Scheduling File cannot be found." & vbCrLf & "The procedure cannot continue.", _
    vbCritical, "Error Occurred"
    GoTo ErrorHandler
End If
' Open the workbook behind the scenes. This will not bring up an error even if
' the workbook is already open because you are creating a whole new instance of the Application object
Set xlWB = xlApp.Workbooks.Open(strSchedulingDir & strWB, ReadOnly:=True)
If SheetExists(xlWB, strSheetName) = False Then
    ' Error Occurred
    MsgBox "The sheet " & strSheetName & " does not exists." & vbCrLf & "The procedure cannot continue.", _
    vbCritical, "Error Occurred"
    GoTo ErrorHandler
    Else
    Set xlSh = xlWB.Worksheets(strSheetName)
End If
If IsError(GetDataRange(curMonth, xlWB, xlSh)) Then
    ' Error Occurred
    MsgBox "There has been an error in finding the data range." & vbCrLf & "The procedure cannot continue.", _
    vbCritical, "Error Occurred"
    GoTo ErrorHandler
End If
' Get the data range for the importing process
Set xlDataRange = GetDataRange(curMonth, xlWB, xlSh)
' Now that everything necessary has been collected, start the importing process
Call ImportDataNow(xlWB, xlSh, xlDataRange)
ErrorHandler:
xlWB.Close
xlApp.Quit
End Sub

---

And here's the function to get the data range (for the guru's, please don't laugh / ask. I'm working with a retarted worksheet for the moment which I'm trying to convince everyone we should consider a new design. I just need these procedures as a quick workaround.)

Code:

---

Public Function GetDataRange(ByVal strMonth As String, ByVal xlWB As Excel.Workbook, _
ByVal xlSh As Excel.Worksheet) As Variant
' Returns the specified range for the Scheduling workbook based on month
' First test if the strMonth is an actual month
On Error Resume Next
Dim lTest As Long
lTest = Month(DateValue("01-" & strMonth & "-1900"))
If Err.Number <> 0 Then
    GetDataRange = CVErr(xlErrValue)
    Exit Function
End If
On Error GoTo 0
Dim rngStart As Excel.Range, rngEnd As Excel.Range, rngFinal As Excel.Range
Dim i As Long, j As Long, iLastCol As Long
' Get the short month
strMonth = Application.WorksheetFunction.Proper(Left(strMonth, 3))
' Get Last column
iLastCol = xlWB.Worksheets(xlSh.Name).Cells(1, Columns.Count).End(xlToLeft).Column
' Find the range based on the Month passed to the function
With xlWB.Worksheets(xlSh.Name).Range("A1:A" & xlWB.Worksheets(xlSh.Name).Cells(Rows.Count, 1).End(xlUp).Row)
    Set rngStart = .Cells.Find(What:=strMonth, LookIN:=xlValues, LookAt:=xlWhole, _
    SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, _
    SearchFormat:=False)
End With
' Could not find range
If rngStart Is Nothing Then
    GetDataRange = CVErr(xlErrValue)
    Exit Function
End If
' Since findind the first instance of the strMonth string, we're going to move down cells
' until there is no blank.
Do
i = i + 1
Set rngStart = rngStart.Offset(1, 0)
' There needs to be some kind of control incase the found cell is the last in the used range
If i = 20 Then ' Too many blank cells
    GetDataRange = CVErr(xlErrValue)
    Exit Function
End If
Loop Until rngStart.Value <> ""
' Now find the last row used in the range...this is tricky because there are blank rows
' in the scheduling workbook. So we'll use Column D because after the last cell used
' there are many blank rows together.
i = 0
Set rngEnd = xlWB.Worksheets(xlSh.Name).Cells(rngStart.Row, "D")
Do
i = i + 1
If rngEnd.Offset(i, 0).Value <> "" Then
    ' If it makes it here, reset the rngEnd
    Set rngEnd = rngEnd.End(xlDown)
    i = 0
End If
Loop Until i = 4
' Set rngEnd to be the last row of usable data and last column & to include the headers
Set rngEnd = xlWB.Worksheets(xlSh.Name).Cells(rngEnd.Row, iLastCol)
' Get final range (including the headers)
Set rngFinal = xlWB.Worksheets(xlSh.Name).Range("A1:" & xlWB.Worksheets(xlSh.Name).Cells(2, rngEnd.Column).Address & _
", " & rngStart.Address & ":" & rngEnd.Address)
' Have to use Set keyword to create the range object passed back to the calling procedure
Set GetDataRange = rngFinal
'Debug.Print rngStart.Address(0, 0), rngEnd.Address(0, 0)
End Function

---

Then I tried this and it works when I have the workbook open in the same instance:

Code:

---

Sub TestMe()
Workbooks("ProductionC121506.xls").Sheets("ACTIVE").Range("A1:AG2,A3329:AG3575").Copy _
Destination:=Workbooks("Book1").Sheets("Scheduling Import - December").Range("A1")
End Sub

---

So I *think* my problem lies from copying and pasting across Application instances, but I could be wrong.

I thought I could explicity call out the new App instance and the current App instance in the copy portion of the code, but that didn't work either:

Code:

---

' Import
xlApp.Workbooks(xlWB.Name).Worksheets(xlSh.Name).Range(xlDataRange.Address(0, 0)).Copy _
Destination:=Application.Workbooks(wbData.Name).Worksheets(shData.Name).Range("A1")

---

What's worse, is if I just leave out the Destination field and have it copy to the clipboard and paste (manually), instead of Pasting "A1:AG2,A3329:AG3575", it pastes "A1:AG3575" :dunno what gives?

Ok, I made a small workaround. I decided to use the new App instance to create another workbook and import the data there....then show the App when it's finished.

I don't really like this idea because the user will have two instances of excel open, taking up memory and what-not. And I don't want to use the active App instance because I don't want the user to see anything that's going on. By this I mean the workbook will show in the start bar, unless there's a way to fully hide the opened workbook in the current App instance.

Anyway here's what I have for now:

Code:

---

Public Sub ImportDataNow(ByVal xlApp As Excel.Application, ByVal xlWB As Excel.Workbook, _
ByVal xlSh As Excel.Worksheet, ByVal xlDataRange As Excel.Range)
' This will perform the actual importing process to a new workbook.
Dim wbData As Excel.Workbook
Dim shData As Excel.Worksheet
' Create new workbook to import data
Set wbData = xlApp.Workbooks.Add
Set shData = wbData.Sheets("Sheet1")
shData.Name = "Scheduling Import - " & MonthName(Month(Now()), False)
' Import
xlApp.Workbooks(xlWB.Name).Worksheets(xlSh.Name).Range(xlDataRange.Address).Copy _
Destination:=xlApp.Workbooks(wbData.Name).Worksheets(shData.Name).Range("A1")
Call ClearClipBoard
xlApp.Visible = True
End Sub

---

EDIT: I changed the thread title to better suit the problem :thumb

Does

Code:

---

option compare text

---

counts for worksheetnames ? It's because I tried the following :

Code:

---

Sub test()
Dim curMonth As String
curMonth = MonthName(Month(Now()), False)
End Sub

---

The current month is december and not December. Could be nothing (edited : it's nothing, don't bother - just tested it) but it's because you hardcoded it in your manual copy routine ?

Charlize

Hi Joseph,

Code:

---

Public Function GetDataRange(ByVal strMonth As String, ByVal xlWB As Excel.Workbook, _
    ByVal xlSh As Excel.Worksheet) As Variant

---

Why a variant? Make it a range, and see if that changes anything...

Surely, your intention for this procedure is to always return a range, correct? Declaring as variant is essentially the same as avoiding option explicit. (Well, maybe not quite that extreme, but close.) ;)

Hey Ken,

I use Variant for the function incase there is an error within it, thus returning CVErr(xlErrValue). So it needs to be a variant because of that. And I'd rather the procedure handle the error rather than the function.

In anycase, I tried explicitly copying one range from one instance of Excel to the current instance of excel and still no luck. I don't know much about how excel uses the clipboard...from what I've seen if you perform a copy-paste through code, i.e.:

Code:

---

Sub Test()
    Dim rng1 As Excel.Range, rng2 As Excel.Range
Set rng1 = Range("A1")
    Set rng2 = Range("A4")
rng1.Copy rng2
End Sub[/vba] It is not placed in the clipboard. But if you do the following:[vba]Sub Test()
    Dim rng1 As Excel.Range, rng2 As Excel.Range
Set rng1 = Range("A1")
' Set rng2 = Range("A4")
rng1.Copy
End Sub

---

It is placed in the clipboard.

But from further investigation I've come to find the clipboard does not like multiple cell ranges (with same amount of columns with variable rows or vice versa), as the following:

Code:

---

Sub Test()
    Dim rng1 As Excel.Range, rng2 As Excel.Range
Set rng1 = Range("A1:E2, A4:E45")
'    Set rng2 = Range("G1")
rng1.Copy
End Sub

---

Will be placed in the clipboard, correctly. And if pasted into a cell (either manually or by code) it works fine IF excel is still in CutCopyMode. If it is not in CutCopyMode when pasted you will not get Range("A1:E2, A4:E45") but rather Range("A1:E45") which is not what I want at all. And I feel that this is probably my main problem because when I copy from one instance of Excel to paste into the other, it may drop the CutCopyMode (haven't tested yet). I've already tried placing the range in the clipboard from the created Excel instance...then PasteSpecial into the current Excel instance..resulting in what I don't want - an overfilled worksheet of 3500 lines of data where it should be 250 lines.

I've seen DataObject used with the clipboard....I'll look into that. Maybe I could use it for something.

BTW, I tried using two separate Copy-PasteSpecial routines (one for the headers, the other for the data), but in either case, it places an embedded set of data into the current instance of Excel from the created instance...which is - sadly again - what I don't want.

And Charlize, even though you figured it out, I just want to let you know that I use Option Compare Text to make sure Cells.Find or Instr() or StrComp() etc are not case-sensitive. And I use curMonth just so I can import data from the worksheet of the current month (because the sheet has data for an entire year, all on one worksheet....I know, I know. Horrible organization).

:think:

Open two intances of Excel. Rightclick some cell, choose copy. Right click another cell within any worksheet in any workbook located within the same instance, choose PasteSpecial. Now rightclick a cell anywhere within the other instance. You will see all of the available formats. My assumtion is that Range.Copy implicitly calls a hidden method "_PasteSpecial" to perform the paste in the destination range. To copy non-contiguous ranges to another application with a contigious result is not possible as far as I know. There is no comparable format to pass to the PasteSpecial method. An oversight I suppose. Anyway, assuming that you need to retain your formats, a simple workaround might be to perform an intermediary copy operation.

Code:

---

Sub CallTestMe()
    Dim DestWb As Workbook
With CreateObject("Excel.Application")
    .Visible = True
    Set DestWb = .Workbooks.Add
    End With
    Call TestMe(ThisWorkbook, DestWb)
End Sub
 
Sub TestMe(SrcWb As Workbook, DestWb As Workbook)
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    SrcWb.Sheets(1).Range("A1:AG2,A3329:AG3575").Copy
With SrcWb.Sheets.Add
    .Paste
    .UsedRange.Copy
    DestWb.Sheets(1).Range("A1").PasteSpecial (3)
    .Delete
    End With
End Sub

---

If you only need to pass values, we have some much better options.

Hey tstom :hi:

Great workaround! I think this is just what I need. I'll test it out monday at my job and let everyone know. :thumb

I have a question, though. When you use PasteSpecial (3), what xlPasteType is that? I looked up the enumerations but didn't see 3 or any addition of the xlPasteType values that added up to 3. The only thing that was 3 was xlPasteSpecialOperationSubtract for the xlPasteSpecialOperation...is this what that means? Doesn't seem to be, but I'm not sure.


Anyway thanks again for the workaround, I really appreciate it. I do believe I will be needing to keep the formats so this will be excellent. Just curious, what better options do we have if I just want to paste values?

I enumerated through all of the available format types and 3 is what worked best. I believe the argument you should pass is actually a variant string, "Microsoft Excel 8.0 Format". The (3) may or may not return an incorrect type in other scenarios. I don't know...


Edit:

Code:

---

DestWb.Sheets(1).Range("A1").PasteSpecial "Microsoft Excel 8.0 Format"

---

appears to be correct.

Joseph, why are you passing your objects ByVal instead of ByRef? Just curious.

tstom,

Thanks for the explanation. I don't fully understand why it works so nicely, but for now, it works :thumb


Zack,

I don't see a reason to. I'm not changing the passed variables in my functions, so I don't need a pointer to them. I guess it doesn't matter either way...I just don't see the point in passing anything ByRef if you're not going to change the original variable (except for arrays, which can't be passed ByVal unless the Function's parameter is Variant for the array passed). :) Why? What would you have done?

I just like to keep my options open. If, for the scope of your application, it is not needed, then no worries. I was just curious. Guess I had a few assumptions of my own. :)

:) I see.

I figured, if anybody would ask a question about my code, it would be one of the following:

1. Why are you looping through 20 blank cells?
2. Why do you loop through 4 cells after?
3. Does this spreadsheet have any type of format?
4. In your function, why do you pass a worksheet when you only use the Worksheet's name...why not pass the string?

But either they figured it out, assumed correctly, didn't think to ask, or just didn't recognize it. :)

I'm like you, though, Zack. I'm curious to know why people code the way they do. Please, always feel free to ask :thumb

:thumb Awesome! So far, so good. I'll do some testing before I mark this thread solved.

Here's what I ended up with:

Code:

---

Public Sub ImportDataNow(ByVal xlApp As Excel.Application, ByVal xlWB As Excel.Workbook, _
ByVal xlSh As Excel.Worksheet, ByVal xlDataRange As Excel.Range)
' This will perform the actual importing process to a new workbook.
Dim wbData As Excel.Workbook
Dim shData As Excel.Worksheet
' Create new workbook to import data
Set wbData = Application.Workbooks.Add
Set shData = wbData.Sheets("Sheet1")
shData.Name = "Scheduling Import - " & MonthName(Month(Now()), False)
' Temporarily create new sheet in Production workbook and copy and paste data there
xlWB.Worksheets(xlSh.Name).Range(xlDataRange.Address).Copy
With xlWB.Sheets.Add
    .Paste
    .UsedRange.Copy
    ' .PasteSpecial (3) = "Microsoft Excel 8.0 Format"
    wbData.Worksheets(shData.Name).Range("A1").PasteSpecial (3)
    .Delete
End With
Call ClearClipBoard
End Sub

---

Now it's time to format and edit my data :)

Thanks Tom for the help, much appreciated :friends: