To capture content of excel as image

Question

2.33/5 (3 votes)

See more:

Hello,

I have written the below code for capturing the excel content as image, adding new sheet in excel and pasting the image in added new sheet.
Earlier my code for capturing the image was working but now its giving some error. Please let me know the error as i am unable to identify it.
Also the code is creating chart in the image but i dont want the chart.

VB

' To copy content as image
Sub Export_Range_Images()
 

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

Set oRange = Range("A1:I84")
Set oCht = Charts.Add
Set oImg = Picture.Add
oCht.Paste
oCht.Export Filename:="E:\img\SavedRange.jpg", Filtername:="JPG"
End Sub
 
' To add new sheet
Sub AddSheet()
Dim ActNm As String
 

With ActiveWorkbook.Sheets
.Add after:=Worksheets(Worksheets.Count)
End With
ActNm = ActiveSheet.Name
On Error Resume Next
ActiveSheet.Name = "Jul 16 2012"
NoName: If Err.Number = 1004 Then ActiveSheet.Name = InputBox("Give name.")
If ActiveSheet.Name = ActNm Then GoTo NoName
On Error GoTo 0
End Sub
 
'Code to paste image in newly added sheet
Sub TestInsertPictureInRange()
InsertPictureInRange "E:\img\SavedRange.jpg", _
Range("A1:I84")
End Sub
 

Sub InsertPictureInRange(PictureFileName As String, TargetCells As Range)
' inserts a picture and resizes it to fit the TargetCells range
Dim p As Object, t As Double, l As Double, w As Double, h As Double
If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub
If Dir(PictureFileName) = "" Then Exit Sub
' import picture
Set p = ActiveSheet.Pictures.Insert(PictureFileName)
' determine positions
With TargetCells
t = .Top
l = .Left
w = .Offset(0, .Columns.Count).Left - .Left
h = .Offset(.Rows.Count, 0).Top - .Top
End With
' position picture
With p
.Top = t
.Left = l
.Width = w
.Height = h
End With
Set p = Nothing
End Sub
 
/*Calling all macros*/
Sub All_codes()
Export_Range_Images
AddSheet
TestInsertPictureInRange
End Sub

Thanks!
Archie

Posted 15-Aug-12 5:10am

archies_gall

Updated 15-Aug-12 5:11am

Kenneth Haugland

v2

Add a Solution

Comments

[no name] 15-Aug-12 11:14am

"giving some error"... and that error would be what? Are we supposed to guess?

archies_gall 17-Aug-12 0:19am

Run-time error '424':
Object required

This information box i am getting when it reaches the line:
Set oImg = Picture.Add

in Export_Range_Images() macro.

I am not sure how to attach a screen shot here....
Hope this helps you to understand my problem.

[no name] 17-Aug-12 7:49am

You are getting that error probably because Picture is Nothing

Sergey Alexandrovich Kryukov 15-Aug-12 13:16pm

I'm curious why would you ever need it. By the way, if you explain, it will give you some chance that someone bothers to answer, otherwise -- hardly.
And then explain the problem properly. Error or exception? Provide comprehensive information. In what line, etc.
--SA

gjdfklfgdgfdgfdgfd 16-Aug-12 13:44pm

I've yet to see a VBA procedure throw an exception.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2012-08-24T06:26:00

The most important thing is that the code is not optimised! What it means?

1) if the part of procedure is used more than one time, you need to use this as a separatly function/procedure or to send it as a parameter!
The procedures TestInsertPictureInRange and Export_Range_Images should have as an input parameter the name of image, as you use it in InsertPictureInRange procedure.

VB

Sub TestInsertPictureInRange (ByVal PictureFileName AS String)
'...

End Sub
Sub Export_Range_Images (ByVal PictureFileName AS String)
'...
End Sub

2) if the code has no context, the effect of its job can bring unexpected results!
For example, try to run this procedure:

VB

Sub Test
Range("A1") = "Hello Kitty!"
End Sub

for 3 opened workbooks and for each sheet in them.

Please, see my code (with error-handler):

VB

Option Explicit

Sub Test()
    InsertImageInRange "F:\Download\Saalbach009.jpg", Format(Date, "yyyyMMdd")
    InsertImageInRange "F:\Download\Saalbach009.jpg", Format(Date, "yyyyMMdd"), "A1:I29"
End Sub

'Code to insert a picture/image in the current/active workbook into newly added sheet
' and resizes it to fit the TargetCells range

Sub InsertImageInRange(ByVal PictureFileName As String, ByVal DefaultSheetName As String, Optional ByVal DefaultTargetRange As String = "G10:N30")
Dim wsh As Worksheet, rng As Range, pic As Shape

'On error go to error-handler
On Error GoTo Err_ExportRangeAsImage

Set wsh = ActiveWorkbook.Worksheets.Add(After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count))

wsh.Name = DefaultSheetName
Set rng = wsh.Range(DefaultTargetRange)

Set pic = wsh.Shapes.AddPicture(PictureFileName, msoFalse, msoCTrue, rng.Left, rng.Top, _
                rng.Offset(0, rng.Columns.Count).Left - rng.Left, rng.Offset(rng.Rows.Count, 0).Top - rng.Top)


Exit_ExportRangeAsImage:
    On Error Resume Next
    Set wsh = Nothing
    Set rng = Nothing
    Set pic = Nothing
    Exit Sub

Err_ExportRangeAsImage:
    MsgBox Err.Description, vbExclamation, Err.Number
    Resume Exit_ExportRangeAsImage

End Sub

More you'll find at:
http://www.jpsoftwaretech.com/export-excel-range-to-a-picture-file/[^]
http://dmcritchie.mvps.org/excel/xl2gif.htm[^]
http://www.ozgrid.com/forum/showthread.php?t=65781[^]