Save sheet bitmap to desktop?

  • Thread starter Thread starter StargateFan
  • Start date Start date
S

StargateFan

I have this macro which is invoked by pressing a button:


Sub Msg_CellsToBitmap()
'
Application.CutCopyMode = False
' Range(ActiveCell.Address,
ActiveCell.End(xlDown).Address).Select ' incorrect copy function;
need "copy to bitmap"
Range("B1:B4").CopyPicture Appearance:=xlScreen, Format:=xlBitmap
'====================================================================
MyMsgBox = MsgBox("The cells have been sent to the clipboard as an
image." & vbCrLf & vbCrLf & _
"(Retrieve from the clipboard with, i.e., ^v
shortcut.) ", vbOKOnly + vbInformation, "DONE ...")
End Sub



Rather than saving the file manually, is there a way to save that
image to a bitmap on the desktop, with desktop destination as:

\\companyName\Users\Reception\Desktop\Tod+Tom.bmp

Thanks! :oD
 
I have this macro which is invoked by pressing a button:

Sub Msg_CellsToBitmap()
'
    Application.CutCopyMode = False
'    Range(ActiveCell.Address,
ActiveCell.End(xlDown).Address).Select     ' incorrect copy function;
need "copy to bitmap"
    Range("B1:B4").CopyPicture Appearance:=xlScreen, Format:=xlBitmap
'====================================================================
    MyMsgBox = MsgBox("The cells have been sent to the clipboard asan
image." & vbCrLf & vbCrLf & _
                      "(Retrieve from the clipboardwith, i.e., ^v
shortcut.)     ", vbOKOnly + vbInformation, "DONE ...")
End Sub

Rather than saving the file manually, is there a way to save that
image to a bitmap on the desktop, with desktop destination as:

\\companyName\Users\Reception\Desktop\Tod+Tom.bmp

Thanks!  :oD
Click to expand...
Modify to suit

Sub Create_GIF()
Dim fn As String
Dim mychart As Chart
fn = "c:\a\" & Range("a1").Value & ".gif"
'MsgBox fn
Set mychart = ActiveSheet.ChartObjects(1).Chart
mychart.Export Filename:=fn, FilterName:="GIF"
End Sub
 
Modify to suit

Sub Create_GIF()
Dim fn As String
Dim mychart As Chart
fn = "c:\a\" & Range("a1").Value & ".gif"
'MsgBox fn
Set mychart = ActiveSheet.ChartObjects(1).Chart
mychart.Export Filename:=fn, FilterName:="GIF"
End Sub
Click to expand...

Hi!

Admittedly, I'm not very good at doing the substitutions sometimes and
this one stumped me. I did the best I could and invoked the macro but
got an error 13 type mismatch code. Admittedly, the substitutions I
made don't look good:

Sub Create_GIF()
Dim fn As String
Dim mychart As Chart
fn = "\\CompanyName\Users\Reception\Desktop\Tod+Tom." &
Range("B1:B4").Value & ".gif"
'MsgBox fn
Set mychart = ActiveSheet.ChartObjects(1).Chart
mychart.Export Filename:=fn, FilterName:="GIF"
End Sub

(file name output hopefully would be Tod+Tom.gif)

How can I fix please?

Thanks!! :oD
 
Modify to suit

Sub Create_GIF()
Dim fn As String
Dim mychart As Chart
fn = "c:\a\" & Range("a1").Value & ".gif"
'MsgBox fn
Set mychart = ActiveSheet.ChartObjects(1).Chart
mychart.Export Filename:=fn, FilterName:="GIF"
End Sub
Click to expand...

I get a mismatch 13 error, again.

This time I didn't change anything other than the range (leaving the
formula as is gave me another error altogether and nothing happened):

Sub Create_GIF_Original()
Dim fn As String
Dim mychart As Chart
fn = "c:\a\" & Range("B1:B4").Value & ".gif"
'MsgBox fn
Set mychart = ActiveSheet.ChartObjects(1).Chart
mychart.Export Filename:=fn, FilterName:="GIF"
End Sub

The fn="c:\a\" ... line is highlighted in the debugger.

So I tried going back to some sort of basics. Using set mychart line
I got from the above code, googling came to this example:

http://answers.yahoo.com/question/index?qid=20080131092437AAeLVto
Sub Create_GIF_of_workbook()
Dim mychart As Chart
Set mychart = ActiveSheet.ChartObjects(1).Chart
mychart.Export FileName:="P:\0DESKTOP\TOD+TOM.gif", FilterName:="GIF"
End Sub

But again error. This time 1004, Unable to get ChartObjects property
of the worksheet class.

