picture scaling (Excel2000)

Topic

New Reply

Hi

I’m trying to use a bit of code to capture a selected range of data to the clipboard, moving to a different workbook where I want to paste the copied data as an ‘image’.
I want to shrink the picture by 50%.

The code runs something like:

[namedrange].CopyPicture Appearance:=xlScreen, Format:=xlBitmap

Workbooks(differentbook).Activate
Range(here).Select
ActiveSheet.Paste

‘reduce picture by 50%..
Selection.ShapeRange.ScaleWidth 0.5, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 0.5, msoFalse, msoScaleFromTopLeft

For some reason, I can’t seem to get the full extent of the [namedrange] in the final shrunken image.
(i.e. its seems to ‘chop off’ beyond a certain number of columns in my [namedrange] )

I’ve tried various combinations including:
[namedrange].CopyPicture Appearance:=xlPrinter, Format:=xlPicture

Any suggestions???

zeddy

Reply | Quote

Replies

Could you be more specific? What is the “certain number of columns”?

Reply | Quote

Hi Hans

The data range I want to capture and scale has a variable number of columns.
(approx 24 columns, width 8 each)
It seems to truncate at a maximum number of columns.

I can manually copy the large column range block to the clipboard, go into Word, set a page to landscape, and use pastespecial to put the full contents there as a bitmap.
Can’t seem to do the same thing if I use another Excel workbook as my destination instead of Word.

zeddy

Reply | Quote

When I try your code, it works correctly to well beyond 60 columns. Could you attach a workbook that demonstrates the problem?

Reply | Quote

Hi Hans

I cobbled together a quick example in the attached file.
The data range is named [block1]

zeddy

Reply | Quote

Am I overlooking something? I have attached the result of clicking the button.

Reply | Quote

You are not overlooking something.
You got what I want.
I didn’t.
When I click the button I don’t get all the columns.
I have attached my result of clicking the button.

zeddy

Reply | Quote

Hans: I get the same result as zeddy. I seem to remember having a similar problem on something I was trying to do quite a while back, and that it was a result of a bug in whatever version I was running at that time. The bug may have been fixed in the version you are running.

Reply | Quote

This may be an interesting line of pursuit.

What version are you using Hans?

I’ve got Excel 2000, Excel2002, Excel2003, and Excel2007 here (various SPs)

zeddy

Reply | Quote

I have XL2K here, the same as you posted the question for. Since you have the others, have you tried it on those? I think Hans has XL2003.

Reply | Quote

Hi Legare

Well you have all the answers!
Yes indeedy. It works in Excel2003.
But not apparently in Excel2000, which is what I was in when I tried it, hence the post.

Thanks a million. I thought it was just me.

zeddy

Reply | Quote

I used Excel 2002 SP-3.

Reply | Quote

Many thanks to all for helping me get this resolved.

..now which is better
Format:=xlPicture
or
Format:=xlBitmap

..what would you use?

zeddy

Reply | Quote

If you want to be able to scale the picture later, Format:=xlPicture is far preferable, for Format:=xlBitmap leads to pixelation if you scale it.

Appearance:=xlScreen leads to a larger file size than Appearance:=xlPrinter without a significant increase in quality, so I’d choose Appearance:=xlPrinter, Format:=xlPicture

(Edited by HansV to correct error)

Reply | Quote

did you mean
so I’d choose Appearance:=xlPrinter , Format:=xlPicture

zeddy

Reply | Quote

Yes, sorry about that. I have corrected my previous reply.

Reply | Quote