Range to Image

[lawvictor]

Hi All,
I ma writing a script to covert a given range into image, i found a code from which was working fine as long as the range was smaller width but the moment i increased the width of the column it is not working, the whole image is not creating. the below is the steps how the code works,
1) selects the range, find it height and width,
2) inserts a chart and changes the height and width to the range height and width,
3) paste the copied range into the chart
4) export the chart as image.

i find that the chart gets modified to the size of the range however the image pasted in it is no the full image,

any help would be useful.

 

[Kenneth Hobson]

If you use pdf, you need a page setup to fit it all there. Try a few manual runs to see if sizing or fit all or such will suffice.

A recorded macro shows the code.

 

[lawvictor]

Just for curiosity, are you able to convert range of order ("a1:DZ30") to image file, where the whole of the range is visible on a single image file

 

[Yongle]

Just for curiosity, are you able to convert range of order ("a1:DZ30") to image file, where the whole of the range is visible on a single image file?

YES with Excel 365

Am working with excel 2013, is it something to do with that version of excel.?

Yes Yongle I tried post #11 , but no success.

Test one of the solutions on a NEW workbook without any settings
- simply create a new workbook and save A1:DZ30" to image file
- does it work?

If successful you have a "safe" starting point
Next : add each zoom and screen settiings to that workbook ONE by ONE until it stops working

 

[Yongle]

Test this on your existing workbook first
- if it fails then test on a NEW workbook

Have you used Excel's camera tool?
1. see this link and add the camera icon to your Quick Access Toolbar
2. select range A1:DZ30
3. click on camera icon (takes the picture)
4. click on any cell (pastes the picture)

Is the resultant image the one you expect?

 

[lawvictor]

Thanks a lot for all your suggestion, for some reason it is still not working.

yes i tried doing it, it gives an error saying "The picture is too large and will be truncated"

I even manually copied the big range and tried pasting it in the new sheet as paste special image, it still give the error "The picture is too large and will be truncated". I believe it is limitation of excel 2013 which is not letting this happen. I even tried copying and pasting the range manually into the new paint file, but it still fails.

I am unable to fit that large range in the pdf too. IS there any work around for this issue.

 

[Yongle]

google “The picture is too large and will be truncated” and you will get a lot of results

Rather than an Excel version problem it may be a Windows clipboard history issue according this link

 

[lawvictor]

All the result about the pic getting truncated id about solution using third party software which is noy feasible.

I was thinking of another approach, let me know if that has any possibility of succeeding, wherein I will copy that big range in small incremental size and then paste next to each other in the chart such that the whole range is pasted in the chart. Later exporting to image.

For this approach i need to know where to paste the range each time. And how do i fix the start point to paste the range in the chart
For eg.

I paste range A1:A20 from a left of the chart and next i need to paste A20:A40 at a distance from the top left of the chart and so on. Is there a script wherein i csn fix that start point thru script

 

[Kenneth Hobson]

Change the PageSetup to suit.

Sub Main3()
  Dim ws As Worksheet, p$, fn$
  p = ThisWorkbook.Path & "\"
  
  For Each ws In Worksheets
    With ws
      If Len(.Name) = 5 And IsNumeric(.Name) Then
        With .PageSetup
          .LeftMargin = Application.InchesToPoints(0.2)
          .RightMargin = Application.InchesToPoints(0.2)
          .TopMargin = Application.InchesToPoints(0.5)
          .BottomMargin = Application.InchesToPoints(0.5)
          .HeaderMargin = Application.InchesToPoints(0)
          .FooterMargin = Application.InchesToPoints(0)
          .Orientation = xlLandscape
          .BlackAndWhite = False
          .Zoom = False
          .FitToPagesWide = 1
        End With
        fn = p & .Name & ".pdf"
        .[A1:CD11].ExportAsFixedFormat Type:=xlTypePDF, Filename:=fn, _
          Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
          :=True, OpenAfterPublish:=False
      End If
    End With
  Next ws
End Sub

 

[lawvictor]

Change the PageSetup to suit.

Sub Main3()
  Dim ws As Worksheet, p$, fn$
  p = ThisWorkbook.Path & "\"
  
  For Each ws In Worksheets
    With ws
      If Len(.Name) = 5 And IsNumeric(.Name) Then
        With .PageSetup
          .LeftMargin = Application.InchesToPoints(0.2)
          .RightMargin = Application.InchesToPoints(0.2)
          .TopMargin = Application.InchesToPoints(0.5)
          .BottomMargin = Application.InchesToPoints(0.5)
          .HeaderMargin = Application.InchesToPoints(0)
          .FooterMargin = Application.InchesToPoints(0)
          .Orientation = xlLandscape
          .BlackAndWhite = False
          .Zoom = False
          .FitToPagesWide = 1
        End With
        fn = p & .Name & ".pdf"
        .[A1:CD11].ExportAsFixedFormat Type:=xlTypePDF, Filename:=fn, _
          Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
          :=True, OpenAfterPublish:=False
      End If
    End With
  Next ws
End Sub

thanks Ken, i did try that option, it still gives me on a three page pdf, one more thing i want to point out is that the range is A1:CD11 and each column is of width 70, when i tried the same with lesser width i could get it on single page,

 

[lawvictor]

In the below code, with ochart.paste, can i define the location of the paste within the chart area. is that possible, if it is possible am believing this issue could be solved. any comments on it

Try this
- amend the filepath & name
- run the macro

Input box asks user for range (otherwise defaults to current selected range)
- select with mouse or type it in

Sub RangeToJpg()
    Dim oChart As Chart, Ws As Worksheet, Shp As Shape, Rng As Range
    Set Rng = Application.InputBox("Select a range to copy", "Range to jpg", Selection.Address, , , , , 8)
    If Rng Is Nothing Then Exit Sub

    Set Ws = Rng.Worksheet
    Rng.Copy:   Ws.Pictures.Paste.Select
    Set Shp = Ws.Shapes(Ws.Shapes.Count)

    Set oChart = Charts.Add
    oChart.ChartArea.Clear
    
    Set oChart = oChart.Location(Where:=xlLocationAsObject, Name:=Ws.Name)
    With oChart.ChartArea
        .Width = Shp.Width
        .Height = Shp.Height
        Shp.Copy
        oChart.ChartArea.Select
    End With
    With oChart
        .Paste
        .Export Filename:="[COLOR=#ff0000]C:\test\SavedRange.jpg[/COLOR]", FilterName:="jpg"
    End With
    Ws.ChartObjects(Ws.ChartObjects.Count).Delete
    Shp.Delete
End Sub