All other attempts have failed so far. Help!
 
I get a mismatch 13 error, again.

This time I didn't change anything other than the range (leaving the
formula as is gave me another error altogether and nothing happened):

Sub Create_GIF_Original()
Dim fn As String
Dim mychart As Chart
fn = "c:\a\" & Range("B1:B4").Value & ".gif"
'MsgBox fn
Set mychart = ActiveSheet.ChartObjects(1).Chart
mychart.Export Filename:=fn, FilterName:="GIF"
End Sub

The fn="c:\a\" ... line is highlighted in the debugger.

So I tried going back to some sort of basics. Using set mychart line
I got from the above code, googling came to this example:

http://answers.yahoo.com/question/index?qid=20080131092437AAeLVto
Sub Create_GIF_of_workbook()
Dim mychart As Chart
Set mychart = ActiveSheet.ChartObjects(1).Chart
mychart.Export FileName:="P:\0DESKTOP\TOD+TOM.gif", FilterName:="GIF"
End Sub

But again error. This time 1004, Unable to get ChartObjects property
of the worksheet class.

All other attempts have failed so far. Help!
Click to expand...

Hey, just realized something is sticking out like sore thumb, I have
an excel spreadsheet and not a chart. Why the line:

Set mychart = ActiveSheet.ChartObjects(1).Chart
??

Is that where problem lies?

I need this:

Range("B1:B4").CopyPicture Appearance:=xlScreen, Format:=xlBitmap

to be saved to desktop as

P:\0DESKTOP\TOD+TOM.gif

Is the chart reference best way? Thanks once again.
 
I have this macro which is invoked by pressing a button:


Sub Msg_CellsToBitmap()
'
Application.CutCopyMode = False
' Range(ActiveCell.Address,
ActiveCell.End(xlDown).Address).Select ' incorrect copy function;
need "copy to bitmap"
Range("B1:B4").CopyPicture Appearance:=xlScreen, Format:=xlBitmap
'====================================================================
MyMsgBox = MsgBox("The cells have been sent to the clipboard as an
image." & vbCrLf & vbCrLf & _
"(Retrieve from the clipboard with, i.e., ^v
shortcut.) ", vbOKOnly + vbInformation, "DONE ...")
End Sub



Rather than saving the file manually, is there a way to save that
image to a bitmap on the desktop, with desktop destination as:

\\companyName\Users\Reception\Desktop\Tod+Tom.bmp

Thanks! :oD
Click to expand...

Okay, thanks to the code given by Don Guillett, had more amunition to
use to do broader searches. The only thing that has worked so far,
and does an okay job, is this code (sure enough, creates a chart
first!):

'2011-08-17 19:27:39
(http://vbadud.blogspot.com/2010/06/how-to-save-excel-range-as-image-using.html)
Sub Export_Range_Images()
' ==========================================
' Code to save selected Excel Range as Image
' ==========================================

Dim oRange As Range
Dim oCht As Chart
Dim oImg As Picture

Set oRange = Range("B1:B4")
Set oCht = Charts.Add

oRange.CopyPicture xlScreen, xlPicture
oCht.Paste
oCht.Export Filename:="P:\0DESKTOP\TOD+TOM.gif", Filtername:="GIF"

End Sub


If I can just fine-tune it, will be very happy!

1) It saves a GIF of the range B1:B4, yes, but drags the chart
background with it. Can the range itself be the only thing exported,
by any chance? Would the parameters in oRange.CopyPicture xlScreen,
xlPicture be the culprits (xlScreen???).

2) Error capture re path possible? If desktop is not P:\0DESKTOP\,
as when I do this at work where desktop at different location, any way
to prompt user to change path?

Thanks! This is very encouraging. By far best results without going
some sort of API route which means to me I'd have to dl a third party
something which is no good. Need this to work wherever I am without
installing or fiddling unnecessarily. Cheers. :oD
 
[snip]
1)  It saves a GIF of the range B1:B4, yes, but drags the chart
background with it.  Can the range itself be the only thing  exported,
by any chance?  Would the parameters in oRange.CopyPicture xlScreen,
xlPicture be the culprits (xlScreen???).

2)  Error capture re path possible?  If desktop is not P:\0DESKTOP\,
as when I do this at work where desktop at different location, any way
to prompt user to change path?
Click to expand...

Hi, everyone. I have tried to fix this myself but I have had no
luck. On the code above, is there _any_ way to JUST get the range
exported? The export includes what must be a temporary chart
background.

Secondly, any error correction possible on the path?

Thank you!

:oD
 
Back
Top