Saving BMP Image Using VBA

Morning All,

After completing a quick search for a similar piece of code I was able to locate the following from circa 2005:

PHP Code: 

Option Explicit

Private Sub SaveRngAsBMP(Rng As Range, FileName As String)
Rng.CopyPicture xlScreen, xlBitmap
SavePicture PastePicture(xlBitmap), FileName
End Sub

Sub TestIt()
Dim Rng As Range, Fn As String
Set Rng = Range("A1:A5")
Fn = "C:\MyFile.bmp"
SaveRngAsBMP Rng, Fn
End Sub 


The problem I'm having is, that code looks like it would never work to me, and I certainly can't seem to make it work.

Effectively I intend to push a variable range into it by using "Set Rng=ImageRange", with ImageRange being a variable determined by another subroutine. The ImageRange will then be saved as a picture (it's for a widget style of reporting that only supports images unfortunately).

Does anyone have any ideas? The second I mess around with the order the code is written in, or any of the variables, it fails, so I'm at a loss unfortunately.

Cheers,

Harry.

Is it missing a sub/function called PastePicture?

Originally Posted by kersplash

Is it missing a sub/function called PastePicture?

So it would appear, but the code I copied didn't have it in and unfortunately the original website is down.

Do you have any ideas?

I found a function on this page, not sure if it's related to what you are trying to do or not.

http://www.vbaexpress.com/forum/show...ata-as-picture

Originally Posted by kersplash

I found a function on this page, not sure if it's related to what you are trying to do or not.

http://www.vbaexpress.com/forum/show...ata-as-picture

Not exactly, I want to save a BMP image of the selected range.

Originally Posted by bakerman2

How about this.

Please Login or Register  to view this content.

Bakerman, I tried to test this but have a couple of questions.

Firstly, it looks like I need to add some code for defining "rng" is that correct?
Finally, when i paste your code in, I can't run or step through it, any ideas?

Thanks for your time, appreciate it.

Originally Posted by bakerman2

All is defined in TestIt so it's that code you need to run.

Didn't even notice that part at first haha.

I'm getting a 1004 error when i get to ".Chart.Paste" - could this be because the data it's copying is part of a pivot table?

Thanks again.

Any ideas anyone?

Originally Posted by humdingaling

Please Login or Register  to view this content.

That seem's to be working very well, thank's for that.

Another question I have is, if i wanted to make the file name and range a variable, what would be the syntax?

I've tried ActiveSheet.Range(PictureRange), for the range, but i'm receiving a 1004 error.

Originally Posted by bakerman2

The named range must be inside double quotes.

Please Login or Register  to view this content.

Thanks for the reply.

I'm using the following code to set "PictureRange".

PHP Code: 

Set PictureRange = ActiveSheet.Range(Cells(1, 1), Cells(LastRow, 16)) 


Then I'm trying to reference it by using:

PHP Code: 

Set Plage = ActiveSheet.Range(PictureRange) 


I can't see where the double quotes would go, i'm effectively passing the range between 2 variables, so I don't understand what the error is actually telling me. I can select the range and that seems to work fine.

Thanks again for all your help.

Originally Posted by xlnitwit

I think it would just be

Please Login or Register  to view this content.

or simply use PictureRange instead of plage in the rest of the code.

That seems to have worked, sometimes it's the simple fixes!

The following macro code works perfectly for me Harry. This is a combination of what I already had and then help from this forum:

Please Login or Register  to view this content.

I have put red text on the only thing you'll need to amend (the range for your export).

It also allows you to save the file wherever you wish and also choose the file name of your choice.

Hope this helps