robotjesus

I'm trying to copy a range from a VSTO excel worksheet into a new, external, non-VSTO worksheet using PasteSpecial. If I try to copy and paste a range from the VSTO worksheet into the new worksheet the range gets copied as an image, whereas if I copy and paste a single cell, the cell copies as one would expect - i.e. as cell data and formatting and not as an image.

Is there any way to copy a range in this way (as cell text and formatting and not image)

/* Copies as image */

Excel.Range rng = Globals.MySheet.Range[Globals.MySheet.Cells[10, 2],

Globals.MySheet.Cells[26, 8]];

rng.Copy(missing);

Excel.Range rngPaste = (Excel.Range)wsNewSheet.Cells[10, 2];

rngPaste.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteValues,

Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone,

missing, missing);

/* Copies as cell */

rng = (Excel.Range)Globals.MySheet.Cells[1, 1];

rngPaste = (Excel.Range)wsNewSheet.Cells[1, 1];

rng.Copy(missing);

rngPaste.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteValues,

Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone,

missing, missing);

Many thanks



Re: Visual Studio Tools for Office Copying range into new worksheet NOT as image

Dennis Wallentin

Hi,

I'm not able to reproduce it in VB.NET with the following snippet code:

Dim wsSheet As Excel.Worksheet = Globals.ThisWorkbook.Worksheets.Add

Dim rnSource As Excel.Range = Globals.Sheet1.Range("A1:C8")

Dim rnTarget As Excel.Range = wsSheet.Range("A1:C8")

rnSource.Copy(rnTarget)

However, one way to solve it is to not copy and instead use the following approach:

Dim wsSheet As Excel.Worksheet = Globals.ThisWorkbook.Worksheets.Add

Dim rnSource As Excel.Range = Globals.Sheet1.Range("A1:C8")

Dim rnTarget As Excel.Range = wsSheet.Range("A1:C8")

rnTarget.Value = rnSource.Value

Where You need to format the targeting range.






Re: Visual Studio Tools for Office Copying range into new worksheet NOT as image

robotjesus

Thanks Dennis, I'll give that a try.

FYI:

I've found out the problem only occurs when I'm trying to copy a range that contains a listobject.





Re: Visual Studio Tools for Office Copying range into new worksheet NOT as image

Cindy Meister

robotjesus wrote:

Thanks Dennis, I'll give that a try.

FYI:

I've found out the problem only occurs when I'm trying to copy a range that contains a listobject.

I think that definitely has something to do with the problem, then. If this is a VSTO list object control, then it's not something you can just copy to another workbook. A VSTO control is a wrapped ActiveX object, and the workbook container must be a VSTO workbook in order to support it. In all other circumstances (for example, when security permissions aren't set correctly, so the VSTO dll doesn't load) Office will display a VSTO control as a graphical representation. Sounds as if this is what you're seeing.






Re: Visual Studio Tools for Office Copying range into new worksheet NOT as image

Dennis Wallentin

Hi,

FYI:

I've found out the problem only occurs when I'm trying to copy a range that contains a listobject.

Hm, ListObjects belongs to the group of Host Controls (they don't play according to the range object's rules). Perhaps You need to convert the ListObject to a plain range by using the method Unlist of the ListObject and then copy the range. That is, if it does not interfear with the present solution.