Range.CopyPicture question

[Gokhan Aycan]

Is there a limit on how many rows that can be copied or an image size limit? Or some other setting etc I need to check?

When the range is 6000 rows by 20 columns, the resulting image is around 1600 rows (just short of 25000 points).

Essentially using:

rng.CopyPicture xlScreen, xlPicture

rng2.PasteSpecial

xlPrinter is even less, 200ish rows (I guess Print Area comes into play here).
xlBitmap has worse resolution and file size impact is almost double.

 

[Dan_W]

Awesome. Let me know what you think!

 

[Gokhan Aycan]

I did not try Range.Copy with my project file, but on a new file the result is same. I copied 3500 rows and pasted in Paint. 1638 rows were pasted. Extending the columns twofold did not change the result.

Tried to get familiar with GDI tonight. I read that GDI+ can be slower, so I will see if GDI will cut it first.

Could create a compatible bmp, and paint it on a modeless UF.

And at least move it with BitBlt (by -30 and -60 units on y axis)

Will continue to experiment. Thanks for the tips and links.

 

[Jaafar Tribak]

I did not try Range.Copy with my project file, but on a new file the result is same. I copied 3500 rows and pasted in Paint. 1638 rows were pasted. Extending the columns twofold did not change the result.

True. When I copy the entire Column A (manually or via code) , and then paste it in in another column (say column F (Cell F1)), the entire column is pasted successfully . But when I paste it in PAINT or in another application, the pasted data is truncated.

It seems that excel somehow temporarily stores the copied data somewhere other than in the windows clipboard.

BTW, pasting the column in a second excel instance works ok.

 

[Gokhan Aycan]

So, I have gained some momentum. This is done with GDI, but I may need to add GDI+ for waveform images, as they will be png. I am not using any range image but producing the memory bitmap. I will check out drawing text hopefuly tomorrow along with waveforms. I hope I am freeing everything properly.

A video of how it works atm.
Scroll Test - GDI

If you want to check it out, I packed a 7zip file including files necessary. 15MB download. Won't run on MacOS Office. No idea if it would run on 32-bit.

gdi test.7z (GDrive)

 

[Dan_W]

Just saw the video and am now going to look at the files (obvs not going to attempt to run it on my computer because ... you know... it'll die if I tried) - but that's amazing! Great job!

 

[Gokhan Aycan]

Hey Dan, thanks for the encouraging words It is quite fast as well. It takes around 250ms to create 6000 rows, and another 40ms to create the 1000-1200 note shapes. Also, since my current scrolling is based on rows and each row for this song is around 40ms, it looks choppy. But I have tested with further dividing the sleep/amount scrolled with BitBlt and goes much smoother. Average time to BitBlt is around couple of ms with some occasional jumps.

Do you know how I can check the memory used for the bitmap? Would it be counted towards Excel instance in Task Manager or do I need some other app (a Systernals one perhaps?) to get an idea?

I have been mostly checking the documentation today, and only started to write/test clGdiplus.

 

[Dan_W]

Hi - I took the leap and tried your file on my painfully slow computer- and it works beautifully! This is great - and this is coming from someone who has some form PTSD after listening to his father play the Phil Collins CD over and over and over again!

I don't have an answer to your question, I'm sorry - the extent of the my knowledge when it comes to GDI and memory is that GetDC will cause a memory leak if not subsequently cleaned up with ReleaseDC, but I have yet to work out how to test for these memory leaks. I will let you know if I come across anything.

 

[Gokhan Aycan]

The problem I am facing is drawing a png image (waveform) on to the Track_DC.

Since I can't do this with GDI as is, I need to use GDI+ (unless some other method exists). I did succeed to actually draw onto BmpTrack, but as written below it is only a copy of it.

Interop (GDI + on a GDI memory HBITMAP)

Checking out the various examples here and elsewhere, I create a DIBSection. As a test, I draw a single rectangle and BitBlt it back onto TrackDC. (No GDI+ involvement at this stage) The rectangle is there (white) and the rest is all black. I guess I am making a mistake somewhere and maybe creating a monochrome DIB or maybe need some other step? I read about this GetDIBits but unsure if that is even needed, most examples that use it usually write to a file??

The added code is something like this (left out the releasing/deleting parts):

Public DIB_Track As BITMAPINFO
Public BmpDIB As LongPtr, oldBmpDIB As LongPtr
Public h_DIB As LongPtr, h0_DIB As Long

Public Sub Create_DIB()
    
'    With DIB_Track.bmiColors
'        .rgbBlue = 255
'        .rgbGreen = 255
'        .rgbRed = 255
'        .rgbReserved = 0
'    End With

    Dim bytes_per_scanLine As Long
    Dim pad_per_scanLine As Long

    With DIB_Track.bmiHeader
        .biBitCount = 24 ' bmiColors is NULL
        .biCompression = BI_RGB
        .biPlanes = 1
        .biSize = Len(DIB_Track.bmiHeader)
        .biWidth = pxHeaderWidth
        .biHeight = pxRowHeight * NoRows ' + for bottom-up, - for top-bottom
        bytes_per_scanLine = ((((.biWidth * .biBitCount) + 31) \ 32) * 4)
        pad_per_scanLine = bytes_per_scanLine - (((.biWidth * .biBitCount) + 7) \ 8)
        .biSizeImage = bytes_per_scanLine * Abs(.biHeight)
    End With
    
    BmpDIB = CreateDIBSection(h_TrackDC, DIB_Track, DIB_RGB_COLORS, ByVal 0&, ByVal 0&, ByVal 0&)
    
    h_DIB = CreateCompatibleDC(h_DC)
    h0_DIB = SaveDC(h_DIB)
    
    oldBmpDIB = SelectObject(h_DIB, BmpDIB)
    
    Debug.Print bytes_per_scanLine, pad_per_scanLine
    
End Sub

Public Sub test_stuff()

    Rectangle h_DIB, 0, 0, 15, 30

    BitBlt h_TrackDC, 0, 0, DIB_Track.bmiHeader.biWidth, DIB_Track.bmiHeader.biHeight, h_DIB, 0, 0, SRCCOPY

End Sub

Changing to 32 bit and/or uncommenting RGBQUAD doesn't make any difference.

The result looks like this:

Any ideas, or suggestions are welcome, as I feel as I am stuck. (I can get the waveform as bmp, but file size increases from 1MB to 110MB )

 

[Dan_W]

Hi - just saw this now.
110mb for a bmp file?? Ouch.
Perhaps we are imagining entirely different things, but does a waveform need to be anything more much advanced/complicated than a GIF or a JPG? Or is the 1mb size the smallest you can get it, and that happens to be an optimised PNG?

I came across some VB6 code last night which - coincidence? - draws waveforms of WAVE files. I converted your song to WAVE @ 128kb/s, 2 channels, 22050mhz. Came to 21mb in size, and it took the code
29 seconds to render this in a VB6 PictureBox - is that in anyway helpful?

 

[Dan_W]

Haven't had a chance to look at this most recent code. Will try later this afternoon.