Copy content of one excel sheet as an image to another sheet of the same excel.

[Archies]

Hi,

I have written a code for reading the excel and capturing the content as an image but now i want to paste the captured content into another sheet of the same excel and want to add new column in the same captured image?

Can you please let me know if it is possible to add a column in the captured image?

Thanks!
Archie

 

[patel45]

can you share your code ? I'll share my code to put the image on sheet

 

[Archies]

Hi,

Please find the code which reads the excel and creates image, adds new sheet in excel and paste the image in the new sheet made.


/* 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




Now my prob is...
1) I dont want the chart which is coming in the image.
2) The new sheet gets added at the last but i want it to be the first sheet in my excel.


Thanks for the reply.

Regards,
Archie

 

[patel45]

I'm sorry, but I don't understand how you can export oRange as image, you define oRange and never use it.

 

[Archies]

I'm sorry, but I don't understand how you can export oRange as image, you define oRange and never use it.

I have set the range value:

Set oRange = Range("A1:I84")

it will take content from A1 to I84.

Hope this clarifies.

 

[patel45]

Set oRange = Range("A1:I84") ' define range
Set oCht = Charts.Add ' why ?
Set oImg = Picture.Add
oCht.Paste ' ?????
oCht.Export Filename:="E:\img\SavedRange.jpg", Filtername:="JPG" ' this export oCht, not oRange

 

[patel45]

I think you missed a line, the correct code is

Sub Export_Range_Images()
Dim rng As Range, Cht As Chart
Set rng = Range("[COLOR=#333333]A1:I84[/COLOR]")
rng.CopyPicture xlScreen, xlPicture ' missing in your code
Set Cht = Charts.Add(0, 0, rng.Width + 10, rng.Height + 10) ' resize
Cht.Paste
Cht.Export Filename:="E:\img\SavedRange.jpg", Filtername:="JPG"
Cht.Delete
Set Cht = Nothing
Set rng = Nothing
End Sub

 

[Archies]

I think you missed a line, the correct code is

Sub Export_Range_Images()
Dim rng As Range, Cht As Chart
Set rng = Range("[COLOR=#333333]A1:I84[/COLOR]")
rng.CopyPicture xlScreen, xlPicture ' missing in your code
Set Cht = Charts.Add(0, 0, rng.Width + 10, rng.Height + 10) ' resize
Cht.Paste
Cht.Export Filename:="E:\img\SavedRange.jpg", Filtername:="JPG"
Cht.Delete
Set Cht = Nothing
Set rng = Nothing
End Sub

this line gives error:
Set Cht = Charts.Add(0, 0, rng.Width + 10, rng.Height + 10) ' resize