Why is this pasting as an image between workbooks? It's TEXT!

[ClimoC]

Hello again,

I can't stop this from happening. All I'm doing is declaring a range, then copying the range and using xlpasteall into another workbook, and it keeps pasting the cells as an image.

I've now just tried doing it Cell by Cell in the following loop:

                    SRange = SBook.Sheets("By Type").Range("A1:A" & SBook.Sheets("By Type").Range("A65000").End(xlUp).Row).Find(what:=ConsolC, _
                                        LookIn:=xlValues, lookat:=xlWhole).Address
                    If OutPut.Sheets("By Type").Range("A65000").End(xlUp).Row > 1 Then
                        DRange = OutPut.Sheets("By Type").Range("A65000").End(xlUp).Offset(9, 0).Address
                    Else
                        DRange = OutPut.Sheets("By Type").Range("A65000").End(xlUp).Offset(1, 0).Address
                    End If
                    For xDx = 0 To 10 Step 1
                        For yDy = 0 To 8 Step 1
                            SBook.Sheets("By Type").Range(SRange).Offset(yDy, xDx).Copy
                            OutPut.Sheets("By Type").Range(DRange).Offset(yDy, xDx).PasteSpecial xlPasteAll
                            Application.CutCopyMode = False
                        Next yDy
                    Next xDx

This keeps pasting as images. Which is clearly not what I want.

It has to be 'Paste Everything' rather than 'Values and Number Formats' (which incidentally, DOES work, but doesn't bring across the Test formatting I need - because there are multiple text colours within each cell.

Another thing to note, is that when the code between the SHEETS in the same book gets a dose of copy/xlpasteallexceptborders, the different text colours DO come through. But the problem is getting them between workbooks.

Can ANYONE help on this? Or can help me make sure that the data goes to the clipboard as it is in the source sheet?

As a test for anyone thinking this is straight forward - Write in a cell in one workbook some text, 1 or 2 carriage returns in it too, and then colour one line of text in that cell red, the second blue. Now try and make a cell in another workbook look exactly like that... !

 

[GlennUK]

I've done a test ( Excel 2007 ), just like you said, and it works fine for me. What version of Excel are you using?

 

[p45cal]

I've done a test ( Excel 2007 ), just like you said, and it works fine for me. What version of Excel are you using?

Ditto for Excel 2010

 

[Andrew Poulsom]

Does this work?

SBook.Sheets("By Type").Range(SRange).Offset(yDy, xDx).Copy OutPut.Sheets("By Type").Range(DRange).Offset(yDy, xDx)

I didn't have a problem copying a cell with rich format text to a sheet in another workbook, using Paste or PasteSpecial|Paste All.

 

[ClimoC]

2003 - Professional.

But we also have some people on 2010 and under Windows 7, so it needs to work across all of them.

One thing I forgot to mention - because of the 'open read only, close without saving' aspect of this, the SOURCE book, I actually create a new instance of Excel for the book to reside in. So I'm actually copying and pasting between 2 workbooks in 2 instances of Excel. but both obviously are 2003, and I've never had this sort of problem before...

If I step through the code, allow it to copy, then use the GUI to pastespecial, I get a lot more options I can paste with, and I can't find the parameters for these in the object libraries.

 

[RoryA]

For me in 2007 it does not copy the colours, but it does not paste as an image. Are your workbooks in the same instance of Excel?
Just seen your note about multiple instances - that's your problem (I also don't see the need for it, based on your 'close without saving' logic?)
When pasting between instances you need the PasteSpecial method of the sheet, not the cell, so you would have to select the cell first.

 

[ClimoC]

Does this work?

SBook.Sheets("By Type").Range(SRange).Offset(yDy, xDx).Copy OutPut.Sheets("By Type").Range(DRange).Offset(yDy, xDx)

I didn't have a problem copying a cell with rich format text to a sheet in another workbook, using Paste or PasteSpecial|Paste All.

"Copy Method of Range class failed"

 

[Andrew Poulsom]

"Copy Method of Range class failed"

It would if the workbooks are in different instances of Excel.

 

[GlennUK]

2003 - Professional.

But we also have some people on 2010 and under Windows 7, so it needs to work across all of them.

One thing I forgot to mention - because of the 'open read only, close without saving' aspect of this, the SOURCE book, I actually create a new instance of Excel for the book to reside in. So I'm actually copying and pasting between 2 workbooks in 2 instances of Excel. but both obviously are 2003, and I've never had this sort of problem before...If I step through the code, allow it to copy, then use the GUI to pastespecial, I get a lot more options I can paste with, and I can't find the parameters for these in the object libraries.

... that's a massive thing to forget to mention. Like rory, I don't see the need to have a separate instance, at all.

 

[ClimoC]

... that's a massive thing to forget to mention. Like rory, I don't see the need to have a separate instance, at all.

I'm re-working it as we speak, but the point behind it all was that the workbook in which this resides serves as like an 'application' for churning out data. It picks up files, copies them in, spits out a new workbook, and then closes itself down

Seems a bit silly though. I can understand problems between applications, but between two excels?!

Will get back to ya'll shortly...